Microsoft SQL Server (Part 2)

Neil Haddley • March 27, 2021

T-SQL, Stored Procedures and Snapshot Isolation.

← Back to Posts

Stored procedures

Transact SQL (T-SQL) statements are used to create Microsoft SQL Server Stored Procedures.

Stored Procedures are maintained in the database making it easier to apply updates that improve the performance of all client applications.

Stored Procedures that help customers to purchase cinema tickets or reserve seats on airplanes need to be written with isolation levels in mind.

Shuffling cards

Consider how a stored procedure might be used to shuffle the contents of a table representing a deck of playing cards.

Swap cards

To shuffle a deck of standard playing cards we could select any two cards from the deck at random and swap their positions. If we did this enough times the deck would be shuffled.

Here is a stored procedure that will swap the data in two rows of the deck table.

Pick two cards, swap and repeat

To test the SwapCards stored procedure we can create:

a PickACard function that selects a card from the deck at random

a SwapCardsAtRandom stored procedure that uses the PickACard function (twice) and the SwapCards stored procedure (above) to swap the random pair of cards; and

client code that calls the SwapCardsAtRandom stored procedure 1,000 times.

With a single client

Running a single copy of client code that calls the SwapCardV1 stored procedure is fine.

The result is a shuffled deck with 13 cards of each suit.

Shuffle results single client

Shuffle results single client

Concurrency issues

However, running multiple copies of the client code that calls the SwapCardV1 stored procedure reveals an issue.

Shuffle failure

Shuffle failure

Snapshot issolation

When concurrency is introduced our original SwapCards stored procedure does not work. No errors are being raised but transactions are stepping on each other.

We can use Snapshot isolation to address the issue.

Multiple clients

With the updated SwapCard stored procedure we can run multiple updates concurrently.

Shuffle success

Shuffle success

Catching the errors

Snapshot isolation guarantees that reads made in a transaction will see a consistent snapshot of the database and that the transaction will commit only if no update made in the transaction conflicts with another concurrent update.

With snapshot isolation enabled there is a chance that a commit will fail and that an error will be raised. This is a good thing.

In the code shown above if SwapCards throws an error the client code catches the error, prints a message and continues. Perhaps instead the client code should retry the swap?

Concurrency exceptions (that the client code needs to handle)

Concurrency exceptions (that the client code needs to handle)

.NET Core console app

$ dotnet run

The code below calls the SwapCards stored procedure 1000 times using C#.

Node console app

$ node index.js

The code below calls the SwapCards stored procedure 1000 times using JavaScript and the 'mssql' node module.

Java console app

$ javac Program.java

then

$ java -cp ".:/Users/neilhaddley/sqljdbc_9.2/enu/mssql-jdbc-9.2.1.jre11.jar" Program

or

$ export CLASSPATH=.:/Users/neilhaddley/sqljdbc_9.2/enu/mssql-jdbc-9.2.1.jre11.jar

$ java Program

The code below calls the SwapCards stored procedure 1000 times using Java.

Using cross join to create a table the represents ...

TEXT
1-- Create the Deck
2CREATE TABLE Cards
3(
4    Card CHAR(5) PRIMARY KEY
5)
6GO
7
8INSERT INTO Cards
9VALUES
10    ('Ace'),
11    ('2'),
12    ('3'),
13    ('4'),
14    ('5'),
15    ('6'),
16    ('7'),
17    ('8'),
18    ('9'),
19    ('10'),
20    ('Jack'),
21    ('Queen'),
22    ('King')
23GO
24
25CREATE TABLE Suits
26(
27    Suit CHAR(8) PRIMARY KEY
28)
29GO
30
31INSERT INTO Suits
32VALUES
33    ('Clubs'),
34    ('Diamonds'),
35    ('Hearts'),
36    ('Spades')
37GO
38
39SELECT Id = IDENTITY(INT, 1, 1),
40    Suits.Suit,
41    Cards.Card
42INTO Deck
43FROM Cards
44CROSS JOIN Suits
45GO
46
47ALTER TABLE Deck ADD CONSTRAINT Deck_PK PRIMARY KEY (Id)
48GO

SwapCardsV1

TEXT
1-- Stored Procedure 'SwapCardsV1'
2CREATE PROCEDURE SwapCardsV1
3    @Card1 INT = 1,
4    @Card2 INT = 52
5AS
6BEGIN
7    
8    BEGIN TRY
9		BEGIN TRANSACTION
10
11		SET NOCOUNT ON
12
13		-- Store the first card's details
14		SELECT *
15        INTO #Temp
16        FROM Deck
17        WHERE Id = @Card1
18
19		-- Move second card into first card's place
20		UPDATE Deck
21		SET Card = CardDetails.Card,
22			Suit = CardDetails.Suit
23		FROM (
24			SELECT d.Card,
25            d.Suit
26        FROM Deck d
27        WHERE Id = @Card2
28			) CardDetails
29		WHERE Id = @Card1
30
31		-- Move first card into second card's place
32		UPDATE Deck
33		SET Card = CardDetails.Card,
34			Suit = CardDetails.Suit
35		FROM (
36			SELECT Card,
37            Suit
38        FROM #temp
39			) CardDetails
40		WHERE Id = @Card2
41
42		COMMIT TRANSACTION;
43	END TRY
44
45	BEGIN CATCH
46		-- PRINT 'We were unable to make the swap rolling back [' + ERROR_MESSAGE() + '].';
47		ROLLBACK TRANSACTION;
48        THROW 51000, 'Unable to swap card.', 1; 
49	END CATCH
50END
51GO

SwapCards T-SQL Client Code

TEXT
1-- User Defined Function 'PickACard'
2CREATE FUNCTION PickACard (@RAND FLOAT)
3RETURNS INT
4AS
5BEGIN
6    RETURN (
7			SELECT FLOOR(@RAND * (52 - 1 + 1)) + 1
8			)
9END
10GO
11
12-- Stored Procedure 'SwapCardsAtRandom'
13CREATE PROCEDURE SwapCardsAtRandom
14AS
15BEGIN
16    DECLARE @Rnd1 FLOAT
17
18    SET @Rnd1 = RAND()
19
20    DECLARE @Card1 INT
21
22    SET @Card1 = (
23			SELECT dbo.PickACard(@Rnd1)
24			)
25
26    DECLARE @Rnd2 FLOAT
27
28    SET @Rnd2 = RAND()
29
30    DECLARE @Card2 INT
31
32    SET @Card2 = (
33			SELECT dbo.PickACard(@Rnd2)
34			)
35
36    -- 'No need to swap a card with itself'
37    IF @Card1 != @Card2
38	BEGIN
39        EXECUTE dbo.SwapCardsV1 @Card1,
40			@Card2
41    END -- IF @Card1 != @Card2
42END -- CREATE PROCEDURE SwapCards
43GO
44
45-- Call Swap Cards Multiple times
46DECLARE @Counter INT
47SET @Counter = 0
48DECLARE @ErrorCounter INT
49SET @ErrorCounter = 0
50WHILE (@Counter < 1000)
51BEGIN
52
53    BEGIN TRY
54		EXECUTE SwapCardsAtRandom
55	END TRY
56
57	BEGIN CATCH
58		SET @ErrorCounter = @ErrorCounter+1
59        PRINT 'We were unable to swap cards [' + ERROR_MESSAGE() +']. Error Count ['+LTRIM(STR(@ErrorCounter,10))+']'  
60	END CATCH
61    
62    SET @Counter = @Counter + 1
63END
64GO
65
66-- Review results
67SELECT COUNT(*)
68FROM Deck
69GROUP BY Suit
70GO
71
72SELECT *
73FROM Deck
74ORDER BY Card,Suit
75GO

SwapCards with snapshot isolation

TEXT
1-- Stored Procedure 'SwapCards'
2CREATE PROCEDURE SwapCards
3    @Card1 INT = 1,
4    @Card2 INT = 52
5AS
6BEGIN
7    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
8
9    BEGIN TRY
10		BEGIN TRANSACTION
11
12		SET NOCOUNT ON
13
14		-- Store the first card's details
15		SELECT *
16        INTO #Temp
17        FROM Deck
18        WHERE Id = @Card1
19
20		-- Move second card into first card's place
21		UPDATE Deck
22		SET Card = CardDetails.Card,
23			Suit = CardDetails.Suit
24		FROM (
25			SELECT d.Card,
26            d.Suit
27        FROM Deck d
28        WHERE Id = @Card2
29			) CardDetails
30		WHERE Id = @Card1
31
32		-- Move first card into second card's place
33		UPDATE Deck
34		SET Card = CardDetails.Card,
35			Suit = CardDetails.Suit
36		FROM (
37			SELECT Card,
38            Suit
39        FROM #temp
40			) CardDetails
41		WHERE Id = @Card2
42
43		COMMIT TRANSACTION;
44	END TRY
45
46	BEGIN CATCH
47		-- PRINT 'We were unable to make the swap rolling back [' + ERROR_MESSAGE() + '].';
48		ROLLBACK TRANSACTION;
49        THROW 51000, 'Unable to swap card.', 1; 
50	END CATCH
51END
52GO

Program.cs

TEXT
1using System;
2using System.Data.SqlClient;
3using System.Data;
4
5namespace dotnet_sql {
6  class Program {
7    static void Main(string[] args) {
8      var cs = @ "User ID=sa;Password=Passw0rd123;Initial Catalog=Shuffle;Server=192.168.68.109;";
9
10      using var con = new SqlConnection(cs);
11      con.Open();
12
13      using(SqlCommand cmd = new SqlCommand("dbo.SwapCards", con)) {
14        cmd.CommandType = CommandType.StoredProcedure;
15        cmd.Parameters.Add("@Card1", SqlDbType.Int);
16        cmd.Parameters.Add("@Card2", SqlDbType.Int);
17
18        int errorCounter = 0;
19        for (int count = 0; count < 1000; count++) {
20          Random rn = new Random();
21          int card1 = rn.Next(1, 52);
22          int card2 = rn.Next(1, 52);
23
24          cmd.Parameters["@Card1"].Value = card1;
25          cmd.Parameters["@Card2"].Value = card2;
26
27          try {
28            cmd.ExecuteNonQuery();
29          } catch (Exception e) {
30            errorCounter++;
31            Console.WriteLine("We were unable to swap cards [" + e.Message + "]. Error Count [" +
32              errorCounter + "]");
33          }
34
35        }
36      }
37    }
38  }
39}

index.js

TEXT
1const mssql = require('mssql')
2
3const config = {
4    user: 'sa',
5    password: 'Passw0rd123',
6    server: '192.168.68.109',
7    database: 'Shuffle',
8    options: { enableArithAbort: false }
9};
10
11const dbConn = mssql.connect(config, function (err) {
12
13    if (err) {
14        console.log(err);
15        return
16    }
17
18    const request = new mssql.Request();
19
20    request.input('Card1', mssql.Int);
21    request.input('Card2', mssql.Int);
22
23    let errorCounter = 0;
24
25    (async () => {
26        for (x = 0; x < 1000; x++) {
27
28            const card1 = Math.floor(Math.random() * (52 - 1 + 1)) + 1
29            const card2 = Math.floor(Math.random() * (52 - 1 + 1)) + 1
30
31            request.parameters.Card1.value = card1;
32            request.parameters.Card2.value = card2;
33
34            if (card1 != card2) {
35
36                try {
37                    await request.execute('dbo.SwapCards')
38                } catch (e) {
39                    errorCounter++;
40                    console.error("We were unable to swap cards [" + e.originalError.message + "]. Error Count [" + errorCounter + "]");
41                }
42
43            }
44        }
45        dbConn.close();
46
47    })().catch(err => {
48        console.error(err);
49    });
50
51})

Program.java

TEXT
1import java.sql.Connection;
2import java.sql.DriverManager;
3import java.sql.SQLException;
4import java.sql.CallableStatement;
5import java.util.Random;
6
7public class Program {
8
9
10    public static void main(String[] args) {
11
12        String connectionUrl = "jdbc:sqlserver://192.168.68.109:1433;databaseName=Shuffle;user=sa;password=Passw0rd123";
13
14        try (Connection con = DriverManager.getConnection(connectionUrl);) {
15
16            CallableStatement callableStatement = con.prepareCall("{call dbo.SwapCards(?,?)}");
17
18            int errorCounter = 0;
19            for (int count = 0; count < 1000; count++) {
20
21                Random rn = new Random();
22                int card1 = rn.nextInt(52 - 1 + 1) + 1;
23                int card2 = rn.nextInt(52 - 1 + 1) + 1;
24
25                try {
26                    if (card1 != card2) {
27                        callableStatement.setInt(1, card1);
28                        callableStatement.setInt(2, card2);
29                        callableStatement.execute();
30                    }
31                } catch (SQLException e1) {
32                    errorCounter++;
33                    System.out.println("We were unable to swap cards [" + e1.getMessage() + "]. Error Count ["
34                            + Integer.toString(errorCounter) + "]");
35                }
36            }
37
38        } catch (SQLException e) {
39            e.printStackTrace();
40        }
41    }
42}

Full T-SQL code

TEXT
1DROP DATABASE Shuffle
2GO
3
4-- Create the DB
5CREATE DATABASE Shuffle
6GO
7
8-- Allow snapshot isolation
9ALTER DATABASE Shuffle
10SET ALLOW_SNAPSHOT_ISOLATION ON
11GO
12
13-- Enable accelerated database recovery
14ALTER DATABASE Shuffle
15SET ACCELERATED_DATABASE_RECOVERY = ON;
16GO
17
18-- Use Shuffle DB
19USE Shuffle
20GO
21
22-- Create the Deck
23CREATE TABLE Cards
24(
25    Card CHAR(5) PRIMARY KEY
26)
27GO
28
29INSERT INTO Cards
30VALUES
31    ('Ace'),
32    ('2'),
33    ('3'),
34    ('4'),
35    ('5'),
36    ('6'),
37    ('7'),
38    ('8'),
39    ('9'),
40    ('10'),
41    ('Jack'),
42    ('Queen'),
43    ('King')
44GO
45
46CREATE TABLE Suits
47(
48    Suit CHAR(8) PRIMARY KEY
49)
50GO
51
52INSERT INTO Suits
53VALUES
54    ('Clubs'),
55    ('Diamonds'),
56    ('Hearts'),
57    ('Spades')
58GO
59
60SELECT Id = IDENTITY(INT, 1, 1),
61    Suits.Suit,
62    Cards.Card
63INTO Deck
64FROM Cards
65CROSS JOIN Suits
66GO
67
68ALTER TABLE Deck ADD CONSTRAINT Deck_PK PRIMARY KEY (Id)
69GO
70
71-- User Defined Function 'PickACard'
72CREATE FUNCTION PickACard (@RAND FLOAT)
73RETURNS INT
74AS
75BEGIN
76    RETURN (
77			SELECT FLOOR(@RAND * (52 - 1 + 1)) + 1
78			)
79END
80GO
81
82-- Stored Procedure 'SwapCardsV1'
83CREATE PROCEDURE SwapCardsV1
84    @Card1 INT = 1,
85    @Card2 INT = 52
86AS
87BEGIN
88    
89    BEGIN TRY
90		BEGIN TRANSACTION
91
92		SET NOCOUNT ON
93
94		-- Store the first card's details
95		SELECT *
96        INTO #Temp
97        FROM Deck
98        WHERE Id = @Card1
99
100		-- Move second card into first card's place
101		UPDATE Deck
102		SET Card = CardDetails.Card,
103			Suit = CardDetails.Suit
104		FROM (
105			SELECT d.Card,
106            d.Suit
107        FROM Deck d
108        WHERE Id = @Card2
109			) CardDetails
110		WHERE Id = @Card1
111
112		-- Move first card into second card's place
113		UPDATE Deck
114		SET Card = CardDetails.Card,
115			Suit = CardDetails.Suit
116		FROM (
117			SELECT Card,
118            Suit
119        FROM #temp
120			) CardDetails
121		WHERE Id = @Card2
122
123		COMMIT TRANSACTION;
124	END TRY
125
126	BEGIN CATCH
127		-- PRINT 'We were unable to make the swap rolling back [' + ERROR_MESSAGE() + '].';
128		ROLLBACK TRANSACTION;
129        THROW 51000, 'Unable to swap card.', 1; 
130	END CATCH
131END
132GO
133
134-- Stored Procedure 'SwapCards'
135CREATE PROCEDURE SwapCards
136    @Card1 INT = 1,
137    @Card2 INT = 52
138AS
139BEGIN
140    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
141
142    BEGIN TRY
143		BEGIN TRANSACTION
144
145		SET NOCOUNT ON
146
147		-- Store the first card's details
148		SELECT *
149        INTO #Temp
150        FROM Deck
151        WHERE Id = @Card1
152
153		-- Move second card into first card's place
154		UPDATE Deck
155		SET Card = CardDetails.Card,
156			Suit = CardDetails.Suit
157		FROM (
158			SELECT d.Card,
159            d.Suit
160        FROM Deck d
161        WHERE Id = @Card2
162			) CardDetails
163		WHERE Id = @Card1
164
165		-- Move first card into second card's place
166		UPDATE Deck
167		SET Card = CardDetails.Card,
168			Suit = CardDetails.Suit
169		FROM (
170			SELECT Card,
171            Suit
172        FROM #temp
173			) CardDetails
174		WHERE Id = @Card2
175
176		COMMIT TRANSACTION;
177	END TRY
178
179	BEGIN CATCH
180		-- PRINT 'We were unable to make the swap rolling back [' + ERROR_MESSAGE() + '].';
181		ROLLBACK TRANSACTION;
182        THROW 51000, 'Unable to swap card.', 1; 
183	END CATCH
184END
185GO
186
187-- Stored Procedure 'SwapCardsAtRandom'
188CREATE PROCEDURE SwapCardsAtRandom
189AS
190BEGIN
191    DECLARE @Rnd1 FLOAT
192
193    SET @Rnd1 = RAND()
194
195    DECLARE @Card1 INT
196
197    SET @Card1 = (
198			SELECT dbo.PickACard(@Rnd1)
199			)
200
201    DECLARE @Rnd2 FLOAT
202
203    SET @Rnd2 = RAND()
204
205    DECLARE @Card2 INT
206
207    SET @Card2 = (
208			SELECT dbo.PickACard(@Rnd2)
209			)
210
211    -- 'No need to swap a card with itself'
212    IF @Card1 != @Card2
213	BEGIN
214        EXECUTE dbo.SwapCards @Card1,
215			@Card2
216    END -- IF @Card1 != @Card2
217END -- CREATE PROCEDURE SwapCards
218GO
219
220-- Call SwapCardsAtRandom Multiple times
221DECLARE @Counter INT
222SET @Counter = 0
223DECLARE @ErrorCounter INT
224SET @ErrorCounter = 0
225WHILE (@Counter < 1000)
226BEGIN
227
228    BEGIN TRY
229		EXECUTE SwapCardsAtRandom
230	END TRY
231
232	BEGIN CATCH
233		SET @ErrorCounter = @ErrorCounter+1
234        PRINT 'We were unable to swap cards [' + ERROR_MESSAGE() +']. Error Count ['+LTRIM(STR(@ErrorCounter,10))+']'  
235	END CATCH
236    
237    SET @Counter = @Counter + 1
238END
239GO
240
241-- Review results
242SELECT COUNT(*)
243FROM Deck
244GROUP BY Suit
245GO
246
247SELECT *
248FROM Deck
249ORDER BY Card,Suit
250GO