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 |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2012-04-11 : 11:54:53
|
| Got locking on a table with only 58 rows. Nonclustered indexes on all columns and Col1 index is responsible for the locking. Although only single row update, there are 1000s SPIDs attempting this during peak times.Here's the t-sql:update <tbl> Col4=4202 where Co1=13 and ( Col4=1 or Col4=4202)Any ideas? (thanks in advance) |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-11 : 12:12:03
|
| Locking is normal, do you mean blocking? The best you can do to limit blocking is to use the ROWLOCK hint:UPDATE <tbl> WITH (ROWLOCK) SET Col4=4202 WHERE Co1=13 and ( Col4=1 or Col4=4202)Be advised that using hints is not a recommended practice. Don't just add them unless you absolutely need them. |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-04-11 : 12:17:06
|
| Also your code could be causing problemsUPDATE yourTableSET Col4 = 4202WHERE Co1 = 13 and Col4 = 1accomplishes the same thingJimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
Locking on table with only 58 rows