Chapter 12 - Understanding Transactions and Locking

Most marketing literature has attempted to tell us, as application developers, that SQL Server is going to manage locking and that there is nothing to worry about. This is a very optimistic locking approach. Unfortunately, it is not very pragmatic because it assumes that there is nothing a programmer or user can do to explicitly cause locking. In fact, there are many situations that will cause a large amount of locking to occur on a server, potentially disabling it for the enterprise that it is supporting.

Background Information on Locking

• Page Sizes and Granularity of Data

• Types of Locks

Page Sizes and Granularity of Data

How does this affect a database application? One of the most important considerations when writing a multiuser application is that there must be a way for multiple users to work independently of one another. For example, two users must be able to update customer records at the same time while answering phone calls from customers. The greater the capability to manipulate data in the same table without affecting other users by locks, the greater the concurrency of an application and the greater the chance of being able to support a lot of users.

A highly accessed table (such as a table of unique values for the rest of the system) should be made as concurrent as possible by forcing as few as possible rows of data onto the same data page—thereby limiting the number of coincidental rows locked as the result of a user action. Additionally, users transactions should be kept to a minimum duration when hitting these tables.

Two other types of locks can occur that lock data more greatly than a singe data page: table and extent. Table locks occur because a user issued a query to update a table without including a WHERE clause (thereby implicitly saying that "I want to update every row"), and when the number of data pages locked exceeds the Lock Escalation Threshold defined for the particular table or database. Extent locks occur when SQL Server needs to create a new database extent (eight pages of data) to respond to a user query. Unfortunately, there are no controls at our disposal to handle or deal with extent locks, so you simply should know that they occur and what they mean. For more information, see the section entitled LE Thresholds later in this chapter.

Types of Locks

Multiple processes may have SHARED locks on the same data page, and they are usually acquired when data is being read. Importantly though, no other process may take an EXCLUSIVE lock (to perform DML) until all SHARED locks have been released.

EXCLUSIVE locks of table pages are given to a process that is updating a record on a page, inserting a new record at the end of a page, or when a process deletes a record from a page. EXCLUSIVE locks disallow any other process from accessing the page.

The UPDATE lock type is a middling lock. It sits in between SHARED and EXCLUSIVE in that it will allow a process to acquire a SHARE on the page until an actual update has occurred on it. UPDATE locks are acquired when a CURSOR is being built in the server. UPDATE locks are automatically promoted to EXCLUSIVE when an update occurs on one of the pages associated with the cursor.

At the table level, SQL Server has SHARED and EXCLUSIVE locks that work in the same fashion as the page level. SQL Server also has INTENT locks. INTENT locks indicate that a table has a number of pages on it that SQL Server is intending to lock at the page level in response to a user process.

SQL Server 6.5 has added insert row-level locking. This new lock allows multiple users to insert records into the same page. It was added because of a large amount of contention with inserts at the end of tables.

For additional information, see Chapter 15, "Creating and Using Cursors"

Defining Isolation Levels

Transaction isolation levels are set for the entire time that a session is connected to the database. If you change isolation levels for a specific part of your application, do not forget to change back to the default so that other parts of the application are not adversely affected.

To achieve the same effects as isolation levels for a single SELECT statement, refer to the section below, Holding a Lock Explicitly for more information.

Read Committed

If it is important that the query's results be completely unaffected by other users during the life of a particular transaction, so make sure that you use the Repeatable Read isolation level.

To set your isolation level to Read Committed, perform the following SQL:

Set Transaction Isolation Level Read Committed
Go

The Read Uncommitted transaction isolation level is not recommended for any applications that require data integrity because you cannot be guaranteed that the data you are working with is still as it was or, indeed, in the database at all. Use Read Uncommitted sparingly in your applications and possibly only for such procedures as reporting applications on tables that are statistically unaffected by the average transactions that post against your server.

To set your isolation level to Read Uncommitted, perform the following SQL transaction:

Set Transaction Isolation Level Read Uncommitted
Go

Repeatable Read (a.k.a. Serializable)

To set your isolation level to Repeatable Read perform the following SQL transaction:

Set Transaction Isolation Level Repeatable Read
Go

Creating and Working with Transactions

It is very important to remember that every BEGIN TRAN must be followed at some point in the code by a matching COMMIT TRAN or ROLLBACK TRAN. Transactions must begin and end in pairs, otherwise the server will continue holding locks until the client is disconnected.

BEGIN TRAN

It is possible to issue operations without a BEGIN TRAN statement, and they will affect a database. However, you will not be able to conditionally undo the work that you sent to the server if it is not preceded by a BEGIN TRAN so that SQL Server knows to what state the database must be returned.

SQL Server's transaction logs monitor those transactions that are contained inside of BEGIN and COMMIT statements. In the event of a media failure on a database before data is physically changed on the database, SQL Server will recover or ensure that those changes are applied by "rolling forward" those unapplied transactions to the database when the server is next brought back online.

COMMIT TRAN

ROLLBACK TRAN

SQL Server will enable you to call remote stored procedures inside a transaction; however, because of the nature of the Remote Procedure Call (RPC) interface with the other server upon which the RPC executed, SQL Server will not be able to ROLLBACK any such calls. Take care when writing applications that require RPCs that there are additional RPCs to programmatically undo the work you did previously.

DDL and Database Statements

ALTER TABLE        CREATE DEFAULT     CREATE INDEX
CREATE PROCEDURE   CREATE RULE        CREATE TABLE
CREATE TRIGGER     CREATE VIEW        DROP DEFAULT
DROP INDEX         DROP PROCEDURE     DROP RULE
DROP TABLE         DROP TRIGGER       DROP VIEW
GRANT & REVOKE     SELECT INTO        TRUNCATE TABLE

Using Named Transactions and SavePoints

A new type of transaction has been added that allows transactions to be distributed. Such transactions are controlled by the Distributed Transaction Coordinator. This type of transaction is very useful for transactions that occur at remote locations but affect centralized inventory levels.

However, even the most careful programmer will find that it becomes a bit of a nightmare to remember how many indents to ROLLBACK out of in the event of an error condition or some programmatic constraint. Named Transactions and SavePoints are used for just this purpose: they provide a way of rolling back work to a given named or saved portion of the code that has been executing (even if it is at a higher nesting level).

Named Transactions

/* Open outer transaction */
Begin Tran UPDATE_AUTHORS
      Update AUTHORS
      Set    CONTRACT = 1
      Where  AU_ID = '341-22-1782'

      /* Open inner transaction */
      Begin Tran UPDATE_TITLEAUTHOR
            Update TITLEAUTHOR
            Set   ROYALTYPER = ROYALTYPER + 25
            Where AU_ID = '341-22-1782'
            If @@error != 0
            Begin
                  Rollback Tran UPDATE_TITLEAUTHOR
                  Print 'Failed to update Royalties'
                  Return
            End
      Commit Tran UPDATE_TITLEAUTHOR
Commit Tran UPDATE_AUTHORS

Using SavePoints

SAVE TRANSACTION <TRAN_NAME>

The text for this statement can be found in 12_06.SQL on the CD-ROM.

Begin Tran
      Update AUTHORS
      Set    CONTRACT = 1
      Where  AU_ID = '341-22-1782'

      /* save our work to this point */
      Save Transaction AuthorDone

      Update TITLEAUTHOR
            Set    ROYALTYPER = ROYALTYPER + 25
            Where  AU_ID = '341-22-1782'
      If @@error != 0 Or @@RowCount > 1
      Begin
            /* rollback and exit */
            Rollback Tran AcctDataDone
            Print 'Error occurred when updating TitleAuthor'
            Return
      End
Commit Tran
Print 'Transaction Committed'

I have an application that seems to continuously hold locks after the first transaction executes. I'm sure that I'm committing properly. What's going on?

The most likely scenario is that you have issued more BEGIN TRANs than you have corresponding COMMIT TRANs or ROLLBACK TRANs. Remember that transactions must be enclosed in pairs of BEGIN and COMMIT/ROLLBACK. If you fail to do so, SQL Server will think that you want to keep the transaction open for a longer period.

To help identify your code problems, do a walk through of your application, and monitor error conditions carefully. Chances are that an error condition is occurring and some code is returning control before closing an open transaction. Also, check the value of the system variable @@trancount to tell you how deeply nested in transactions you really are.

Serialized Columns without IDENTITY

For some applications that must support multiple database back ends and for those applications that require SQL Server 4.x compatibility, it is possible to implement the same kind of feature as an IDENTITY column by performing the following steps:

1. Create a table with columns in it to store a table name and the current value. (The text for all the following statements can be found in 12_07.SQL on the CD-ROM.)

   /* create the table */
   Create Table Record_IDs(
         Table_Name  varchar(30),
         Current_ID  int)
   Go
   
   /* add a primary clustered index */
   Create      Unique Clustered Index PK_Record_IDs
         on Record_IDs( Table_Name ) with FILLFACTOR = 1
   Go

2. Insert records into the table that correspond to tables in the target database:

   Insert Record_IDs
         Select   Name,     1
         From   Sysobjects
         Where  Type = 'U'	/* user defined tables */

3. Create a stored procedure that will have a consistent access interface to the table and will lock the table so that no other users can modify the data while a given process is accessing it:

   Create Procedure up_GetID                 /* up = user procedure */
         @psTableName      varchar(30),            /* p = parameter */
         @rnNewID    int OUTPUT        /* r = receive or output parameter */
   As
   Declare
         @nSQLError	int,
         @nRowCount	int
   
   Begin Tran
         /* First update the record to acquire the exclusive lock on the page */
         Update Record_IDs
         Set    Current_ID = Current_ID + 1
         Where  Table_Name = @psTableName
   
         /* Check for errors */
         Select    @nSQLError = @@error,
               @nRowCount = @@rowcount
         If @nSQLError != 0 OR @nRowCount != 1
         Begin
               Rollback Tran
               Return –999 /* failed to update record correctly */
         End
   
         /* Select back the value from the table that we've already locked */
         Select      @rnNewID = Current_ID
         From   Record_IDs
         Where  Table_Name = @psTableName
   
         /* Check for errors */
         Select     @nSQLError = @@error,
               @nRowCount = @@rowcount
         If @nSQLError != 0 OR @nRowCount != 1
         Begin
               Rollback Tran
               Return –998 /* failed to select record correctly */
         End
   Commit Tran
   Return 0
   Go

4. Test the new procedure:

Declare
      @nRecordID  int,
      @nRC        int,
      @sMsg       varchar(255)

/* Fetch a record ID for use in inserting new record */
Exec @nRC = up_GetID 'table_A', @nRecordID OUTPUT

If @nRC != 0
      Print 'An error occurred fetching new Record ID'
Else
Begin
      Select @sMsg = 'New Record value is ' + Convert( varchar(4), @nRecordID )
      Print @sMsg
End
Go

Understanding Locks

Displaying Lock Information

Using SQL Enterprise Manager

1. Run SQL Enterprise Manager from the Microsoft SQL Server 6.5 group (see fig. 12.1).

   Fig. 12.1 - After just being started, SQL Enterprise Manager shows that no server is selected.

2. Select the server on which you want to work (see fig. 12.2).

   Fig. 12.2 - Clicking the plus next to the server expands its tree of devices, databases, and logins.

3. From the Server menu, select Current Activity and click the Object Locks page (see fig. 12.3).

   Fig. 12.3 - The Object Locks page of the Current Activity window shows the objects that are currently being locked.

To get more information about the individual statement that is causing locking (see fig. 12.4), you can either double-click the process that is in the Object Locks page or click the More Info toolbar button (see reference in fig. 12.3).

Fig. 12.4 - The Process Details dialog box shows additional information about the SQL statement that is causing locks.

Using sp_lock

Begin Tran
      Update authors
      set au_id = au_id
go
sp_lock
go
rollback tran
go

spid   locktype             table_id    page        dbname
------ -------------------- ----------- ----------- ---------------
10     Sh_intent            640005311   0           master
10     Ex_table             16003088    0           pubs
10     Sh_table             288004057   0           pubs
10     Ex_extent            0           320         tempdb

Killing a Locking Process

When reviewing the output from sp_who, look at the blk spid column to identify a user that is blocked. Trace the tree of the blocks back the parent spid, and kill that user. To kill a user process you can either use SQL Enterprise Manager, or execute the Kill command.

Using SQL Enterprise Manager

Having found a process that needs to be killed, you can press the Kill Process button on the toolbar of the Current Activity window (see reference in fig. 12.5).

Fig. 12.5 - The Kill Process toolbar button enables you to halt an activity.

A warning dialog box appears so you can change your mind and undo your action (see fig. 12.6).

Fig. 12.6 - The warning dialog box enables you to confirm whether or not you really want to kill a process.

Using KILL

KILL spid

Holding a Lock Explicitly

NOLOCK

NOLOCK is a very useful option for those people writing applications in which the data is statistically unaffected by a small sample of records having fluctuating values (i.e., you are more interested in trends of data than in the actual values themselves). Care should be taken, and it is important to clearly differentiate between data fetched with the NOLOCK keyword and data that is legitimately accurate according to the known condition of the database as a whole.

When selecting data with the NOLOCK keyword, it is possible that another user has affected your data in such a way as to make it invalid during the time you are reading from the data page in which it resides. For example, another user could have deleted a record that you are reading, and while you are reading it, their COMMIT is processed and the record is removed.

If you are reading data and it is no longer available, you will receive error messages 605, 606, 624, or 625. It is recommended that you process these errors in the same way that you process a deadlock condition. That is, inform the users that an error has occurred and ask them to retry their operations—advanced applications may want to auto retry the first time to avoid confusing the users unnecessarily.

HOLDLOCK

TABLOCK and TABLOCKX

Do not place a table lock (TABLOCK) on a table unless you have a good programmatic reason. TABLOCKs often create unnecessary overhead and undue locking in the server. Instead, rely on Lock Escalation (LE) thresholds to manage TABLOCKs for you.

Lock Escalation Options

Fortunately, SQL Server enables you to configure the way in which it chooses to escalate locks from page level to table level. These are options that are set at the server level with the server stored procedure, sp_configure.

LE Thresholds

The threshold maximum is used by the server to determine when to escalate a set of page locks to a table lock. The default for the server is 200 pages. To change this value, follow these steps. (The text for this statement can be found in 12_08.SQL on the CD-ROM.)

Use Master
Go
sp_configure 'LE threshold Maximum', NNN  /* where NNN is the new number 
                                             of pages */
Go
Reconfigure
Go

The threshold percentage is used to enable you to generically set a level at which you want to escalate a set of page locks to a single table lock relative to the number of rows in the table. The default value of this configuration option is zero (0), meaning that the LE threshold maximum should be used to determine escalation.

As we saw above, despite the LE thresholds it is possible to force locking on pages and tables by using the HOLDLOCK and TABLOCK keywords when issuing a SELECT statement to the server.

From Here...

Take a look at the following chapters to further develop your SQL Server and application programming knowledge:

• Chapter 5, "Creating Database Tables and Using Datatypes" tells you how you can redefine some of your tables to enable better concurrency.

• Chapter 10, "Managing and Using Indexes and Keys" shows you how you can optimize table access through the creation of a clustered index with a sparse FILL FACTOR.

• Chapter 16, "Understanding Server, Database, and Query Options" provides an understanding of how the options you set up for the server affect database applications and transaction locks.

Table of Contents

11 - Managing and Using Rules and Defaults

13 - Managing Stored Procedures and Using Flow Control Statements