Question :
How is this possible? For my understanding if i already have an UPDLOCK in the first process. The second process needs to wait or hang in the select, during the time the first process are not commiting the transaction.
Deadlockgraph:
<deadlock-list>
<deadlock victim="process1f37b18c108">
<process-list>
<process id="process1f37b18c108" taskpriority="0" logused="0" waitresource="KEY: 8:72057594088980480 (22cb2de3bf06)" waittime="1446" ownerId="230178654" transactionname="implicit_transaction" lasttranstarted="2022-04-13T14:40:02.193" XDES="0x1f57387c428" lockMode="U" schedulerid="2" kpid="2092" status="suspended" spid="80" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-04-13T14:40:02.193" lastbatchcompleted="2022-04-13T14:40:02.193" lastattention="1900-01-01T00:00:00.193" clientapp="SITLOG-WMC" hostname="LAGW6ML001" hostpid="0" loginname="bml1gdb" isolationlevel="read committed (2)" xactid="230178654" currentdb="8" currentdbname="BML1GDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="128" stmtend="356" sqlhandle="0x02000000012fda2a8b70601ac4d20eae5633914dd83d1eea0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 datetime2,@P1 int,@P2 bigint,@P3 nvarchar(4000),@P4 bigint)UPDATE SYS_SCHED_SEQUENCE SET mod_ts = @P0, seq_next = @P1, VERSION = @P2 WHERE ((IDENT = @P3) AND (VERSION = @P4)) </inputbuf>
</process>
<process id="process1f5665664e8" taskpriority="0" logused="0" waitresource="KEY: 8:72057594088980480 (334bd54e4e37)" waittime="1446" ownerId="230178657" transactionname="implicit_transaction" lasttranstarted="2022-04-13T14:40:02.193" XDES="0x1f51d020428" lockMode="U" schedulerid="2" kpid="4332" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2022-04-13T14:40:02.193" lastbatchcompleted="2022-04-13T14:40:02.193" lastattention="1900-01-01T00:00:00.193" clientapp="SITLOG-WMC-SL-BML1G-WMC-SYS-SCHEDULER" hostname="LAGW6ML001" hostpid="0" loginname="bml1gdb" isolationlevel="read committed (2)" xactid="230178657" currentdb="8" currentdbname="BML1GDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" stmtend="290" sqlhandle="0x020000009b29c10ffdd03721b9f269c4eca16d8a5f55d5820000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000))SELECT IDENT, seq_end, gen_ts, mod_ts, seq_next, seq_start, VERSION FROM SYS_SCHED_SEQUENCE WITH (UPDLOCK) WHERE (IDENT = @P0) </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594088980480" dbid="8" objectname="BML1GDB.dbo.SYS_SCHED_SEQUENCE" indexname="PK_SYS_SCHEDSEQ" id="lock1f44ce78600" mode="U" associatedObjectId="72057594088980480">
<owner-list>
<owner id="process1f5665664e8" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process1f37b18c108" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594088980480" dbid="8" objectname="BML1GDB.dbo.SYS_SCHED_SEQUENCE" indexname="PK_SYS_SCHEDSEQ" id="lock1f11847bb00" mode="U" associatedObjectId="72057594088980480">
<owner-list>
<owner id="process1f37b18c108" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process1f5665664e8" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Edit:
We already found the answer in the Excution Plan as in the comments got suggestions. The Executionplan was unexpectedly using an Index Seek wich causing the whole Key to be locked and not a single Row.
This happend because of different Types in the Preparedstatement “nvarch” and the column definition type “varchar”.
Answer :
How is this possible?
SQL Server uses mostly row-level locking, and so both queries start running and taking U locks on the keys they read (one because it’s an UPDATE statement, and the other because it’s a SELECT with UPDLOCK hint).
Soon, each process owns U locks on many different keys. Then one session needs a U lock on a key locked by the other and blocks. Then the unblocked session needs a U lock on a key owned by the blocked session, which is a deadlock.
The Problem was, that the Queryparam were implicitly converted into NVARCHAR. So that the UPDLOCK locks not only one Index.
Now we changed the Collation from SQL_Latin1_CP1_CI_AS to Latin1_General_CI_AS.
Possible is also to set the property “sendStringParametersAsUnicode=false” in URL.