SQL Server Magazine December Reader Challenge Submission

Back in November I did something I had never done before. I submitted an entry for the monthly "Reader Challenge" contest SQL Server Magazine hosts in their "SQL Server Magazine UPDATE" e-mail newsletter.

I was a little disappointed when I did not win, but was happy to see the winning solution was the same as my own submission. Here is the question for December:

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.

Here is my solution to the problem:

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

The above stored procedure both fetches the current and updates the next sequence value in one operation. Testing this updated stored procedure with concurrent SQL Server connections verifies this new stored procedure eliminates the deadlock problem seen in the original stored procedure.

Since the updating and fetching occurs in the same statement, it is safe to remove the "BEGIN TRANSACTION" and "COMMIT TRANSACTION" statements from the stored procedure.

You can read about the winning submission at http://www.sqlmag.com/Article/
ArticleID/94572/sql_server_94572.html https://www.itprotoday.com/sql-server/converting-hexadecimal-string-varbinary-value.