A good understanding of transactions and locking is essential for anybody who is going to write database applications for more than one user. Even single-user applications require some understanding of locking, though the impact of locking yourself is not nearly as drastic as that of locking an enterprise network of hundreds of users.
SQL Server has a number of different styles of locking available to the programmer. This chapter will provide you with the information required to make an accurate assessment of what is needed for your application in terms of transaction control and locking.
You can never be too cautious in a multiuser application. As a programmer you should always concentrate on attempting to minimize the amount of locking that can occur so that there is less chance of users interfering with each other.
Single-statement transactions can be executed in ISQL just by entering their text and typing go. Single-statement transactions are ideal where the results required are simple and self-contained. For example, the following statement will return a list of tables from the database currently being used. (The text for this statement can be found in 12_01.SQL on the CD-ROM.)
To implement optimistic locking in your application without having it grind to a halt under excessive locks on the server, you must take care to observe some simple rules, as follows:Ensure that all application codes update and select from tables in the same order. This will stop any deadlocks from occurring.
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.
I think it will be useful as background to first discuss some of the basics of locking as they pertain to (and are implemented by) SQL Server, so that some of the more detailed items discussed in the sections below are not without a base of understanding. Specifically, I want to focus on the following two key areas of locking:
- Page Sizes and Granularity of Data
- Types of Locks
SQL Server's internal basic unit of work is a 2K data page. What this means is that any activity that is executed on the server must do work on at least 2K of data. To further explain, a table has a number of pages of data associated with it (depending on the number and size of rows that it contains); SQL Server can only reference data in that table a page at a time. So, if an update hits a single record in a table and a lock is held for some period of time, it is more than likely that more than one row is in fact being locked.
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 pagethereby 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.
SQL Server can place several types of locks on database pages and tables. The page locks that are possible are SHARED, EXCLUSIVE, and UPDATE. SHARED locks and EXCLUSIVE locks are reasonably self explanatory in that they either allow another process to acquire a lock on the same page or they don't.
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"
There are a number of ways in SQL Server that you can cause locks to be held or released while querying the database. One of those ways is by setting a transaction's isolation level. As its name implies, an isolation level specifies to the database how "isolated" to keep the data that is currently being worked on by the other users and requesters of data on the server. SQL Server has three different types of isolation levels and they are documented in the following three sections.
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 is the default method of operation for SQL Server. It does not allow you to have data returned from the database that is "dirty" or uncommitted. Read Committed acquires SHARE locks on all the pages it passes over inside a transaction. It is possible that, due to another user performing a delete or insert that is committed or rolled back during the life of your query, you may receive some data pages that are not re-readable or that may contain values that only temporarily exist in the database.
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
Read Uncommitted is the same as the NOLOCK keyword on an individual SELECT statement. No SHARED locks are placed on any data that you pass over in the query; additionally, no locks held by other users are observed. For example, if another user has deleted a whole table that you are about to select from, but has yet to COMMIT a transaction, you will still be able to read the data from it and not receive any error conditions.
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
Repeatable Read is the most exclusive type of locking that you can force SQL Server to maintain. Repeatable Read guarantees that the data you are reading will be unaffected by other transactions issued from other users during the life of a given transaction that you are working on. Because of Repeatable Read's explicit locking of data from other users, Repeatable Read reduces the concurrency of the database. It reduces the number of different users that can access data at the same time without affecting each other. Take care that you do not use Repeatable Read unwisely in your application; there are not that many places where it is actually required.
To set your isolation level to Repeatable Read perform the following SQL transaction:
In the opening section of this chapter, you saw how to delineate a transaction using BEGIN, COMMIT and ROLLBACK. SQL Server's keywords or Transact-SQL statements that are required for transaction control are described below for clear definition.
Set Transaction Isolation Level Repeatable Read
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.
When you issue a BEGIN TRAN to the database, SQL Server marks a point in the database's transaction logs identifying a point to be returned to in the event of a ROLLBACK TRAN. BEGIN TRAN explicitly tells SQL Server that all the work following, until a COMMIT or ROLLBACK is encountered, should be treated as one logical unitdespite the fact that it may contain many operations.
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.
Issuing a COMMIT TRAN to the database signals SQL Server that you are happy with the work done so far and no longer want to group any additional work inside the transaction. COMMIT TRAN is not reversible.
ROLLBACK TRAN is SQL Server's equivalent of the Edit, Undo menu option in your favorite word processor. Sending a ROLLBACK to the database server will cause it to undo all the work to the most recent BEGIN TRAN statement. Typically, a ROLLBACK TRAN would be issued during a long transaction if any particular part of it encountered a SQL error of some kind.
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 (Data Definition Language) and database modification statements are now allowed inside a transaction. The following statements can appear in transactions:
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
SQL Server does not allow a table column to be dropped, but there is a way to work around it. Create a new table with the required schema (minus the column) and use the SELECT INTO Transact-SQL command to copy the data.
One thing that becomes obvious during the writing of large stored procedures and applications with large bodies of SQL code is that no matter how it is looked at, the code is pretty unreadable. It is text based and there is a great reliance on programmers all working with the same style of format and layout. When transactional programming is involved, it becomes even more important for people to use good indenting to clearly mark blocks of code.
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 provide a convenient way of attaching an identifier to a whole body of work. Use named transactions to make it easier to undo large portions of code. To create a named transaction, add the name of the transaction to the BEGIN TRAN statement, as follows. (The text for this statement can be found in 12_05.SQL on the CD-ROM.)
/* Open outer transaction */
Begin Tran UPDATE_AUTHORS
Set CONTRACT = 1
Where AU_ID = '341-22-1782'
/* Open inner transaction */
Begin Tran UPDATE_TITLEAUTHOR
Set ROYALTYPER = ROYALTYPER + 25
Where AU_ID = '341-22-1782'
If @@error != 0
Rollback Tran UPDATE_TITLEAUTHOR
Print 'Failed to update Royalties'
Commit Tran UPDATE_TITLEAUTHOR
Commit Tran UPDATE_AUTHORS
If you omit the transaction's identifier or name when committing or rolling back a transaction, SQL Server will simply undo the work to the most recent BEGIN TRAN regardless of its name. Take care when using named transactions that all work is coded in a consistent mannereither using names or not. Otherwise, programmers may end up stepping on each others transactions inadvertently.
SavePoints are really just another way of doing a named transaction. They provide a method of marking a place in the code to which a ROLLBACK may be used to undo work. To create a SavePoint, issue the SQL command
SAVE TRANSACTION <TRAN_NAME>
Then just use the identifier, <TRAN_NAME>, when performing your ROLLBACK. For example:
The text for this statement can be found in 12_06.SQL on the CD-ROM.
Set CONTRACT = 1
Where AU_ID = '341-22-1782'
/* save our work to this point */
Save Transaction AuthorDone
Set ROYALTYPER = ROYALTYPER + 25
Where AU_ID = '341-22-1782'
If @@error != 0 Or @@RowCount > 1
/* rollback and exit */
Rollback Tran AcctDataDone
Print 'Error occurred when updating TitleAuthor'
Print 'Transaction Committed'
Despite the fact that the transaction above rolled back the UPDATE on TITLEAUTHOR, SQL Server will hold locks on the TITLEAUTHOR table until the entire transaction is completed by either COMMIT or ROLLBACK. This is a side-affect of using a SavePoint and is something that may cause unexpected locking in an application.
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.
SQL Server 6.0 introduced a new serial datatype, called the IDENTITY, in which SQL Server will automatically assign the next sequential value to a column in a table. IDENTITYs are very valuable in applications that have high transaction volume and want to identify each record uniquely.
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:
- 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(
/* add a primary clustered index */
Create Unique Clustered Index PK_Record_IDs
on Record_IDs( Table_Name ) with FILLFACTOR = 1
- Insert records into the table that correspond to tables in the target database:
Select Name, 1
Where Type = 'U' /* user defined tables */
- 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 */
/* First update the record to acquire the exclusive lock on the page */
Set Current_ID = Current_ID + 1
Where Table_Name = @psTableName
/* Check for errors */
Select @nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1
Return 999 /* failed to update record correctly */
/* Select back the value from the table that we've already locked */
Select @rnNewID = Current_ID
Where Table_Name = @psTableName
/* Check for errors */
Select @nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1
Return 998 /* failed to select record correctly */
- Test the new procedure:
/* 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'
Select @sMsg = 'New Record value is ' + Convert( varchar(4), @nRecordID )
Always use the new IDENTITY column to create identifying columns instead of the TIMESTAMP datatype. The IDENTITY column is far easier to reference and use in application code and can impose less data overhead if you use a small datatype for it (such as TINYINT or SMALLINT).
In addition to the background information provided previously in the section entitled Types of Locks it is important to know how to handle locking when it occurs in your database.
There are two ways to review information about locks held in the database: using the SQL Enterprise Manager or through the execution of the system stored procedure, sp_lock. SQL Enterprise Manager, under the covers, is calling sp_lock to get the information to display.
To view information that is being locked using the SQL Enterprise Manager, perform the following steps:
- 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.
- 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.
- 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.
The sp_lock system stored procedure will return a list of processes and the types of locks that they are holding on the system. To get the locks held by a particular process, add the process ID to the command (sp_lock spid). Here is some example code to show you the output of sp_lock:
set au_id = au_id
The output of the sp_lock follows:
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
Many system procedures return an OBJECT_ID column to identify a database object. To quickly get the name of that object, use the system function OBJECT_NAME( ). For example, select OBJECT_NAME(1232324).
Before killing a process that is holding locks on the database, verify with the sp_who and sp_lock system procedures that the spid (server process id) that you are targeting to kill is in fact the user holding the locks.
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 to kill a process involves first finding the process that is causing locking, and the steps to do this are outlined previously in the section entitled "Using sp_lock."
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.
Having identified the user process (spid) that you want to kill, execute the following SQL to kill it:
This will kill most processes that are existing on the server. Under some circumstances, it is possible to have processes that can't be killed. Usually this occurs when it is in an Extent or Resource lock awaiting the underlying operating system to complete a task. Monitor the process with sp_who until it leaves this condition and then execute the KILL command.
If you have application code that really needs to explicitly hold locks on particular sets of data, SQL Server provides you with extensions to the basic SELECT statement that perform this functionality. SQL Server enables you to add optimizer hints or keywords to your SELECT statements that tell it how to process the data that matches your results. There are several kinds of hints that you can place on a set of data affected by a SELECT statement: NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, PAGLOCK, TABLOCKX. Some of these options are discussed below.
NOLOCK is an option that enables the query to read from dirty data. Dirty data is data that may or may not have been affected by other user's updates and deletes. Selecting records from a table with the NOLOCK keyword ignores any other user's EXCLUSIVE locks (indicating that they had updated a record) and does not place any locks on the data itself.
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 operationsadvanced applications may want to auto retry the first time to avoid confusing the users unnecessarily.
Normal SELECTs on tables acquire a SHARED lock on a page while the SELECT is passing through the data. A SHARED lock does not prohibit another user from updating a record or attempting to gain an EXCLUSIVE lock on the data page that is currently being processed by the SELECT. In addition, the SHARED lock expires on a data page as the next page is being read. If you want to maintain data integrity for the life of the SELECT (because you may need to scroll backwards and forwards through the result set), use the HOLDLOCK command to force SQL Server to hold the SHARED lock until the transaction is complete.
As its name implies, TABLOCK forces a SELECT statement to lock the entire table or tables affected by the SELECT for the duration of the statement. TABLOCKX forces an exclusive table lock for the life of the transaction, denying any other user access to the table until the transaction has been completed.
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.
SQL Server locks data on the page level. Any query that you execute on the server will hold locks on at least one full page. If you start updating or locking multiple pages on a table SQL Server starts consuming resources to manage your requests. At a certain point (based on a percentage of pages locked per table) it becomes more efficient for the database to lock the entire table (a table lock) than to keep managing the individual pages being locked by a given transaction.
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.
Using sp_configure, it is possible to set three different types of Lock Escalation (LE) thresholds: LE threshold maximum, LE threshold minimum, and LE threshold percent.
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.)
sp_configure 'LE threshold Maximum', NNN /* where NNN is the new number
of pages */
The threshold minimum is used in conjunction with the threshold percent to stop a table lock escalation occurring on a table with few rows. Suppose you set the LE threshold percent to 50 percent, meaning that if more than half the data pages where being locked you wanted the whole table locked. This is not an unreasonable proposition until you have a small table with only a few pages. The threshold minimum that defaults to 20 pages stops the threshold percentage from escalating page locks to table locks unless its minimum number of pages has been locked.
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.
In this chapter you learned about the fundamentals of locking and transactions and how they will affect your application. In addition, you learned about the internals of SQL Server and how it manages many users hitting the same table.
Take a look at the following chapters to further develop your SQL Server and application programming knowledge:
QUE Home PageFor technical support For our books And software contact firstname.lastname@example.orgCopyright © 1996, Que Corporation
Table of Contents
11 - Managing and Using Rules and Defaults
13 - Managing Stored Procedures and Using Flow Control Statements