'SQL Server 2017 undetected deadlock issue
The problem is:
BEGIN TRANSACTION
SELECT TOP 1
q.* )
FROM [QTable] q WITH(UPDLOCK)
WHERE <some condition>
ORDER BY <some columns>
This is called by a dozen of concurrent processes every few seconds each. After this select they make stuff by 1-2 seconds then make a single update of this very selected row and end their sessions with commit (rollback is also possible but never observed).
Table QTable
currently has 350'000 rows but the job is around its top (it has an identity column as a primary key). The table is fed periodically with some new rows by the single insert statement (under transaction, as the another table must be updated because of new rows added to QTable
).
It has been working for last 5 years smoothly but last time THEY (means IT stuff) updated SQL Server to 2017. And the tragedy appeared immediately: all processes get blocked very fast -- some of them (1-2) are on locks, while all others are waiting for CXPACKET.
When we analyse deeply it looks as they are waiting cycles -- classic deadlock.
We changed updlock into tablockx and it works again, but my question is why? We have a lot of concurrent updlock access, but rather rare, but we are afraid that all of our SQL will crash for the very same reason.
My suspicion is the ORDER BY
clause is responsible for that, but what has been changed in SQL Server that corrupted its many-years perfect behaviour?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|