Lesson 2: Designing Transactions and Optimizing Locking

Estimated lesson time: 40 minutes

Always try to design a solution that minimizes both the amount of resources locked in a transaction and the risk of blocking and deadlocks. This lesson covers both using table locking options and alternative ways of reducing and optimizing locking.

Optimizing Locking

In addition to using the correct transaction isolation level, you can take several other steps to reduce locking and still get the correct results in your transactions. You can use table locking hints by applying the WITH(<locking_hint>) keyword after the table name in a DML statement. In this way, you can use a stricter transaction isolation level (such as serializable) or a leaner isolation level (such as read uncommitted) for a specific table in a transaction. There are several table locking hints available; the following are the most commonly used:

  • WITH (READUNCOMMITTED / READCOMMITTED / REPEATABLEREAD / SERIALIZABLE) These hints specify an alternate isolation level to use for the specific table. Note that you cannot specify the snapshot isolation level as a table locking hint.
  • WITH (NOLOCK) This hint is equal to specifying READUNCOMMITTED; use READUNCOMMITTED.
  • WITH (HOLDLOCK) This hint is equal to specifying SERIALIZABLE, but use SERIALIZABLE. “Hold lock” sounds more like it should be translated to repeatable read; however, HOLDLOCK is translated to serializable because of the lack of row locks in SQL Server versions prior to 7.0. Because row locks were not available, HOLDLOCK could protect against phantom reads. Because of this backward-compatibility issue, HOLDLOCK is translated to serializable.
  • WITH (READPAST) READPAST causes SQL Server to skip locked rows instead of waiting for the locks to be released. In SQL Server 2005, READPAST is also available for UPDATE and DELETE statements. READPAST is usually used to mimic a queuing mechanism. If this is what is needed, consider implementing a SQL Server 2005 Service Broker solution instead. Service Broker natively provides queues and is implemented using T-SQL. A great book about SQL Server 2005 Service Broker is The Rational Guide to SQL Server 2005 Service Broker by Roger Wolter (Rational Press, 2006).
  • WITH (XLOCK) This hint is used in DML statements to cause SQL Server to acquire exclusive locks instead of shared locks. The exclusive locks acquired will be held for the remainder of the transaction.
  • WITH (UPDLOCK) This hint is used in DML statements to cause SQL Server to acquire update locks instead of shared locks. The update locks acquired will be held for the remainder of the transaction. Update locks are compatible with shared locks but are not compatible with other update locks. Therefore, SELECT statements that acquire update locks will be blocked, while SELECT statements that only intend to read the data, and not change it, are allowed. WITH (UPDLOCK) is usually specified if data is to be read first and updated later. Because of this, it can be referred to as a “SELECT WITH INTENT TO UPDATE”.
  • WITH (NOWAIT) This hint is equal to setting LOCK_TIMEOUT to 0. For example, SQL Server will return an error message and stop execution of the current statement if it encounters a lock that it would need to wait for—on the specific table!

For more information about table locking hints, see the SQL Server 2005 Books Online articles “Locking Hints” at https://msdn2.microsoft.com/en-us/library/ms189857.aspx or “Table Hint (Transact-SQL)” at https://msdn2.microsoft.com/en-us/library/ms187373.aspx.

Exam Tip

For this exam, it is recommended that you know and understand the behaviors of the different locking hints.

In addition to using locking hints, the OUTPUT keyword for INSERT, UPDATE, and DELETE statements can also be very helpful. This keyword makes it possible to know which rows were accessed (inserted, updated, or deleted) by a specific DML statement. Following is an example of how concurrency problems can be solved without increased locking. The example is a simplified account-withdrawal transaction. (The lab for this lesson includes a more complex example that uses an account transactions table instead of just storing the account balance in a column of the accounts table.)

Here’s an example of the implementation in pseudo code:

If(Enough money in account)
    Withdraw the money
Else
    Return an error message

Here, this transaction is translated to T-SQL, and the test table used in the example is included.

CREATE TABLE Test.Accounts (
    AccountNumber INT PRIMARY KEY
    ,Balance DECIMAL(19,5) NOT NULL
);
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED – Default...
BEGIN TRAN;
    IF EXISTS (SELECT * FROM Test.Accounts
           WHERE AccountNumber = @AccountNumber
             AND Balance >= @AmountToWithdraw)
        UPDATE Test.Accounts SET
        Balance = Balance - @AmountToWithdraw
        WHERE AccountNumber = @AccountNumber;
    ELSE
        BEGIN
        RAISERROR('Not enough funds in account!', 16, 1);
        ROLLBACK TRAN;
        RETURN;
        END
COMMIT TRAN;

What is wrong with this transaction? There is one specific issue. There is a chance that the balance of the account will be updated between the execution of the SELECT statement and the execution of the UPDATE statement. This example shows the problem of two users (connections) trying to withdraw all the money in the account at the same time:

CREATE TABLE Test.Accounts (
    AccountNumber INT PRIMARY KEY
    ,Balance DECIMAL(19,5) NOT NULL
);
INSERT Test.Accounts (AccountNumber, Balance)
    VALUES (1001, 500);

Cc546505.table_C06623835_10(en-us,TechNet.10).png

DROP TABLE Test.Accounts;

In this case, it is possible to withdraw more money from the account than what was available because the shared lock issued by the SELECT statement is not held for the duration of the transaction. This enables the update in Connection 2 to withdraw money from the account even though the transaction in Connection 1 has not yet completed. To solve this problem, you can change the transaction isolation level to repeatable read, which will cause Connection 1 to keep its lock on the row for the entire transaction, as follows:

CREATE TABLE Test.Accounts (
    AccountNumber INT PRIMARY KEY
    ,Balance DECIMAL(19,5) NOT NULL
);
INSERT Test.Accounts (AccountNumber, Balance)
    VALUES (1001, 500);

Cc546505.table_C06623835_11(en-us,TechNet.10).png

DROP TABLE Test.Accounts;

This solution solves the problem of two connections being able to overdraw the account if executed at the same time. However, it introduces a potential deadlock problem. Deadlocks seriously degrade performance and should be avoided if possible. Instead of using the repeatable read isolation level, you can use the UPDLOCK lock hint or XLOCK lock hint. If UPDLOCK is specified for the SELECT statement, only the first transaction would get the lock, because update locks are not compatible with other update locks. If UPDLOCK is used, it is important to use it wherever data is first read and then updated. If UPDLOCK is left out of some transactions, the solution won’t work because those transactions will acquire shared locks instead of update locks and could cause deadlocks to occur. If you are not sure whether you are using UPDLOCK in all instances in which it is needed, consider implementing the XLOCK hint, because exclusive locks are not compatible with shared locks. Here is the updated transaction using update locks:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
    IF EXISTS (SELECT * FROM Test.Accounts WITH (UPDLOCK)
           WHERE AccountNumber = @AccountNumber
             AND Balance >= @AmountToWithdraw)
        UPDATE Test.Accounts SET
        Balance = Balance - @AmountToWithdraw
        WHERE AccountNumber = @AccountNumber;
    ELSE
        BEGIN
        RAISERROR('Not enough funds in account!', 16, 1);
        ROLLBACK TRAN;
        RETURN;
        END
COMMIT TRAN;

However, there is an even simpler and better-performing solution: skip the IF and SELECT statements. Instead, execute the UPDATE statement, include the balance check in the WHERE clause, and check whether the UPDATE statement updated the account by querying the @@ROWCOUNT function. When possible, executing fewer statements is usually a better solution.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
    UPDATE Test.Accounts SET
        Balance = Balance - @AmountToWithdraw
    WHERE AccountNumber = @AccountNumber
        AND Balance >= @AmountToWithdraw;
    IF(@@ROWCOUNT <> 1)
    BEGIN
        RAISERROR('Not enough funds in account!', 16, 1);
        ROLLBACK TRAN;
    END
ELSE
     COMMIT TRAN;

This solution is very useful but works only if there is one row to be updated. If more rows need to be updated, you need to use the OUTPUT clause. Typically, you would execute the UPDATE statement and then OUTPUT the accessed rows into a table variable. You can then query the table variable to find out which rows were updated. Below is a simple example of updating the price of products in a specific category, and then returning the average price for the products that were updated. To make sure that the SELECT statement that returns the average price does not return phantom rows, either the snapshot transaction isolation level or the serializable transaction isolation level would need to be used. In this case, the OUTPUT clause protects against phantom rows without added locking or use of row versions.

USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
    DECLARE @tmp TABLE (ProductID INT PRIMARY KEY);
    UPDATE Production.Product SET
        ListPrice = ListPrice * 1.1
    OUTPUT INSERTED.ProductID INTO @tmp (ProductID)
    WHERE ProductSubcategoryID = 17;
    SELECT AVG(p.ListPrice) AS NewAvgPrice
    FROM Production.Product AS p
    WHERE p.ProductID IN (
        SELECT t.ProductID FROM @tmp AS t
    );
COMMIT TRAN;

Exam Tip

For this exam, knowledge of alternate solutions is important.

Minimizing Deadlocks

To minimize deadlocks, you should follow three guidelines:

  • Minimize the time during which transactions are open by keeping transactions short.

  • Minimize locking. Use the least-restrictive transaction isolation level, and implement more-restrictive locking by using table locking hints rather than the SET TRANSACTION ISOLATION LEVEL. The table locking hint only affects one table, while the SET statement affects all tables in the transaction.

  • Access tables in the same order in all transactions. This reduces the risk of deadlocks resulting from transactions crossing paths. Here is an example of two transactions that can potentially be deadlocked due to the tables not being accessed in the same order in each transaction.

    Cc546505.table_C06623835_12(en-us,TechNet.10).png

The typical solution for avoiding these types of deadlocks is to always access tables in the order parent to child, such as Customer to Order. What if you first need to read the order (child) and then update the customer (parent) based on the previous select? First, you can probably do this by executing an UPDATE statement with a subquery. If not, you can start by executing a dummy statement to lock the parent row. (To avoid firing triggers, use a SELECT statement, rather than an UPDATE statement, as the dummy statement.) For example:

DECLARE @x INT;
BEGIN TRAN;
    -- Lock parent row.
    SET @x = (SELECT 1 FROM Customer WITH (XLOCK)
          WHERE CustomerID = @CustomerID)
    -- Now access the child rows...
...

Working with Distributed Transactions

Distributed transactions are needed when a transaction covers more than one resource manager. A SQL Server instance is a typical resource manager. When working with multiple resource managers, a transaction manager is used to manage the transaction. The transaction manager that is installed with Windows is the Distributed Transaction Coordinator (also known as MS DTC).

Server-Side Distributed Transactions in T-SQL

When T-SQL transactions contain write operations that affect both local and remote objects on linked servers, a distributed transaction is automatically started, and the Distributed Transaction Coordinator (DTC) service is engaged to manage the distributed transaction. If the DTC service is not started on all involved machines, an error message is thrown. A distributed transaction can also be manually started by executing the BEGIN DISTRIBUTED TRANSACTION statement. Note that the snapshot transaction isolation level is not supported for distributed transactions.

Application-Side Distributed Transactions in .NET Framework 2.0

When developing applications using .NET Framework 2.0, the System.Transactions namespace (which is found in the System.Transactions.dll assembly) can be used to manage transactions. If only one SQL Server instance is accessed in a transaction, System.Transactions uses a local SQL Server transaction. However, if more resource managers are included in the transaction, System.Transactions automatically promotes the local transaction to a distributed transaction and involves the MS DTC to manage the transaction. This means that System.Transactions is ideal to use for all transactions that you want to manage from the client side, even if they only involve one SQL Server instance. Following is an application sample implementation of System.Transactions:

//C#
using System.Transactions;
using System.Data.SqlClient;
//Start a new transaction.
using (TransactionScope tran = new TransactionScope())
{
    using (SqlConnection connSqlServer1 = new
           SqlConnection(connString1))
    {
        connSqlServer1.Open(); //Opens first connection.
        //The transaction is now local to the first SQL Server
        //instance.
        using(SqlConnection connSqlServer2 = new
              SqlConnection(connString2))
        {
            connSqlServer2.Open(); // Opens second connection.
            //The transaction is promoted to a distributed
            //transaction.
        }
    }
    //Commit the transaction.
    tran.Complete();
}
'VB
Imports System.Transactions
Imports System.Data.SqlClient
'Start a new transaction.
Using tran As New TransactionScope()
    Using connSqlServer1 As New SqlConnection(connString1)
        connSqlServer1.Open() 'Opens first connection.
        'The transaction is now local to the first SQL Server
        'instance.
        Using connSqlServer2 As New SqlConnection(connString2)
            connSqlServer2.Open() 'Opens second connection.
            'The transaction is promoted to a distributed
            'transaction.
        End Using
    End Using
    'Commit the transaction.
    tran.Complete()
End Using 

Lab: Designing Code That Uses Transactions

In this lab, you create a stored procedure that will be used to withdraw money from a bank account. The stored procedure should make sure that no concurrency problems occur and that the account cannot be overdrawn.

IMPORTANT

Lab requirements You will need to have SQL Server 2005 and the Adventure Works database installed before you can complete this lab. Refer to the Introduction for setup instructions.

Exercise 1: Use the Default Isolation Level

In this exercise, you create the draft for the stored procedure and use the read committed transaction isolation level.

  1. Open SQL Server Management Studio, and connect to an instance of SQL Server 2005.

  2. Open a new query window, and type and execute the following SQL statements. This will create the TestDB database, the Test schema, and the tables that are used in this exercise: you will also create the Test.spAccountReset stored procedure. You can execute this procedure to reset the data in the tables if you need to restart the exercise.

    CREATE DATABASE TestDB;
    GO
    USE TestDB;
    GO
    CREATE SCHEMA Test;
    GO
    CREATE TABLE Test.Accounts (
        AccountNumber INT PRIMARY KEY
    );
    CREATE TABLE Test.AccountTransactions (
        TransactionID INT IDENTITY PRIMARY KEY
        ,AccountNumber INT NOT NULL REFERENCES Test.Accounts
        ,CreatedDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ,Amount DECIMAL(19, 5) NOT NULL
    );
    GO
    CREATE PROC Test.spAccountReset
    AS
    BEGIN
        SET NOCOUNT ON;
        DELETE Test.AccountTransactions;
        DELETE Test.Accounts;
        INSERT Test.Accounts (AccountNumber) VALUES (1001);
        INSERT Test.AccountTransactions (AccountNumber, Amount)
        VALUES (1001, 100);
        INSERT Test.AccountTransactions (AccountNumber, Amount)
        VALUES (1001, 500);
        INSERT Test.AccountTransactions (AccountNumber, Amount)
        VALUES (1001, 1400);
        SELECT AccountNumber, SUM(Amount) AS Balance
        FROM Test.AccountTransactions
        GROUP BY AccountNumber;
    END
    
  3. Open another query window, and type and execute the following SQL statements to create the Test.spAccountWithdraw stored procedure:

    USE TestDB;
    GO
    CREATE PROC Test.spAccountWithdraw
    @AccountNumber INT
    ,@AmountToWithdraw DECIMAL(19, 5)
    AS
    BEGIN
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
        BEGIN TRY
            IF(@AmountToWithdraw <= 0)
                RAISERROR('@AmountToWithdraw must be > 0.', 16, 1);
            BEGIN TRAN;
                -- Verify that the account exists...
                IF NOT EXISTS(
                        SELECT *
                        FROM Test.Accounts
                        WHERE AccountNumber = @AccountNumber
                    )
                    RAISERROR('Account not found.', 16, 1);
                -- Verify that the account will not be overdrawn...
                IF (@AmountToWithdraw > (
                        SELECT SUM(Amount)
                        FROM Test.AccountTransactions
                        WHERE AccountNumber = @AccountNumber)
                    )
                    RAISERROR('Not enough funds in account.', 16, 1);
                -- ** USED TO TEST CONCURRENCY PROBLEMS **
                RAISERROR('Pausing procedure for 10 seconds...', 10, 1)
                    WITH NOWAIT;
                WAITFOR DELAY '00:00:30';
                RAISERROR('Procedure continues...', 10, 1) WITH NOWAIT;
                -- Make the withdrawal...
                INSERT Test.AccountTransactions (AccountNumber, Amount)
                    VALUES (@AccountNumber, -@AmountToWithdraw);
                -- Return the new balance of the account:
                SELECT SUM(Amount) AS BalanceAfterWithdrawal
                FROM Test.AccountTransactions
                WHERE AccountNumber = @AccountNumber;
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(2047);
            SET @ErrorMessage = ERROR_MESSAGE();
            RAISERROR(@ErrorMessage, 16, 1);
            -- Should also use ERROR_SEVERITY() and ERROR_STATE()...
            IF(XACT_STATE() <> 0)
                ROLLBACK TRAN;
        END CATCH
    END
    
  4. Open another query window, which will be referred to as Connection 1, and type and execute the following SQL statement to prepare the connection:

    Connection 1
    /* Leave the above line to easily see that this query window
    belongs to Connection 1. */
    USE TestDB
    GO 
    Reset/generate the account data
    EXEC Test.spAccountReset;
    
  5. Open another query window, which will be referred to as Connection 2, and type and execute the following SQL statement to prepare the connection:

    Connection 2
    /* Leave the above line to easily see that this query window
    belongs to Connection 2. */
    USE TestDB
    GO
    
  6. In this step, you will execute two batches at the same time to try to test for concurrency problems. In both the Connection 1 and Connection 2 query windows, type the following SQL statements without executing them yet. The statements will first retrieve the current account balance and then attempt to empty the account.

    SELECT SUM(Amount) AS BalanceBeforeWithdrawal
    FROM Test.AccountTransactions
    WHERE AccountNumber = 1001;
    GO
    EXEC Test.spAccountWithdraw @AccountNumber = 1001,
                    @AmountToWithdraw = 2000;
    

    To get a better view of what will happen, press Ctrl+T in SQL Server Management Studio to set results to be returned as text instead of grids. Do this for both query windows. Now, start the execution in both query windows simultaneously and wait for both batches to finish execution. (This should take approximately 30 seconds because of the WAITFOR DELAY statement in the Test.spAccountWithdraw stored procedure.) Both connections’ batches should return two result sets; the first result set will contain the current account balance (which should be 2,000 for both batches), and the second result set will contain the account balance after the withdrawal. What was the result of the two withdrawals? Was the account overdrawn? What kind of concurrency problem occurred (if any)?

  7. Close all open query windows except one, and in that query window, type and execute the following SQL statements to clean up after this exercise:

    >USE master;
    GO
    DROP DATABASE TestDB;
    

Exercise 2: Use a Locking Hint

In the previous exercise, you encountered the “phantom reads” concurrency problem. In this exercise, you re-create the stored procedure, but this time, you will use the serializable locking hint to protect against phantom reads.

  1. Open SQL Server Management Studio, and connect to an instance of SQL Server 2005.

  2. Open a new query window, and type and execute the following SQL statements. This will create the TestDB database, the Test schema, and the tables that you will use in this exercise. You will also create the Test.spAccountReset stored procedure. You can execute this procedure to reset the data in the tables if you need to restart the exercise.

    CREATE DATABASE TestDB;
    GO
    USE TestDB;
    GO
    CREATE SCHEMA Test;
    GO
    CREATE TABLE Test.Accounts (
        AccountNumber INT PRIMARY KEY
    );
    CREATE TABLE Test.AccountTransactions (
        TransactionID INT IDENTITY PRIMARY KEY  
        ,AccountNumber INT NOT NULL REFERENCES Test.Accounts
        ,CreatedDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ,Amount DECIMAL(19, 5) NOT NULL
    );
    GO
    CREATE PROC Test.spAccountReset
    AS
    BEGIN
        SET NOCOUNT ON;
        DELETE Test.AccountTransactions;
        DELETE Test.Accounts;
        INSERT Test.Accounts (AccountNumber) VALUES (1001);
        INSERT Test.AccountTransactions (AccountNumber, Amount)
        VALUES (1001, 100);
        INSERT Test.AccountTransactions (AccountNumber, Amount)
        VALUES (1001, 500);
        INSERT Test.AccountTransactions (AccountNumber, Amount)
        VALUES (1001, 1400);
        SELECT AccountNumber, SUM(Amount) AS Balance
        FROM Test.AccountTransactions
        GROUP BY AccountNumber;
    END
    
  3. Open another query window, and type and execute the following SQL statements to create the Test.spAccountWithdraw stored procedure:

    USE TestDB;
    GO
    CREATE PROC Test.spAccountWithdraw
    @AccountNumber INT
    ,@AmountToWithdraw DECIMAL(19, 5)
    AS
    BEGIN
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
        BEGIN TRY
            IF(@AmountToWithdraw <= 0)
                RAISERROR('@AmountToWithdraw must be > 0.', 16, 1);
            BEGIN TRAN;
                -- Verify that the account exists...
                IF NOT EXISTS(
                        SELECT *
                        FROM Test.Accounts
                        WHERE AccountNumber = @AccountNumber
                    )
                    RAISERROR('Account not found.', 16, 1);
                -- Verify that the account will not be overdrawn...
                IF (@AmountToWithdraw > (
                        SELECT SUM(Amount)
                        FROM Test.AccountTransactions WITH(SERIALIZABLE)
                        WHERE AccountNumber = @AccountNumber)
                    )
                    RAISERROR('Not enough funds in account.', 16, 1);
                -- ** USED TO TEST CONCURRENCY PROBLEMS **
                RAISERROR('Pausing procedure for 10 seconds...', 10, 1)
                    WITH NOWAIT;
                WAITFOR DELAY '00:00:30';
                RAISERROR('Procedure continues...', 10, 1) WITH NOWAIT;
                -- Make the withdrawal...
                INSERT Test.AccountTransactions (AccountNumber, Amount)
                    VALUES (@AccountNumber, -@AmountToWithdraw);
    
                -- Return the new balance of the account:
                SELECT SUM(Amount) AS BalanceAfterWithdrawal
                FROM Test.AccountTransactions
                WHERE AccountNumber = @AccountNumber;
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(2047);
            SET @ErrorMessage = ERROR_MESSAGE();
            RAISERROR(@ErrorMessage, 16, 1);
            -- Should also use ERROR_SEVERITY() and ERROR_STATE()...
            IF(XACT_STATE() <> 0)
                ROLLBACK TRAN;
        END CATCH
    END
    
  4. Open another query window, which will be referred to as Connection 1, and type and execute the following SQL statement to prepare the connection:

    Connection 1
    /* Leave the above line to easily see that this query window
    belongs to Connection 1. */
    USE TestDB;
    GO
    Reset/generate the account data
    EXEC Test.spAccountReset;
    
  5. Open another query window, which will be referred to as Connection 2, and type and execute the following SQL statement to prepare the connection:

    Connection 2
    /* Leave the above line to easily see that this query window
    belongs to Connection 2. */
    USE TestDB;
    GO
    
  6. In this step, you will execute two batches at the same time to try to test for concurrency problems. In both the Connection 1 and Connection 2 query windows, type the following SQL statements without executing them yet. The statements will first retrieve the current account balance and then attempt to empty the account.

    SELECT SUM(Amount) AS BalanceBeforeWithdrawal
    FROM Test.AccountTransactions
    WHERE AccountNumber = 1001;
    GO
    EXEC Test.spAccountWithdraw @AccountNumber = 1001,
                    @AmountToWithdraw = 2000;
    

    To get a better view of what will happen, press Ctrl+T in SQL Server Management Studio to set results to be returned as text instead of grids. Do this for both query windows. Now, start the execution in both query windows simultaneously and wait for both batches to finish execution. (This should take approximately 30 seconds because of the WAITFOR DELAY statement in the Test.spAccountWithdraw stored procedure.) Both connections’ batches should return two result sets; the first result set will contain the current account balance (which should be 2,000 for both batches), and the second result set will contain the account balance after the withdrawal. What was the result of the two withdrawals? Was the account overdrawn? What kind of concurrency problem occurred (if any)? Was there any other problem with this implementation?

  7. Close all open query windows except one, and in that query window, type and execute the following SQL statements to clean up after this exercise:

    USE master;
    GO
    DROP DATABASE TestDB;
    

Exercise 3: Use an Alternative Solution

In Exercise 2, the account was not overdrawn, and you didn’t experience any concurrency problems. The connections were instead deadlocked. In this exercise, you re-create the stored procedure to protect against both phantom reads and deadlocks by changing the implementation slightly.

  1. Open SQL Server Management Studio, and connect to an instance of SQL Server 2005.

  2. Open a new query window, and type and execute the following SQL statements. This will create the TestDB database, the Test schema, and the tables that will be used in this exercise: you will also create the Test.spAccountReset stored procedure. You can execute this procedure to reset the data in the tables if you need to restart the exercise.

    CREATE DATABASE TestDB;
    GO
    USE TestDB;
    GO
    CREATE SCHEMA Test;
    GO
    CREATE TABLE Test.Accounts (
        AccountNumber INT PRIMARY KEY
    );
    CREATE TABLE Test.AccountTransactions (
        TransactionID INT IDENTITY PRIMARY KEY
        ,AccountNumber INT NOT NULL REFERENCES Test.Accounts
        ,CreatedDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ,Amount DECIMAL(19, 5) NOT NULL
    );
    GO
    CREATE PROC Test.spAccountReset
    AS
    BEGIN
        SET NOCOUNT ON;
        DELETE Test.AccountTransactions;
        DELETE Test.Accounts;
        INSERT Test.Accounts (AccountNumber) VALUES (1001);
        INSERT Test.AccountTransactions (AccountNumber, Amount)
        VALUES (1001, 100);
        INSERT Test.AccountTransactions (AccountNumber, Amount)
        VALUES (1001, 500);
        INSERT Test.AccountTransactions (AccountNumber, Amount)
        VALUES (1001, 1400);
        SELECT AccountNumber, SUM(Amount) AS Balance
        FROM Test.AccountTransactions
        GROUP BY AccountNumber;
    END
    
  3. Open another query window, and type and execute the following SQL statements to create the Test.spAccountWithdraw stored procedure:

    USE TestDB;
    GO
    CREATE PROC Test.spAccountWithdraw
    @AccountNumber INT
    ,@AmountToWithdraw DECIMAL(19, 5)
    AS
    BEGIN
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
        BEGIN TRY
            IF(@AmountToWithdraw <= 0)
                RAISERROR('@AmountToWithdraw must be > 0.', 16, 1);
            BEGIN TRAN;
                -- Verify that the account exists
                -- and LOCK the account from access by other queries
                -- that will write to the account or its transactions.
                -- Note that SELECT statements against the account
                -- will still be allowed.
                IF NOT EXISTS(
                        SELECT *
                        FROM Test.Accounts WITH (UPDLOCK)
                        WHERE AccountNumber = @AccountNumber
                    )
                    RAISERROR('Account not found.', 16, 1);
                -- Verify that the account will not be overdrawn...
                IF (@AmountToWithdraw > (
                        SELECT SUM(Amount)
                        FROM Test.AccountTransactions /* NO LOCKING HINT */
                        WHERE AccountNumber = @AccountNumber)
                    )
                    RAISERROR('Not enough funds in account.', 16, 1);
                -- ** USED TO TEST CONCURRENCY PROBLEMS **
                RAISERROR('Pausing procedure for 10 seconds...', 10, 1)
                    WITH NOWAIT;
                WAITFOR DELAY '00:00:30';
                RAISERROR('Procedure continues...', 10, 1) WITH NOWAIT;
                -- Make the withdrawal...
                INSERT Test.AccountTransactions (AccountNumber, Amount)
                    VALUES (@AccountNumber, -@AmountToWithdraw);
                -- Return the new balance of the account:
                SELECT SUM(Amount) AS BalanceAfterWithdrawal
                FROM Test.AccountTransactions
                WHERE AccountNumber = @AccountNumber;
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(2047);
            SET @ErrorMessage = ERROR_MESSAGE();
            RAISERROR(@ErrorMessage, 16, 1);
            -- Should also use ERROR_SEVERITY() and ERROR_STATE()...
            IF(XACT_STATE() <> 0)
                ROLLBACK TRAN;
        END CATCH
    END
    
  4. Open another query window, which will be referred to as Connection 1, and type and execute the following SQL statement to prepare the connection:

    Connection 1
    /* Leave the above line to easily see that this query window
    belongs to Connection 1. */
    USE TestDB
    GO
    Reset/generate the account data
    EXEC Test.spAccountReset;
    
  5. Open another query window, which will be referred to as Connection 2, and type and execute the following SQL statement to prepare the connection:

    Connection 2
    /* Leave the above line to easily see that this query window
    belongs to Connection 2. */
    USE TestDB
    GO
    
  6. In this step, you will execute two batches at the same time to try to test for concurrency problems. In both the Connection 1 and Connection 2 query windows, type the following SQL statements without executing them yet. The statements will first retrieve the current account balance and will then attempt to empty the account.

    SELECT SUM(Amount) AS BalanceBeforeWithdrawal
    FROM Test.AccountTransactions
    WHERE AccountNumber = 1001;
    GO
    EXEC Test.spAccountWithdraw @AccountNumber = 1001,
                    @AmountToWithdraw = 2000;
    

    To get a better view of what will happen, press Ctrl+T in SQL Server Management Studio to set results to be returned as text instead of grids. Do this for both query windows. Now, start the execution in both query windows simultaneously and wait for both batches to finish execution. (This should take approximately 30 seconds because of the WAITFOR DELAY statement in the Test.spAccountWithdraw stored procedure.) Both connections’ batches should return two result sets; the first result set will contain the current account balance (which should be 2,000 for both batches), and the second result set will contain the account balance after the withdrawal. What was the result of the two withdrawals? Was the account overdrawn? What kind of concurrency problem occurred (if any)? Was there any other problem with this implementation?

  7. Close all open query windows except one, and in that query window, type and execute the following SQL statements to clean up after this exercise:

    USE master;
    GO
    DROP DATABASE TestDB;
    

Quick Check

  1. What is the difference between the READPAST and READUNCOMMITTED table locking hints?
  2. Which transaction isolation level cannot be specified as a table locking hint?
  3. What is the advantage of locking larger resources, such as tables, instead of rows?
  4. What is the main advantage of locking smaller resources, such as rows, instead of tables or pages?

Quick Check Answers

  1. Both the READPAST and READUNCOMMITTED table locking hints will prevent SELECT (and also UPDATE/DELETE for READPAST) statements from being blocked by resources locked exclusively by other transactions. The difference is that READUNCOMMITTED will return the dirty values for locked resources, while READPAST will simply skip them. (That is, it will not return them at all).
  2. The snapshot isolation level cannot be specified as a table locking hint.
  3. The advantage of locking larger resources is that it will reduce the work required by the SQL Server lock manager to allocate locks (because far fewer locks are allocated), and it will also reduce the memory used to maintain locks.
  4. The main advantage of locking smaller resources is that it greatly reduces the risk of blocking and deadlocks.

< Back      Next >

 

 

© Microsoft. All Rights Reserved.