Sql server deadlock when insert and update in one transaction

azure-sql-databasedeadlock

I'm using Azure SQL + Java + Spring Boot 2. Currently I'm trying to understand the reason why deadlocks occur.
In transaction I'm doing insert and later update on the same table by different rows.
As far as I understood, SQL Server by default uses rowlock and the read_commited isolation. Here are deadlock details:

<deadlock>
    <victim-list>
        <victimProcess id="process1991308a4e8"/>
    </victim-list>
    <process-list>
        <process id="process1991308a4e8" taskpriority="0" logused="1104"
                 waitresource="KEY: 11:72057594043891712 (45e31f8447de)" waittime="3092" ownerId="5354390"
                 transactionname="implicit_transaction" lasttranstarted="2019-02-13T15:22:55.570" XDES="0x19918794420"
                 lockMode="U" schedulerid="1" kpid="85796" status="suspended" spid="147" sbid="0" ecid="0" priority="0"
                 trancount="2" lastbatchstarted="2019-02-13T15:22:56.450" lastbatchcompleted="2019-02-13T15:22:56.340"
                 lastattention="1900-01-01T00:00:00.340" clientapp="Microsoft JDBC Driver for SQL Server"
                 hostname="xxx-service-7ff786b7c7-kklgm" hostpid="0" loginname="xxx_core_user"
                 isolationlevel="read committed (2)" xactid="5354390" currentdb="11" currentdbname="payment"
                 lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="unknown" queryhash="0x49c416d3b96f3c35" queryplanhash="0x6ebf9e54f1242ad5" line="1"
                       stmtstart="510" stmtend="1096"
                       sqlhandle="0x02000000e96bb62c3a246f941a8767e52b870246866daefc0000000000000000000000000000000000000000">
                    unknown
                </frame>
                <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1"
                       sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 datetime2,@P1 datetime2,@P2 bigint,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6
                nvarchar(4000),@P7 bigint,@P8 int,@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 bigint,@P12
                nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 int)update payment set created_at=@P0,
                modified_at=@P1, amount=@P2, currency=@P3, description=@P4, email=@P5, external_id=@P6, fee_amount=@P7,
                lock_version=@P8, payment_method_provider_type=@P9, payment_method_type=@P10, pos_id=@P11,
                pricing_id=@P12, status=@P13 where id=@P14 and lock_version=@P15
            </inputbuf>
        </process>
        <process id="process199130a5088" taskpriority="0" logused="1400"
                 waitresource="KEY: 11:72057594043891712 (22e8948179bd)" waittime="2842" ownerId="5354401"
                 transactionname="implicit_transaction" lasttranstarted="2019-02-13T15:22:55.887" XDES="0x19911ddc420"
                 lockMode="U" schedulerid="1" kpid="8708" status="suspended" spid="156" sbid="0" ecid="0" priority="0"
                 trancount="2" lastbatchstarted="2019-02-13T15:22:56.557" lastbatchcompleted="2019-02-13T15:22:56.540"
                 lastattention="1900-01-01T00:00:00.540" clientapp="Microsoft JDBC Driver for SQL Server"
                 hostname="xxx-service-7ff786b7c7-kklgm" hostpid="0" loginname="xxx_core_user"
                 isolationlevel="read committed (2)" xactid="5354401" currentdb="11" currentdbname="payment"
                 lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="unknown" queryhash="0x49c416d3b96f3c35" queryplanhash="0x6ebf9e54f1242ad5" line="1"
                       stmtstart="510" stmtend="1096"
                       sqlhandle="0x02000000e96bb62c3a246f941a8767e52b870246866daefc0000000000000000000000000000000000000000">
                    unknown
                </frame>
                <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1"
                       sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 datetime2,@P1 datetime2,@P2 bigint,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6
                nvarchar(4000),@P7 bigint,@P8 int,@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 bigint,@P12
                nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 int)update payment set created_at=@P0,
                modified_at=@P1, amount=@P2, currency=@P3, description=@P4, email=@P5, external_id=@P6, fee_amount=@P7,
                lock_version=@P8, payment_method_provider_type=@P9, payment_method_type=@P10, pos_id=@P11,
                pricing_id=@P12, status=@P13 where id=@P14 and lock_version=@P15
            </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594043891712" dbid="11" objectname="56ae82fa-8057-4f9d-b4a8-290dc2ce3dec.dbo.payment"
                 indexname="pk_payment_id" id="lock19923e5d980" mode="X" associatedObjectId="72057594043891712">
            <owner-list>
                <owner id="process199130a5088" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="process1991308a4e8" mode="U" requestType="wait"/>
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594043891712" dbid="11" objectname="56ae82fa-8057-4f9d-b4a8-290dc2ce3dec.dbo.payment"
                 indexname="pk_payment_id" id="lock19928078f80" mode="X" associatedObjectId="72057594043891712">
            <owner-list>
                <owner id="process1991308a4e8" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="process199130a5088" mode="U" requestType="wait"/>
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

Also I'm using a clustered index on my id column and there are no issues with random operations order.
Moreover when I only do insert inside transaction everything works fine, without a deadlock.
So why am I getting a deadlock?

I cannot share source code but this pseudocode completely represents all logic:

@Transactional
public void processMessage(Message message) {

        Entity entity =
                repository.findById(message.getId())
                           .orElseGet(() -> {
                                Entity entity = converter.convertFrom(message);
                                return repository.insert(entity); //  insert
                            });

        if (canUpdateStatus(entity)) { //let's say it's always true
            entity.setStatus(newStatus);
            respository.save(entity); // update
        }

}

Best Answer

Analysis

If you examine your deadlock XML, you will quickly find that these are two separate sessions (spid) involved:

... spid="147" ...

...and:

... spid="156" ...

You'll also notice that the kpid (aka Windows Thread ID) differs:

... kpid="85796" ...

...and:

... kpid="8708" ...

The individual transactions can be analysed by reading the individual <process ...> ... </process> blocks. You have two.

Both processes are trying to update the payment table. This can be observed by extracting the statements form the relevant <process></process> sections which contain individual <inputbuffer> blocks:

For SPID 147:

       <inputbuf>
            (@P0 datetime2,@P1 datetime2,@P2 bigint,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6
            nvarchar(4000),@P7 bigint,@P8 int,@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 bigint,@P12
            nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 int)update payment set created_at=@P0,
            modified_at=@P1, amount=@P2, currency=@P3, description=@P4, email=@P5, external_id=@P6, fee_amount=@P7,
            lock_version=@P8, payment_method_provider_type=@P9, payment_method_type=@P10, pos_id=@P11,
            pricing_id=@P12, status=@P13 where id=@P14 and lock_version=@P15
        </inputbuf>

... and for SPID 156:

        <inputbuf>
            (@P0 datetime2,@P1 datetime2,@P2 bigint,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6
            nvarchar(4000),@P7 bigint,@P8 int,@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 bigint,@P12
            nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 int)update payment set created_at=@P0,
            modified_at=@P1, amount=@P2, currency=@P3, description=@P4, email=@P5, external_id=@P6, fee_amount=@P7,
            lock_version=@P8, payment_method_provider_type=@P9, payment_method_type=@P10, pos_id=@P11,
            pricing_id=@P12, status=@P13 where id=@P14 and lock_version=@P15
        </inputbuf>

Because of the updates to the same table at approximately the same time (2019-02-13 15:22:55.570 for both transactions) one of them will have to be automatically rolled back to resolve the deadlock.

In this case the deadlock resolver decided that the Victim SPID would be number 147.

<victim-list>
    <victimProcess id="process1991308a4e8"/>
</victim-list>

...which resolves to the SPID 147:

<process id="process1991308a4e8" taskpriority="0" logused="1104"
             waitresource="KEY: 11:72057594043891712 (45e31f8447de)" waittime="3092" ownerId="5354390"
             transactionname="implicit_transaction" lasttranstarted="2019-02-13T15:22:55.570" XDES="0x19918794420"
             lockMode="U" schedulerid="1" kpid="85796" status="suspended" spid="147" sbid="0"

Root Cause

The actual blocking is on the index pk_payment_id which can be retrieved from the <resource-list> section. It contains a list of affected objects and the corresponding <owner id="process.... mode="X"/> which allows you to trace it back to the actual session id (spid).

Here your <resource-list>:

<resource-list>
    <keylock hobtid="72057594043891712" dbid="11" objectname="56ae82fa-8057-4f9d-b4a8-290dc2ce3dec.dbo.payment"
             indexname="pk_payment_id" id="lock19923e5d980" mode="X" associatedObjectId="72057594043891712">
        <owner-list>
            <owner id="process199130a5088" mode="X"/>
        </owner-list>
        <waiter-list>
            <waiter id="process1991308a4e8" mode="U" requestType="wait"/>
        </waiter-list>
    </keylock>
    <keylock hobtid="72057594043891712" dbid="11" objectname="56ae82fa-8057-4f9d-b4a8-290dc2ce3dec.dbo.payment"
             indexname="pk_payment_id" id="lock19928078f80" mode="X" associatedObjectId="72057594043891712">
        <owner-list>
            <owner id="process1991308a4e8" mode="X"/>
        </owner-list>
        <waiter-list>
            <waiter id="process199130a5088" mode="U" requestType="wait"/>
        </waiter-list>
    </keylock>
</resource-list>

Both processes have an exclusive lock (X) which they would like to change to an upgrade lock (U). Now two processes can't have an update lock at the same time and so one session gets rolled back.

Update (U) locks prevent a common form of deadlock. In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.

To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.

^{Reference: Transaction Locking and Row Versioning Guide (Microsoft | Docs | SQL)}

Conclusion

There seems to be some form of timing issue in your code/framework or the insert/updates is being performed twice, instead of just once.

It's hard to determine which case you are observing. You would have to manually debug the program and watch for new SPIDs on the database.

Reference Links

sys.sysprocesses (Transact-SQL)

Column name   Data type   Description**  
spid          smallint    SQL Server session ID.  
kpid          smallint    Windows thread ID.

Best Answer

Analysis

Root Cause

Conclusion

Reference Links

Related Solutions

Sql-server – Deadlock on Insert/Update

MySQL: Transaction and Deadlock

Related Question