what is the root cause?

Question

I have a stored procedure which has some update sql statements:

for example:

update tablename with (rowlock) set column1='xxx' where column2='xxx'-- here column2 is the primary key

It exists some sessions using the stored procedure at the same time,of course,the column2 has the different value.

I used the dbcc traceon(3605,1204,-1) to minitor some blocking message,and found that it always blocked at the update point.

so,this is the doult.

column2 is the primary key ,and it updates one record one time,how it could block other session?

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 1

SQLServer 2000 here

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 2

How many rows are being affected in your scenario? It may be that if enough rows are being locked that SQL will escalate the lock type even though rowlock was specified. If you run the test again and look at the database activity montor, does is show a larger lock, like a page or table lock?

---

Joie Andrew "Since 1982"

Answer 3

First of all -T1204 prints the deadlock information and not the blocking information. So, are you experiencing deadlocks or blocking?

When you specify a ROWLOCK hint, the query will still try to lock the rows on the index page that it is modifying. Since, you are modifying a index pages, it would have to acquire locks on the rows present in that page. But this is just one of reasons in case you are experiencing key level locking.

I created a small scenario to illustrate this where I have only two rows in the table and I used the following update statements from 2 different sessions within a transaction:

update tbl with (rowlock) set b = 1 where a =1

Table:

a b

----------- -----------

1 2

2 3

 

spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
53     5      0           0      DB                                    S        GRANT
53     1      1115151018  0      TAB                                   IS       GRANT
53     5      1691153070  2      PAG  1:7733                           IX       GRANT
53     5      1691153070  0      TAB                                   IX       GRANT
53     5      1691153070  2      KEY  (010086470766)                   X        GRANT
53     5      1691153070  2      KEY  (020068e8b274)                   X        GRANT
53     5      1691153070  1      KEY  (010086470766)                   X        GRANT
53     5      1691153070  1      PAG  1:903                            IX       GRANT

spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     5      0           0      DB                                    S        GRANT
61     5      1691153070  2      KEY  (03000d8f0ecc)                   X        GRANT
61     5      1691153070  2      PAG  1:7733                           IX       GRANT
61     5      1691153070  0      TAB                                   IX       GRANT
61     5      1691153070  2      KEY  (010086470766)                   X        WAIT
61     5      1691153070  1      KEY  (020068e8b274)                   X        GRANT
61     5      1691153070  1      PAG  1:903                            IX       GRANT

If you see the output above, the query is waiting on a X lock on the non-clustered index key since, the update is happening on the key resource 020068e8b274 but it is waiting on a X lock because the index page is being modified by Query1.

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL