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.
| Author |
Topic |
|
rogerl1972
Starting Member
2 Posts |
 Posted - 2005-07-29 : 11:09:42
|
| I need to do an update statement and the where clause is an IN() statement with a lot of entries:UPDATE dbo.test SET Field1 = getdate() WHERE ID in (1,2,3,4,5,6...1000)During this update statement, are all the rows (1-1000) locked for the duration of the update statement? If so, what kind of locking is occurring?Also, if the Update statement fails in the middle of the T-SQL, will all the updates be rolled back...similar to a transaction?Thanks for your help! |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2005-07-29 : 11:16:07
|
| During this update statement, are all the rows (1-1000) locked for the duration of the update statement? If so, what kind of locking is occurring?Yes. Exclusive locks are being taken.Also, if the Update statement fails in the middle of the T-SQL, will all the updates be rolled back...similar to a transaction?Yes - it is a single statement, so it will either complete sucessfully or fail. You should do Error checking after the statements (review @@Error in BOL).By the way, if you know that you working with every sequential ID number from 1-1000, then use:Where ID >= 1 and ID <= 1000 --ORWhere ID betwee 1 and 1000 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-07-29 : 11:47:36
|
quote:
Originally posted by rogerl1972
During this update statement, are all the rows (1-1000) locked for the duration of the update statement? If so, what kind of locking is occurring?
You may find it usefull to set showplan_text on. then you can see for yourself exactly what happens |
|
|
|
|
|
|
|
|
Locking with UPDATE..WHERE..IN(xxx)