Congratulations to John T. Keller, who won first prize of \$100 for the best solution to the December Reader Challenge, "Improve T-SQL to Prevent Deadlocks" and to Bill Twomey who won second prize of \$50. John is a database developer for Softek Solutions, and Bill is a Microsoft SQL Server DBA for Database Solutions. Here's a recap of the problem and the solution to the December Reader Challenge.

### Problem:

Manoj is a database developer for a company that develops business applications that use SQL Server 2000 as a database server. In Manoj's environment, the database that the applications use contains a table that stores sequential numbers used by various features. The table stores the name of the sequence and the next number. The schema of the table is shown in the following code:

```USE tempdb
GO
CREATE TABLE dbo.Sequences ( SeqName varchar(30) NOT NULL PRIMARY KEY, NextNum bigint NOT NULL DEFAULT 0)
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #1', DEFAULT )
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #2', DEFAULT )
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #3', DEFAULT )
GO```

The database also contains a GetNextSeqNum stored procedure, which the applications use to get the next number in a particular sequence. The stored procedure is shown in the following code:

```USE tempdb
GO
CREATE PROCEDURE dbo.GetNextSeqNum (@SeqName varchar(30), @NextNum bigint = NULL OUTPUT)
AS
BEGIN
BEGIN TRANSACTION
SET @NextNum = (SELECT NextNum FROM dbo.Sequences WHERE SeqName = @SeqName)
UPDATE dbo.Sequences
SET NextNum = NextNum + 1
WHERE SeqName = @SeqName
COMMIT TRANSACTION
END
GO```

When Manoj tests the stored procedure with concurrent SQL Server connections, he notices that some of the calls encounter deadlocks and fail. Modify the stored procedure logic to help Manoj resolve the deadlock problem.

### Solution:

Manoj realizes that the stored procedure logic that contains the SELECT and UPDATE statements is flawed because concurrent calls are made for the same sequence. When the SELECT statements from different connections acquire the shared lock on the sequence row, the connections will get into a deadlock state because they try to update the same row. One way to eliminate the deadlock would be to serialize access to the sequence table. Alternatively, Manoj can use the T-SQL UPDATE statement extension to get the next sequence number and increment the column in a single statement. The modified stored procedure is shown in the following code:

```USE tempdb
GO
IF object_id('dbo.GetNextSeqNum') IS NOT NULL
DROP PROCEDURE dbo.GetNextSeqNum
GO
CREATE PROCEDURE dbo.GetNextSeqNum (@SeqName VARCHAR(30), @NextNum bigint = NULL OUTPUT)
AS
BEGIN
UPDATE dbo.Sequences
SET @NextNum = NextNum, NextNum = NextNum + 1
WHERE SeqName = @SeqName
END
GO```

Now, the UPDATE statement ensures that only one connection can update the row and the other concurrent calls will be blocked. This revision will prevent the deadlock and provide better performance because the entire operation in the stored procedure is performed in a single statement.