FIX: Undetected deadlock occurs when you use a sequence object in SQL Server 2012

Applies to: SQL Server 2012 DeveloperSQL Server 2012 EnterpriseSQL Server 2012 Standard More

Symptoms


Assume that you use a sequence object in Microsoft SQL Server 2012. When you view your session’s running status, you may find there is an undetected deadlock between the checkpoint process and the session that inserts the new sequence number. In this situation, the session hangs indefinitely.

Note When you kill the blocked session, the SQL Server process stops.

Cause


The issue occurs because a checkpoint thread tries to flush a sequence. However its row cannot be found in sysobjvalues table. This is because another thread performs a rollback on a transaction that has the next value of the sequence, in this situation, the row is removed from sysobjvalues table.

Resolution


The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 2 for SQL Server 2012 SP2

Cumulative Update 11 for SQL Server 2012 SP1

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.