Locks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Locks

Author

Topic

viperbyte
Posting Yak Master

132 Posts

Posted - 2010-03-15 : 11:22:35

Hello everybody. Do you have to code record locking for inserts and updates or is that automatically handled by SQL Server? I'm using SQL Server 2008 for developing a SQL Server 2005 database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 12:16:02

what do you mean code recode locking? do you mean explicit use of locking hints?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-15 : 12:21:15

in general the answer is: don't bother because SQL Server cares.

No, you're never too old to Yak'n'Roll if you're too young to die.

viperbyte
Posting Yak Master

132 Posts

Posted - 2010-03-15 : 13:21:37

visakh16 I don't know about lock hints. But from what i gather from webfred's comment and yours is that generally I don't have to code record locking and that I can HINT SQL Server to explicitly do the locks as I TRY to dictate. Thanks guys.

Kristen
Test

22859 Posts

Posted - 2010-03-15 : 13:31:03

Best to use a transaction block if you do multiple updates in one "batch"

So ...


BEGIN TRANSACTION

INSERT INTO TableA(Col1, Col2, ...) VALUES ('Foo', 'Bar', ...)

UPDATE U
SET Col3 = 'FooBar'
FROM TableB AS U
WHERE Col3 = 'BarFoo'

COMMIT

this will ensure that either both updates occur, or neither do, and if both updates interact in some why that no other update will "get in the way" - the transaction is "Atomic".

You can, and should, put error checking after the Insert and Update statements in that batch, and then if you had an error (or if you hit a soft error - such as some sort of Data Validation error - then you could issue a ROLLBACK and any other updates within the transaction would be rolled-back.

viperbyte
Posting Yak Master

132 Posts

Posted - 2010-03-15 : 14:36:14

Ok Kristen. I wasn't quite there yet but I would have been there eventually so now I know. I appreciate the info.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources