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 |
|
ssrikrish
Yak Posting Veteran
69 Posts |
 Posted - 2007-07-27 : 11:52:40
|
| Folks, I have an update statement and it looks like it is holding exclusive lock on the table and does not release it until it completes. a PAGLOCK hint has been specified on the update statement and i think it is being ignored. It is a transaction database.so, other queries accessing that table has to wait for it to complete and thus causing timeouts.The Update statement is also causing high IO and CPU utilization. How do I 1) reduce the granularity level to prevent the locking2)any ideas on optimizing the query?Here is the query: UPDATE Customers SET IndexStart = TMP.IndexStart, IndexPosition = TMP.IndexStart, IndexStop = TMP.IndexStop, IndexLevel = TMP.IndexLevel FROM Customers AS C WITH (PAGLOCK) INNER JOIN #tmp_IndexBCs AS TMP WITH (TABLOCKX) ON C.ID= TMP.IDThxSri |
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-07-27 : 11:54:50
|
| When we were on SQL 2K, i have not seen this issue occur. It is a case only with our SQl 2005 box.ThxSri |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-27 : 11:55:18
|
| WITH (TABLOCKX) gives you no clues?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-28 : 00:05:56
|
| Take look at snapshot isolation, it may help reduce blocking. |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-07-31 : 11:04:45
|
| Can I specify snapshot isolation just within that proc? and then change isolation after that. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-31 : 11:10:19
|
| look at set transaction isolation level_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-07-31 : 11:57:10
|
| I tried doing it. But after reading Books Online, I am wondering if it is going to help me on an UPDATE transaction. It says"SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data."Can you explain what the implications of the above on the challenge i am having?ThxSri |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
table lock - Issue