A good understanding of transactions and locking is essential if you want to write database applications for more than one user. Even single-user applications require some understanding of locking, although the impact of locking yourself is not nearly as drastic as that of locking an enterprise network of hundreds of users.
SQL Server offers the programmer several different styles of locking. This chapter provides you with the information that you need to accurately assess what your application requires in terms of transaction control and locking.
As a programmer, you should always concentrate on minimizing the amount of locking that occurs so that you decrease the chances of users interfering with each other.
Table 12.1 outlines the basics of a transaction, whether imposed implicitly by SQL Server or explicitly by your application.
Atomicity | A transaction is assumed to be complete or not. Although this requirement seems obvious, it's important to understand that either all or none of the transaction becomes final. For example, if you're posting a transfer of funds from one account to another, the funds are deposited into the target account and they are removed from the original account, as in a commit, or nothing happens at all, as in an abort. |
Consistency | A transaction should "leave things as it found them." In other words, when a transaction starts, the system is in a known state. When a transaction commits, the system must once again be in a known, consistent state. You cannot leave anything hanging from a transaction; it must be a full and complete operation. Of course, by definition, an aborted transaction also fulfills these requirements because it reverts to the state of the system prior to opening the transaction. |
Isolation | Transactions must stand alone and have no effect or dependence on other transactions. Dependence on another transaction causes deadlocks, resulting in rollback operations. This attribute is also known as serializability. |
Durability | Once completed, the transaction's objective has been met and there is no further reason for the operation to be undone. In other words, after a transaction is completed, it stays completed even if something happens to the system. This typically is the reason for wrapping an important operation in one large transaction, ensuring that all or nothing applies to the database tables. |
A transaction does not enforce these rules in and of itself. Instead, you, as the developer, must keep these requirements in mind. You should strive to meet each of these objectives for each transaction that you create, without exception. If an operation doesn't measure up to the ACID test, don't continue coding until the operation meets the requirements. You won't be sorry later.
A transaction is a logical unit of work that you want the SQL Server to perform for you. That unit of work can include one or many SQL statements, if you appropriately define the unit of work to the server by delineating which statements within a batch are part of a transaction.
In ISQL, you can execute single-statement transactions by entering their text and typing go. Single-statement transactions are ideal if the required results are simple and self-contained. For example, the following statement returns a list of tables from the database currently being used:
Select * From SYSOBJECTS Where TYPE = `U' /* user-defined tables */ Order By NAME
You can find the text for this statement in 12_01.SQL on the companion CD-ROM.
In some instances, however, you must do more than one thing in a transaction and conditionally undo it if something goes wrong. In such cases, multistatement transactions come into play. Such transactions enable you to combine two or more SQL statements and send them to the server for processing. You can also define an event that undoes any work submitted. Listing 12.1 shows an example of a multistatement transaction. You can find the text for this statement in 12_02.SQL on the companion CD-ROM.
Create Table TABLE_A( X smallint null, Y smallint null) Go Create Table TABLE_B( Z smallint null) Go Begin Tran Update TABLE_A Set X = X + 1 Where Y = 100 Update TABLE_B Set Z = Z + 1 If @@rowcount = 0 or @@error !=0 /* no rows were hit by our update */ Begin Rollback Tran Print `Error occurred, no rows were updated' Return End Commit Tran
TIP: To make your scripts and stored procedures easier to read, format them with indented sections within transaction blocks.
You can also set up SQL Server to start transactions automatically when certain operations are performed. You shouldn't use this technique in a production environment, because in such environments it is better to have your application declare explicit transactions. However, the IMPLICIT_TRANSACTIONS option can be helpful in a development or testing environment. You set this option with the SET statement as follows:
set IMPLICIT_TRANSACTIONS ON | OFF
CAUTION: If you set this option, you still must issue corresponding COMMIT statements. Otherwise, the transactions remain open and can bog down, if not lock up, your system. You should not use this option in a production environment. Instead, set your transactions explicitly in your routines.
After you set up SQL Server, the following operations automatically begin transactions:
Within a transaction, you cannot perform certain actions. These are general actions that you cannot undo, at least not without significant repercussions to major components of the system. Keep in mind that transactions are meant to protect groups of processing statements, not so much to back up the management of your system. If you want to protect yourself from a potential system-damaging change, consider dumping the database and transaction log prior to the operation that you want to perform.
The following actions are not allowed within a transaction:
NOTE: A new type of transaction enables you to distribute transactions. The Distributed Transaction Coordinator controls such transactions. This type of transaction is quite useful for transactions that occur at remote locations but affect centralized inventory levels.For more information on the Distributed Transaction Coordinator, see Chapter 18, "Using the Distributed Transaction Coordinator."
When you write multiuser database applications, you can take one of two approaches to transaction control: optimistic or pessimistic locking. Optimistic locking assumes that your application code does nothing to explicitly enforce locks on records while you work on them. Instead, you rely on the database to manage this enforcement on its own while you concentrate on application logic. Pessimistic locking assumes that the application code attempts to enforce some type of locking mechanism.
To implement optimistic locking in your application without having it grind to a halt under excessive locks on the server, you must carefully observe some simple rules:
If you assume that SQL Server will manage locking and that you have nothing to worry about, you are taking a very optimistic locking approach. Unfortunately, such an approach isn't very pragmatic because it assumes that a programmer or user can do nothing to explicitly cause locking. In fact, many situations can cause a large amount of locking to occur on a server, potentially disabling it for the enterprise that it supports.
This section provides some background on the basics of locking as they pertain to, and are implemented by, SQL Server. This background should give you a basic understanding of some of the more detailed items discussed in the sections that follow. Specifically, this section focuses on the following two key aspects of locking:
Page Sizes and Granularity of Data SQL Server's internal basic unit of work is a 2K data page. Therefore, any activity that the server executes must do work on at least 2K of data. Further, a table has several pages of data associated with it; the number of pages depends on the number and size of rows that the table contains. SQL Server can reference data in a table only one page at a time. If an update hits a single record in a table and a lock is held for some period of time, probably 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 you must provide a way for multiple users to work independently of one another. For example, two users must be able to update customer records simultaneously 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 many 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. To do so, force as few rows of data as possible onto the same data page, thereby limiting the number of coincidental rows locked as the result of a user action. Additionally, you should keep users' transactions to a minimum duration when hitting these tables.
Two other types of locks can occur that lock data at a higher level than a single data page: table and extent.
Table locks occur when a user issues a query to update a table without including a WHERE clause, which implies that the user wants to update every row. In addition, table locks can occur 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 must create a new database extent--eight pages of data--to respond to a user query. Unfortunately, MS SQL offers no controls for handling extent locks, so you simply should know that they occur and what they mean.
Types of Locks SQL Server can place several types of locks on database pages and tables. The possible page locks are SHARED, EXCLUSIVE, and UPDATE. SHARED and EXCLUSIVE locks are reasonably self-explanatory; SHARED locks allow another process to acquire a lock on the same page, and EXCLUSIVE locks don't. Multiple processes can have SHARED locks on the same data page. These locks usually are acquired when data is being read. It is important, however, that no other process can take an EXCLUSIVE lock to perform statements within the Data Manipulation Language (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 deleting a record from a page. These locks disallow any other process from accessing the page.
The UPDATE lock type is somewhere between a SHARED and EXCLUSIVE lock. It allows a process to acquire a SHARED lock on the page until an 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 locks 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 the same way as they do at the page level. SQL Server also has INTENT locks. INTENT locks indicate that a table has several pages on it that SQL Server intends to lock at the page level in response to a user process. Such might be the case as SQL Server reads a table, but your specific query is ahead of the query that is locking pages in the table.
SQL Server 6.5 adds insert row-level locking. This new lock allows multiple users to insert records into the same page. This lock was added because of a large amount of contention with inserts at the end of tables. The reason for the contention is that when you insert new rows into a table--unless the table has a clustered index--all new rows go to the table's last page. If multiple users are inserting to the same page, the time to insert the row, commit the implicit transaction, and move on takes a performance hit in heavy processing environments.
See Chapter 24, "Creating and Using Cursors," for additional information on cursors.
SQL Server provides several ways to 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 "protected" to keep the data that other users and requesters of data on the server are currently working with.
SQL Server has three different types of isolation levels: Read Committed, Read Uncommitted, and Repeatable Read. The following three sections document each.
NOTE: 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 "Holding a Lock Explicitly," for more information.
The Read Committed Level Read Committed is the default method of operation for SQL Server. It does not allow you to retrieve "dirty" or uncommitted data from the database. Read Committed acquires SHARE locks on all the pages it passes over inside a transaction. If another user performing a deletion or insertion that is committed or rolled back during the life of your query, you might receive some data pages that are not rereadable or that might contain values that exist in the database only temporarily. If you need to ensure that other users cannot affect the query's results during the life of a particular transaction, make sure that you use the Repeatable Read isolation level.
To set your isolation level to Read Committed, perform the following SQL statement:
Set Transaction Isolation Level Read Committed Go
For more information, refer to Table 12.2 and the sections following it. These explain the other types of locks and how they relate to each other.
The Read Uncommitted Level 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 from which you are about to select, but that user has yet to commit a transaction, you still can read the data from it and not receive any error conditions.
CAUTION: You shouldn't use the Read Uncommitted transaction isolation level for any applications that require data integrity, because you have no guarantee that the data you are working with is the same 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
The Repeatable Read (Serializable) Level 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 remains 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 database's concurrency, and reduces the number of different users who can access data at the same time without affecting each other. Take care that you do not use Repeatable Read unwisely in your application. Not many places actually require it. To set your isolation level to Repeatable Read, perform the following SQL transaction:
Set Transaction Isolation Level Repeatable Read Go
In the "Defining Transactions" section earlier in this chapter, you saw how to delineate a transaction using BEGIN, COMMIT, and ROLLBACK. This section describes SQL Server's keywords or Transact-SQL statements required for transaction control.
CAUTION: At some point in your code, you must follow each BEGIN TRAN with a matching COMMIT TRAN or ROLLBACK TRAN. Transactions must begin and end in pairs; otherwise, the server continues holding locks until the client is disconnected.
The BEGIN TRAN Keyword When you issue a BEGIN TRAN to the database, SQL Server marks in the database's transaction logs the point to return to in the event of a ROLLBACK TRAN. BEGIN TRAN explicitly tells SQL Server to treat all the following work, until a COMMIT or ROLLBACK is encountered, as one logical unit. The work can contain many operations. You can issue operations that affect a database without a BEGIN TRAN statement. You cannot, however, conditionally undo work that you send to the server that you do not precede with a BEGIN TRAN so that SQL Server knows the state to which it must return the database.
NOTE: SQL Server's transaction logs monitor transactions contained within BEGIN and COMMIT statements. If a media failure occurs before data physically changes on the database, SQL Server recovers or ensures that it applies those changes by "rolling forward" those unapplied transactions to the database the next time the server is brought back online. n
The COMMIT TRAN Keyword Issuing a COMMIT TRAN to the database signals SQL Server that the work succeeded and that you no longer want to group any additional work inside the transaction. By definition, a COMMIT TRAN also implies that you've fulfilled the requirements of the ACID test presented in Table 12.1.
The ROLLBACK TRAN Keyword ROLLBACK TRAN is SQL Server's equivalent of your favorite word processor's Edit, Undo menu option. Sending a ROLLBACK to the database server causes it to undo all the work to the most recent BEGIN TRAN statement. Typically, a ROLLBACK TRAN is issued during a long transaction if any particular part of it encounters any SQL error.
CAUTION: SQL Server enables you to call remote stored procedures within a transaction. Because of the nature of the Remote Procedure Call (RPC) interface with the other server on which the RPC executed, however, SQL Server cannot roll back any such calls. When writing applications that require RPCs, make sure that you include additional RPCs to undo your previous work programmatically.
A new feature to SQL Server 6.5, DDL (Data Definition Language), and database modification statements are now allowed inside a transaction. The following statements can appear in transactions:
ALTER TABLE | CREATE TRIGGER | DROP TABLE |
CREATE DEFAULT | CREATE VIEW | DROP TRIGGER |
CREATE INDEX | DROP DEFAULT | DROP VIEW |
CREATE PROCEDURE | DROP INDEX | GRANT & REVOKE |
CREATE RULE | DROP PROCEDURE | SELECT INTO |
CREATE TABLE | DROP RULE | TRUNCATE TABLE |
These statements are important because they allow SQL Server 6.5's new database schema management features to function. Many of these statements modify the table structures on the system or create new ways to access the information.
TIP: SQL Server does not provide a direct way to drop a table column, but you can work around this omission. Create a new table with the required schema, minus the column, and use the SELECT INTO Transact-SQL command to copy the data.
When you examine large, stored procedures and applications with large bodies of SQL code, the code is inevitably pretty unreadable. Such code is text-based and relies heavily on the premise that all programmers work with the same style of format and layout. For this reason, when transactional programming is involved, it becomes even more important to use good indenting to mark blocks of code clearly.
However, even the most careful programmer will find that it becomes a bit of a nightmare to remember how many indents to roll back 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 to roll back work to a given named or saved portion of the code that has been executing, even if the portion of code is at a higher nesting level.
Using Named Transactions Named transactions provide a convenient way to attach 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 shown in Listing 12.2. You can find the text for this statement in 12_05.SQL on the companion CD-ROM.
/* 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
NOTE: If you omit the transaction's identifier or name when committing or rolling back a transaction, SQL Server simply undoes the work to the most recent BEGIN TRAN, regardless of the transaction's name. When using named transactions, be careful to code all your work consistently, either using names or not, throughout your work. If you do not use a consistent style, programmers might step on each other's transactions inadvertently.
Using SavePoints SavePoints are simply another way to perform a named transaction. They provide a method of marking a place in the code at which you can use a ROLLBACK to undo work. To create a SavePoint, issue the following SQL command:
SAVE TRANSACTION <TRAN_NAME>
You then use only the identifier, <TRAN_NAME>, when performing your ROLLBACK, as shown in Listing 12.3. You can find the text for this statement in 12_06.SQL on the companion 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'
CAUTION: Although the transaction in Listing 12.3 rolls back the UPDATE on TITLEAUTHOR, SQL Server holds locks on the TITLEAUTHOR table until either COMMIT or ROLLBACK completes the entire transaction. This side effect of using a SavePoint can cause an application to lock unexpectedly.
TIP: If your application seems to hold locks continuously after the first transaction executes, you probably have issued more BEGIN TRANs than you have corresponding COMMIT TRANs or ROLLBACK TRANs.
Remember that you must enclose transactions in pairs of BEGIN and COMMIT or ROLLBACK. If you fail to do so, you tell SQL Server that you want to keep the transaction open longer.To help identify your code problems, walk through your application and monitor error conditions carefully. Probably 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, which indicates how deeply nested in transactions you are.
SQL Server 6.0 introduced a serial datatype called the IDENTITY. In this datatype, SQL Server automatically assigns the next sequential value to a column in a table. IDENTITYs are valuable in applications that have a high transaction volume and must 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, you can implement the same kind of feature as an IDENTITY column by performing the following steps:
/* 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
Insert Record_IDs Select Name, 1 From Sysobjects Where Type = `U' /* user-defined tables */
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 the 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
Declare @nRecordID int, @nRC int, @sMsg varchar(255) /* Fetch a record ID for use in inserting the 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
TIP: To create identifying columns, always use the new IDENTITY column rather than the TIMESTAMP datatype. The IDENTITY column is much 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 understanding the background information provided earlier in this chapter, you need 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: by using the SQL Enterprise Manager or by executing the system stored procedure sp_lock. (Actually, SQL Enterprise Manager calls sp_lock to display the information.)
Using SQL Enterprise Manager To use the SQL Enterprise Manager to view locked information, perform the following steps:
FIG. 12.1
When initially started, SQL Enterprise Manager shows that no server is selected.
FIG. 12.2
By clicking the plus sign next to the server, you expand its tree of devices, databases,
and logins.
FIG. 12.3
The Object Locks page of the Current Activity dialog box displays the currently locked
objects.
To get more information about the individual statement that is causing locking, you can either double-click the process that is in the Object Locks page or click the View Details toolbar button shown in Figure 12.3. You then see the Process Details dialog box shown in Figure 12.4.
FIG. 12.4
The Process Details dialog box shows additional information about the SQL statement
that is causing locks.
Using sp_lock The sp_lock system stored procedure returns a list of processes and the types of locks that they are holding on the system. To get the locks that a particular process is holding, add the process ID to the command, as follows:
sp_lock spid
Note the following example code:
Begin Tran Update authors set au_id = au_id go sp_lock go rollback tran go
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
TIP: Many system procedures return an OBJECT_ID column to identify a database object. To get the object's name quickly, 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) you are targeting to kill in fact belongs to the user holding the locks.
When reviewing the output from sp_who, examine the blk spid column to identify a blocked user. Trace the tree of the blocks back to the parent spid and kill that user process. To kill a user process, you can either use SQL Enterprise Manager or execute the Kill command.
You can also use DBCC to check the status of transactions open against a given database. Using the OPENTRAN statement for DBCC, you can determine which transactions are open and when they were started. The syntax for the command is as follows:
dbcc opentran [(database | databaseID)] [WITH TABLERESULTS]
If you don't specify the database name or ID, the command runs against the current database, listing open transactions. If you specify the WITH TABLERESULTS option, you receive a listing of the same results, but with output formatted a bit more to enable you to save it for use in a workbook or in another table. This listing can be a big help if you're troubleshooting a problem and want to look for trends that are occurring for the transactions.
Using SQL Enterprise Manager When you use SQL Enterprise Manager to kill a process, you first must find the process that is causing locking. How you kill a process was outlined previously in the section "Using sp_lock." After you find a process that you need to kill, you can click the toolbar's Kill Process button in the Current Activity dialog box (see Figure 12.5).
FIG. 12.5
The toolbar's Kill Process button enables you to halt an activity.
A warning dialog box appears so that you can change your mind and undo your action (see Figure 12.6).
FIG. 12.6
The warning dialog box enables you to confirm that you want to kill a process.
Using KILL After identifying the user process (spid) that you want to kill, execute the following SQL statement to kill it:
KILL spid
This statement kills most processes that exist on the server. In certain circumstances, some processes can't be killed. Usually such a process is in an Extent or Resource lock waiting for 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 must explicitly hold locks on particular sets of data, SQL Server provides extensions to the basic SELECT statement that have 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. You can place several kinds of hints on a set of data that a SELECT statement affects: NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, PAGLOCK, and TABLOCKX.
Table 12.2 explains the two most obscure options, and the next series of sections explains the others.
Option | Description |
UPDLOCK | Locks the values in the table for a future update, but enables other users to read the information in the table. This condition can help prevent delays that users might typically experience when simply browsing or reviewing the data in tables while updates are occurring. If you use this option, users still can review the database contents, but they can't update the locked rows. |
PAGLOCK | Explicitly tells SQL Server that you want to use shared page locks. The only time that you might use this option is when issuing a TABLOCKX lock on a table, preventing all other reads. See the coming sections for addi-tional information about TABLOCKX locks. By default, this option is on. |
The NOLOCK Option The NOLOCK option enables a query to read from dirty data. Dirty data is data that other users' updates and deletions might have affected. If you select records from a table with the NOLOCK keyword, any other user's EXCLUSIVE locks are ignored. Such a lock indicates that a user updated a record, but does not lock the data itself. NOLOCK is a useful option when you are writing applications that use a small sample of records with fluctuating values that do not affect the data statistically--that is, for applications in which trends of data are more important than the actual values themselves. Be careful to differentiate clearly between data fetched with the NOLOCK keyword and data that is legitimately accurate according to the known condition of the database as a whole.
CAUTION: When selecting data with the NOLOCK keyword, keep in mind that during the time that you are reading from the data page in which the data resides, another user's actions might affect your data in such a way that makes it invalid. For example, another user might delete a record that you are reading, and while you are reading it, the user's COMMIT is processed and the record is removed.If you are reading data that is no longer available, you receive error messages 605, 606, 624, or 625. You should process these errors the same way that you process a deadlock condition: by informing the users that an error has occurred and asking them to retry their operations. Advanced applications might automatically retry initially to avoid confusing users unnecessarily.
The HOLDLOCK Command A normal SELECT on tables acquires 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 the SELECT currently is processing. 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 might need to scroll backward and forward through the result set, use the HOLDLOCK command to force SQL Server to hold the SHARED lock until the transaction is complete.
The TABLOCK and TABLOCKX Commands 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.
TIP: 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 at the page level. Any query that you execute on the server holds 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, the database can lock the entire table more efficiently than it can keep managing the individual pages that a given transaction is locking.
Fortunately, SQL Server enables you to configure the way it escalates locks from page level to table level. You set these options at the server level using the server stored procedure sp_configure.
LE Thresholds Using sp_configure, you can set three different types of lock escalation (LE) thresholds: LE threshold maximum, LE threshold minimum, and LE threshold percent. The server uses the threshold maximum 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, use the statement shown in Listing 12.4. You can find this statement's text in 12_08.SQL on the companion CD-ROM.
Use Master Go sp_configure `LE threshold Maximum', NNN /* where NNN is the new number of pages */ Go Reconfigure Go
You use the threshold minimum with the threshold percent to stop a table lock escalation from occurring on a table with few rows. Suppose that you set the LE threshold percent at 50 percent. This setting tells SQL Server that if more than half the data pages are being locked, you want to lock the whole table. This setting is reasonable unless 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.
Using the threshold percentage, you can set a generic 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. This configuration option's default value is zero, which tells SQL Server to use the LE threshold maximum to determine escalation.
TIP: Despite the LE thresholds, you can force locking on pages and tables by using the HOLDLOCK and TABLOCK keywords when issuing a SELECT statement to the server.
After you start working with transactions, the transaction log can quickly become your mortal enemy. Over time, you issue many transactions and leave many open. You might recall that when you truncate a transaction log, the log dumps its contents but only to include all committed transactions. If you have many uncommitted transactions, you won't free up the log much at all.
You can solve this problem by using the DBCC OPENTRAN approach mentioned earlier. You still have to troubleshoot your code to determine what went wrong, but at least you can start to free up the processes that have filled the transaction log.
In one especially interesting case, a developer opened a transaction, performed operations, and verified that the code performed correctly. When the transaction never showed up in the database table itself, the developer decided a server restart was in order. When SQL Server came back up, had finished recovering the database, and allowed the developer back in to the system, the data was unchanged, even though the routine had apparently executed correctly.
In this case, of course, the developer left the transaction open. When the server restarted, it rolled back the operations performed within the transaction, restoring the database to the state that it was before the developer ran the routine. The stored procedure's code didn't have any syntax errors, only a missing COMMIT statement at the termination of the processing.
When in doubt, check for open, pending processes. Also, be sure to review open or pending locks. You can save a lot of trouble by installing a small bit of error control and referencing the @@trancount intrinsic SQL Server variable to confirm that, as you leave a stored procedure, the relevant transactions have been completed and committed.
In this chapter, you learned about the fundamentals of locking and transactions and how they affect your application. In addition, you learned about the internals of SQL Server and how it manages many users hitting the same table.
To develop your SQL Server and application programming knowledge further, see the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.