SQL Deadlock when batch import
Recently we found deadlock report in SQL server, according to detailed info, it is from batch import which process the csv file row by row. Each row is processed by Queuing system (e.g. RabbitMq, AmazonMq…) and service bus. Thus, it is very possible to have multiple processes trying to update in one DB table row and cause deadlock.
Investigation: from the SQL deadlock.xdl file diagram, we can trace back which tables and index are involved in deadlock.
We can open xdl file into xml format to see few important info here: stored procedure name, isolation level, and lock mode. We can trace back where the code make this stored procedure call:
One stored procedure EntitySyncSelByEntityType which has SELECT statement with a WHERE clause. The table in question SyncStatus actually has 2 indexes, there's the clustered index PK_EntitySyncStatus and a non-clustered index nc_EntitySyncStatus_EntityType_EntityId. Another stored procedure EntitySyncUpdByEntityTypehas an UPDATE statement to update an existing row.
For some reason, the TRANSACTION ISOLATION LEVEL been set to “SERIALIZABLE” for both processes that became deadlocked, this can be seen in the deadlock XML file. This fact will become very relevant in a moment. In C# code we do set isolation level to read committed, but SQL might auto escalate it to serializable level for some reason.
Here is the assumption of deadlock reason and steps:
1. The first process (spid 772) executed the SELECT statement, and SQL decided to use the clustered index to read the value, so it takes out a shared lock S on the row in the clustered index. Ordinarily, for example with transaction isolation level = read committed, this shared lock would be released immediately after the SELECT statement is done executing. But because we’re inside a transaction and the transaction isolation level = serializable, shared locks are not released until the transaction commits or rolls back. So the shared lock S is still in effect.
Follow up: When a SELECT statement with a WHERE clause is executed, the SQL query optimizer decides which index to use. It does not simply choose the clustered or non-clustered index; its choice depends on factors like the columns in the WHERE clause, e.g. where column_A = value, which column_A is non-clustered index, SQL will use this index because this query is all about this non-clustered index; otherwise SQL will use cluster index first.
2. The second process (spid 746) now executes the SELECT statement, and this time SQL server decides to use the non-clustered index to read the value, and because SQL knows we’re inside a transaction, it takes out a RangeS-U lock on the non-clustered index. This is a shared lock across a range of rows plus an update lock on 1 particular row in that range. Again, because transaction isolation level = serializable, this lock will not be released until the transaction commits or rolls back. So the RangeS-U lock is still in effect.
Get Cookie Jen’s stories in your inbox
Join Medium for free to get updates from this writer.
3. Now, the first process (spid 772) is ready to execute the UPDATE statement, so it wants to upgrade the shared lock S to an exclusive lock X on the row it wants to update in the clustered index. But this request is blocked because the other spid has the conflicting RangeS-U lock on the non-clustered index. (Updating data in the clustered index would also modify data in the non-clustered index, so the range lock on the non-clustered index blocks the acquisition of the exclusive lock on the clustered index.)
4. The second process (spid 746) is ready to execute the UPDATE statement, so it now wants to acquire a RangeS-U lock on the clustered index. But it can’t get it, because the other spid already has a conflicting lock on a row within the same range.
5. Both spids are now deadlocked on each other.
It is hard to reproduce deadlock from APP side, so we wrote some SQL statement to simulate that in test environment:
So the key point is in SQL Server, a SELECT statement can acquire a RangeS-U (Shared-Update) lock—but only under the SERIALIZABLE isolation level and when scanning a range of index keys.
But we do set isolation level to ReadCommitted in c# code and SQL should inherit this setting in stored procedure. I confirmed there is no SERIALIZABLE isolation level been set in stored procedure. I know for some reason SQL stored procedure still runs under Serializable regardless original setting. Here is likely reasons:
1. Implicit transactions inside SQL. If your stored procedure starts its own transaction (e.g., BEGIN TRANSACTION), SQL Server may default to Serializable unless explicitly set otherwise.
2. Triggers or nested procedures. If your stored procedure calls other procedures or triggers that use Serializable, the isolation level can propagate upward.
However, none of them applies to our situation, still don’t know why.
Solution: we can make our select and update 2 stored procedure into ONE Upsert stored procedure. Then there will be only one stored procedure in transaction, when another process trying to run Upsert, it will wait until first process finished. For more detailed, I will have another article to discuss Upsert pattern.
