A very strange deadlock on key lock in SQL Server -


it strange when there 2 connections running below transaction on different rows, deadlock detected. why query optimizer require transaction t1 have resource key2 row updated transaction t2?

key1 (row 1) key: 5:72057594048348160 (150fa2746afc)

key2 (row 2) key: 5:72057594048348160 (1bec117e39ae)

2 transactions updating different rows @ same time. suppose not interfering each other uplock , rowlock?

begin tran select * table with(uplock, rowlock) pk_col1 = ? , pk_col2 = ? update table set col3 = ? pk_col1 = ? , pk_col2 = ? end 

the following deadlock list

<process id="process10e7502c8" taskpriority="0" logused="0" waitresource="key: 5:72057594048348160 (150fa2746afc)"  .... <process id="process10e750988" taskpriority="0" logused="0" waitresource="key: 5:72057594048348160 (1bec117e39ae)"  ... <resource-list>   <keylock hobtid="72057594048348160" dbid="5" objectname="" indexname="" id="locka6b73300" mode="u" associatedobjectid="72057594048348160">     <owner-list>       <owner id="process10e750988" mode="u" />     </owner-list>     <waiter-list>       <waiter id="process10e7502c8" mode="u" requesttype="wait" />     </waiter-list>   </keylock>   <keylock hobtid="72057594048348160" dbid="5" objectname="" indexname="" id="locka5319b80" mode="u" associatedobjectid="72057594048348160">     <owner-list>       <owner id="process10e7502c8" mode="u" />     </owner-list>     <waiter-list>       <waiter id="process10e750988" mode="u" requesttype="wait" />     </waiter-list>   </keylock> </resource-list> 

here sp_lock result when deadlock occurred

spid    dbid    objid   indid   type    resource    mode    status 51  5   0   0   db                                      s   grant 52  6   0   0   db                                      s   grant 53  4   0   0   db                                      s   grant 54  5   0   0   db                                      s   grant 54  5   1941581955  0   tab                                     ix  grant 54  5   1941581955  1   key (1bec117e39ae)                      u   grant 54  5   0   0   md  4(6:0:0)                            sch-s   grant 54  5   1941581955  1   key (150fa2746afc)                      u   wait 54  5   1941581955  1   pag 1:73626                             iu  grant 57  5   0   0   db                                      s   grant 58  6   0   0   db                                      s   grant 58  6   0   0   app 16384:[repl-logread]:(04dddec9)     x   grant 59  5   0   0   db                                      s   grant 60  6   0   0   db                                      s   grant 61  5   0   0   db                                      s   grant 62  5   0   0   db                                      s   grant 63  4   0   0   db                                      s   grant 64  4   0   0   db                                      s   grant 65  5   0   0   db                                      s   grant 65  5   1941581955  1   key (1bec117e39ae)                      u   wait 65  5   1941581955  1   pag 1:73626                             iu  grant 65  5   0   0   md  4(6:0:0)                            sch-s   grant 65  5   1941581955  1   key (150fa2746afc)                      u   grant 65  5   1941581955  0   tab                                     ix  grant 66  6   0   0   app 16384:[dc1isgsd03\i]:(152e28ac)     x   grant 66  6   0   0   db                                      s   grant 67  1   1131151075  0   tab                                      grant 69  5   0   0   db                                      s   grant 

if add unclustered index same columns , ordering clustered index created primary key, deadlock problem disappears. why update row on clustered index key required update lock on other clustered index key?

please correct me if have misunderstanding. answers appreciated.

the table schema described below sql script

create table [dbo].[table1] ( [pk_col1] char(10) not null, [pk_col2] char(10) not null, [col3] char(10) not null, primary key ([pk_col1],[pk_col2]) ); 

add readpast hint:

begin tran     select * table with(updlock, rowlock, readpast)      pk_col1 = ? , pk_col2 = ?     update table set col3 = ? pk_col1 = ? , pk_col2 = ? commit tran 

as @cjbs noted in comments use of hints should limited situations required , understand consequences.

the hints in example above occur in example of using table queue, want select single row, hold lock on it, update in separate statement (such deleting row once processed), allow other readers read rows past held update lock.


Comments

Popular posts from this blog

c++ - QTextObjectInterface with Qml TextEdit (QQuickTextEdit) -

javascript - angular ng-required radio button not toggling required off in firefox 33, OK in chrome -

xcode - Swift Playground - Files are not readable -