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
Post a Comment