by Orryn Sledge
How many times have you seen an application that works with a single user but when multiple users access the application, all sorts of performance and data problems occur? When these types of problems arise, it usually is up to the DBA to fix them. You have probably heard that SQL Server is a high-performance database capable of handling 100 or more users. That is a true statement. But to extract maximum performance and data consistency in a multi-user environment, you must understand how SQL Server manages transactions. Otherwise, performance and data consistency will suffer. To help you avoid these problems, the topics discussed in this chapter explain how to design multi-user databases that maximize transaction throughput while maintaining data consistency.
SQL Server uses locks to maintain data consistency in a multi-user environment. Locking behavior is automatically handled by SQL Server; SQL version 6.x enables the DBA to control additional aspects of locking.
To help understand why locks are important, look at the banking example shown in Figure 23.1. Suppose that you decide to transfer $100 from checking to savings. Your bank decides to run a report that shows your combined balance for checking and savings. What happens if the report is run while the transfer is in progress? Would the report show a balance of $200 or $100?
Figure 23.1.
How locks maintain data consistency.
The answer resides in how SQL Server uses locks to maintain data consistency. When
transaction 1 is initiated, SQL Server places a lock on the checking account information
and on the savings account information. These locks force other users to wait until
the locks are released before they can access the data. This prevents users from
reading incomplete or pending changes. Therefore, when the transaction is complete,
the report is allowed to access the account data, thus reporting the correct balance
of $200.
NOTE: When pending changes can be read by a transaction, it is known as a dirty read. By default, SQL Server prevents dirty reads.
Without locks, the report might have showed a balance of $100, which is incorrect. For example, if the report read the data after the $100 was subtracted from check-ing but before it was added to savings, the report would show a combined balance of $100.
If locks are automatically handled by SQL Server you may wonder, "Why are we having this discussion?" The answer is blocking and deadlocks. Whenever multiple users try to access or modify the same data, the potential for blocking and deadlocks increases.
By understanding SQL Server's locking behavior, you can decrease the likelihood of blocking and deadlocks.
Following are some of the variables that can impact the frequency of blocking and deadlocks:
Blocking Blocking occurs when a process must wait for another process to complete. The process must wait because the resources it needs are exclusively used by another process. A blocked process resumes operation after the resources are released by the other process.
For this example, assume that the bank decides to eliminate the monthly service charge for all existing customers (see Figure 23.2). Therefore, the DBA sets the service_charge to $0.00 for all accounts. Not being a good DBA, he runs this transaction during prime hours. This forces transactions 2, 3, and 4 to wait until transaction 1 is complete. The waiting transactions are considered to be blocked by transaction 1.
Figure 23.2.
A blocking example.
When blocking occurs, it looks like your machine is hung. What has happened is that
SQL Server has put your process in a holding queue. The process remains in the queue
until it can acquire the resources it needs to complete its tasks.
Blocking Problems
I once was thrown into a project that involved converting a mainframe application to SQL Server. Management was eager to convert the application quickly. They did not want any time spent on table design or index strategy. I tried to explain to them that their existing database design could lead to blocking. The day the application went into production was a prime example of how blocking can impact a system. Whenever certain components of the application were run, the transaction processing component of the application would halt because of blocking. The reason the blocking was so severe was because of poor table design and index strategy. The main table used by the application was not properly normalized, thus making it very wide. Very wide tables can substantially slow processing throughput. Additionally, the table lacked any useful indexes. The combination of these factors is a sure-fire way to generate massive blocking. After management realized what had happened, they were willing to allocate the resources to go back and redesign the table schema and reevaluate the index strategy. After the redesign, the blocking problem went away and the application could be used without impacting the transaction processing aspect of the application.
Deadlock Deadlock occurs when two users have locks on separate objects and each user is trying to lock the other user's objects. SQL Server automatically detects and breaks the deadlock. It terminates the process that has used the least amount of CPU time. This enables the other user's transaction to continue processing. The terminated transaction is automatically rolled back and an error code 1205 is issued.
Figure 23.3 shows an example of a deadlock. Assume that transaction 1 and transaction 2 begin at the exact same time. By default, SQL Server automatically places exclusive locks on data that is being updated. This causes transaction 1 to wait for transaction 2 to complete--but transaction 2 has to wait for transaction 1 to complete. This is classic deadlock. To resolve the deadlock, SQL Server automatically terminates one of the transactions.
Figure 23.3.
An example of deadlock.
NOTE: SQL Server does not use locks for single-user or read-only databases.
The following utilities are provided by SQL Server to view locks:
Current Activity dialog box
The Current Activity dialog box in the Enterprise Manager provides graphical lock
and blocking information (see Fig- ure 23.4).
Figure 23.4.
Viewing locks from the Current Activity dialog box in the Enterprise Manager.
sp_lock
The system procedure sp_lock provides text-based lock information (see Figure
23.5).
Figure 23.5.
Sample output from sp_lock.
Performance Monitor
The Performance Monitor allows you to graphically track lock information (see Figure
23.6).
Figure 23.6.
Tracking locks with the Performance Monitor.
sp_who
The system procedure sp_who provides information about blocked processes
(see Figure 23.7).
Figure 23.7.
Sample output from sp_who.
Trace flags The trace flags shown in Table 23.1 provide extended insight into locking behavior.
Trace Flag | Output |
1200 | Displays process ID and types of locks being requested |
1204 | Displays locks in use with a deadlock and the command involved in the deadlock |
1205 | Displays information about the commands used during a deadlock |
NOTE: The trace flag 1200 can be useful for tracking locking behavior. The easiest way to use the trace flag is to use the DBCC traceon() function. You also must turn on trace flag 3604 to echo trace information to the client workstation, as in the following example:
DBCC traceon(3604)
DBCC traceon(1200)
UPDATE t_1
SET c_1 = 0The following is sample output from DBCC trace flag 1200:
Process 11 requesting page lock of type SH_PAGE on 7 2
Process 11 releasing page lock of type SH_PAGE on 7 2
Process 11 releasing page lock of type SH_PAGE on 7 2
Process 11 requesting table lock of type EX_TAB on 7 8000331
Process 11 clearing all pss locks
For years, users have complained about SQL Server's lack of row-level locking capabilities. Until version 6.5, all locking was done at the page level (a 2K unit of data). The problem with a page-level locking mechanism surfaces when multiple records exist on the same page and multiple processes must access information on the same page. If the page is locked by a process, other processes must wait to access the data.
To help resolve the issues associated with page-level locking, SQL Server 6.5 kinda has row-level locking. The reason I say it kinda has row-level locking is because row-level locking is possible only for INSERT operations and it must be enabled on a table-by-table basis. In SQL Server 6.5, row-level locking is off by default.
Therefore, you can gain a limited amount of functionality (reduced blocking and the potential for improved INSERT performance) by using row-level locking. Because row-level locking works only with the INSERT statement, it is best suited for tables that meet the following criteria:
To implement row-level locking, use the sp_tableoption system procedure. Following is the syntax for sp_tableoption:
sp_tableoption @TableNamePattern [, `@OptionName'] [, `@OptionValue']
For example, to enable row-level locking for all tables in the pubs database, use the following syntax:
use pubs
go
EXECUTE sp_tableoption `%.%', `insert row lock', `true'
NOTE: When you enable row-level locking for all tables in a database, you enable row-level locking for only the currently defined tables in the database. If you add another table to your database and you want to enable row-level locking for that table, you must explicitly configure the table to use row-level locking by using the sp_tableoption system procedure.
To enable row-level locking for the authors table, use the following syntax:
use pubs
go
EXECUTE sp_tableoption `authors', `insert row lock', `true'
To disable row-level locking for the authors table, use the following syntax:
use pubs
go
EXECUTE sp_tableoption `authors', `insert row lock', `false'
To view the row-level lock status for all the tables in a database, use the following syntax:
EXECUTE sp_tableoption `%.%', `insert row lock'
CAUTION: Row-level locking can lead to deadlocks if you enable row-level locking on a table and then perform a large number of concurrent INSERT statements followed by an UPDATE statement within a transaction. The deadlock results from contention for the data within the transaction. The following example may lead to deadlock:
begin transaction
/* insert a record */
INSERT INTO stores (stor_id, stor_name, stor_address, city, state, zip)
VALUES (`234','some_store_name','some_store_addr','some_city',
`VA','11111')
/* update the record we just inserted */
UPDATE stores
SET stor_name = `new_store_name'
WHERE stor_id = `234'
commit transactionTo minimize the risk of deadlock, either disable row-level locking for the table being modified or revise your code to eliminate the update to the record that was just inserted within a transaction.
Now that you know why it is important to understand SQL Server's locking be-havior and how you can view active locks, you need to get into the nuts and bolts of locking.
Any transaction that reads or modifies data (SELECT, INSERT, DELETE, UPDATE, CREATE INDEX, and so on) generates some type of lock. The degree of locking is determined by the following two questions:
To answer these questions, you must look at the two levels of physical locks: page locks and table locks. Page Locks A page lock is a lock on a 2K data page. Whenever possible, SQL Server attempts to use a page lock rather than a table lock. Page locks are preferred over table locks because they are less likely to block other processes. There are three main types of page locks:
NOTE: Typically, read statements acquire shared locks and data modification statements acquire exclusive locks.
Table Locks A table lock occurs when the entire table (data and indexes) is locked. When this happens, SQL Server has detected that it is faster to process the transaction by locking the table rather than incurring the overhead of locking numerous pages.
SQL Server usually begins the transaction by placing page locks on the data being accessed. By default, if more than 200 pages are acquired within a transaction, SQL Server automatically escalates the page locks into a table lock.
NOTE: The 200-page limit can be overridden with SQL Server 6.x. See the topic "Lock Escalation" in the "Multi-User Configuration Options" section of this chapter for more information.
The drawback of table locking is that it increases the likelihood of blocking. When other transactions try to access or modify information in a locked table, they must wait for the table lock to be released before proceeding. There are three main types of table locks:
Table 23.2 summarizes the different types of locks that can be placed on an object.
Object | Lock Type |
Page | Shared |
Exclusive | |
Update | |
Table | Shared |
Exclusive | |
Intent |
Using Index? | Syntax | Table Level Locks | Page Level Locks |
N/A | INSERT | Exclusive intent | Exclusive page |
Yes | SELECT | Shared intent | Shared page |
Yes | SELECT with HOLDLOCK | Shared intent | Shared page |
Yes | UPDATE | Exclusive intent | Update and exclusive |
Yes | DELETE | Exclusive intent | Exclusive page |
No | SELECT | Shared intent | Shared page |
No | SELECT with HOLDLOCK | Shared table | None |
No | UPDATE | Exclusive table | None |
No | DELETE | Exclusive table | None |
N/A | Create clustered index | Exclusive table | None |
N/A | Create nonclustered index | Shared table | None |
NOTE: Before version 6.x, you could run out of exclusive locks when building clustered indexes on large tables. This problem has been resolved with SQL Server 6.x.
Try using the tips in the following sections to resolve locking problems. These
tips can help minimize locking problems and prevent deadlocks.
Tip 1: Use an Index with UPDATE/DELETE Statements Whenever you issue
an UPDATE or DELETE statement that does not use an index,
an exclusive table lock is used to process the transaction. The exclusive table lock
may block other transactions.
To reduce the chance of an exclusive table lock, specify a WHERE clause
that takes advantage of an existing index. This may enable SQL Server to use page
level locks instead of an exclusive table lock.
Tip 2: Convert a Large INSERT Statement into a Single INSERT
Statement Within a Loop Inserting a large number of rows into a table may result
in an exclusive table lock (for example, INSERT INTO table2 SELECT * FROM table1).
To avoid this problem, convert the INSERT statement into an INSERT
statement within a loop. For example, the following code opens a cursor and then
initiates a loop that fetches the data from table1 into a variable and then
inserts the contents of the variable into table2. This approach decreases
the likelihood of blocking because it generates exclusive page locks rather than
an exclusive table lock. The drawback of this approach is that it runs slower than
a batch INSERT.
declare @col1 varchar(11)
declare sample_cursor cursor
for select col1 from table1
open sample_cursor
fetch next from sample_cursor into @col1
while @@fetch_status = 0
begin
insert into table2 values (@col1)
fetch next from sample_cursor into @col1
end
deallocate sample_cursor
Tip 3: Avoid Using HOLDLOCK HOLDLOCK is one of the keywords that almost every developer new to SQL Server has tried to use. Quite often, the developer uses HOLDLOCK without fully understanding the ramifications behind it.
When HOLDLOCK is used with a SELECT statement, all shared locks (remember that shared locks are acquired whenever a SELECT is issued) remain in effect until the transaction is complete. This means additional locking overhead, which degrades performance and increases the likelihood of blocking or deadlocks. When the HOLDLOCK command is not used, SQL Server releases the shared locks as soon as possible rather than waiting for the transaction to complete.
What usually happens is that developers use the HOLDLOCK command, thinking
that they can temporarily prevent other users from reading the same data. What they
do not realize is that HOLDLOCK only generates shared locks, not exclusive
locks. Because the locks are shared, other users can still read the same data values.
Tip 4: Place Clustered Indexes on Tables that Have a High Frequency of Inserts
Whenever you insert data into a table without a clustered index, you increase the
risk of contention for the last data page. In a high-transaction environment with
multiple users, this can lead to blocking.
Add a clustered index to your table to prevent this problem. By adding a clustered
index, the inserted rows are distributed across multiple pages, reducing the likelihood
of contention for the same page.
Tip 5: Keep Transactions Short Long running transactions--especially data
modification transactions--increase the likelihood of blocking and deadlocks. Whenever
possible, try to keep the length of a transaction to a minimum. Following are suggestions
to help decrease the length of a transaction:
INSERT INTO t_1
SELECT * FROM t_2
INSERT INTO t_1
SELECT * FROM t_2
WHERE t_2.id <= 50
INSERT INTO t_1
SELECT * FROM t_2
WHERE t_2.id > 50
Tip 6: Understand Transactions Two common misunderstandings in using transactions are nested transactions and user interaction within a transaction. Nested Transactions Look at the approach taken in Figure 23.8. Do you see any problems with the code?
Figure 23.8.
A nested transaction: the common (incorrect) approach.
The problem is with the rollback statement for the inner transaction. If the inner
transaction is rolled back, you receive an error message and both INSERT
transactions are automatically rolled back by SQL Server, with the following error
message:
The commit transaction request has no corresponding BEGIN TRANSACTION.
To avoid the error message, use the SAVE TRANSACTION statement (see Figure 23.9).
Figure 23.9.
A nested transaction: the correct approach.
NOTE: The COMMIT TRANSACTION statement must be issued after the ROLLBACK TRANSACTION INNER_TRANS statement.
Now look at Figure 23.10. If the outer transaction is rolled back, do you think the inner transaction will also be rolled back? The answer is yes. SQL Server always rolls back the inner transaction when the outer transaction is rolled back, even though the inner transaction has been committed. This is how SQL Server handles nested transactions!
Figure 23.10.
The way in which SQL Server handles nested transactions.
NOTE: In my experience as a DBA, the way SQL Server handles nested transactions is often contrary to what developers expect. Developers usually expect the inner transaction to not be rolled back because it has been committed. Make sure that your developers understand how SQL Server handles nested transactions.
Whenever you nest a transaction, all locks are held for the duration of the transaction (see Figure 23.11). This means that when the inner transaction is committed, its locks are not released until the outer transaction is committed. Be on the lookout for nested transactions; they increase the likelihood of blocking or deadlocks.
Figure 23.11.
The way locks are held within a nested transaction.
User Interaction Within a Transaction Keeping a watchful eye on transaction
implementation can help ward off blocking. Consider the example in Figure 23.12.
This situation virtually guarantees blocking in a multi-user environment. Always
avoid user interaction within a transaction.
Figure 23.12.
User interaction within a transaction.
You should rewrite the transaction to prompt the user first; based on the user's
response, you can then perform the DELETE (see Figure 23.13). Transactions
should always be managed in a single batch.
Figure 23.13.
The transaction rewritten to avoid user interaction.
Tip 7: Run Transactions that Modify Large Amounts of Data During Off Hours
You should process CREATE CLUSTERED INDEX and mass UPDATE/DELETE/INSERT
statements during off hours. These types of transactions require exclusive table
locks and can be resource intensive.
Tip 8: Add More Memory to Your Server By adding more memory to your server,
you increase the amount of data that can remain in cache. This improves transaction
performance, which reduces resource contention.
Tip 9: Know How to Safely Increment an ID Most applications require some type
of auto-incrementing ID to be used as a key field. Before a new record is inserted
into the table, the application must get the next available ID.
The easiest way to create an auto-incrementing ID is to use the identity property. (The identity datatype is new with SQL Server 6.x.) This datatype has been optimized for performance and eliminates the need to have a separate table to track the next available ID.
If you cannot use the identity property (for example, when you need complete control over the counter values), you can use the following stored procedure to return the next ID. Because the update statement is within a transaction, the risk of two users receiving the same ID is eliminated. The following stored procedure returns the next ID:
CREATE PROCEDURE usp_next_id AS
declare @next_id integer
begin transaction
update t_1
set id = id + 1
select @next_id = id from t_1
commit transaction
RETURN @next_id
The following is an example of how to use the usp_next_id stored procedure:
declare @next_id integer
exec @next_id = usp_next_id
select @next_id
Tip 10: Tune the Lock Escalation Threshold The standard 200-page lock threshold limit can be unnecessarily low for very large tables. By tuning the lock escalation threshold level, you may be able to decrease the frequency of table locks. See the topic "Lock Escalation" in the "Multi-User Configuration Options" section of this chapter for more information.
SQL Server has several configuration options that allow you to tailor locking and other multi-user considerations. These options provide maximum control for multi-user access.
At the server level, you can configure the maximum number of locks, the maximum number of open objects, and the point at which lock escalation occurs.
All server-level configuration options can be changed by using the Enterprise Manager or the sp_configure system procedure. The following steps explain how to change a server configuration option with the Enterprise Manager:
Figure 23.14.
How to configure server options using the Enterprise Manager.
Maximum Number of Locks To configure the maximum number of locks, modify the locks setting in the Server Configuration/Options dialog box. The default installation value is 5000. When setting this value, keep in mind that each lock uses 32 bytes of memory.
The following is the syntax for the sp_configure system procedure:
sp_configure `locks', [value]
NOTE: If you see the following error message, you must increase the maximum number of locks:
Error 1204, SQL Server has run out of LOCKS. Re-run your command when there are fewer active users, or ask your System Administrator to reconfigure SQL Server with more LOCKS.Typically, the default installation value of 5000 locks is insufficient for most systems.
TIP: Use the Performance Monitor to track lock usage. You can use this value to help determine whether your lock configuration value is reasonable.
Maximum Number of Open Objects To configure the maximum number of open objects, modify the open objects setting in the Server Configuration/Options dialog box. This setting controls the maximum number of open objects for a server. The default installation value for this option is 500. Each open object uses 70 bytes of memory. The following is the syntax to configure the number of open objects:
sp_configure `open objects', [value]
Lock Escalation Lock escalation occurs when multiple page locks are converted into a single table lock. At this point, SQL Server determines that it is more efficient to process the transaction with a table lock rather than numerous page locks.
NOTE: The capability to configure lock escalation is new with SQL Server 6.x. Previous versions of SQL Server automatically escalated page locks to a table lock when 200 or more page locks had been acquired.
The default installation value for lock escalation is 200 pages. With a very large table, the default value may not be an optimal setting. For example, a modification to 1 percent of the data in a 200M table automatically produces a table lock. This results in unnecessary table locking, which can lead to increased blocking.
By tuning the lock escalation point, you avoid unnecessary table locking. You can use the configuration settings shown in Table 23.4 to control lock escalation.
Threshold Name | Threshold Type | Default Setting | Minimum Setting | Maximum Setting |
LE threshold maximum | Maximum threshold value | 200 | 2 | 500000 |
LE threshold minimum | Minimum threshold value | 20 | 2 | 500000 |
LE threshold percent | Threshold value based on percentage of table size | 0 | 1 | 100 |
NOTE: LE threshold minimum is an advanced option. To view and set advanced options, you must use the SHOW ADVANCED OPTION syntax with sp_configure. Following is the syntax to show advanced options:
sp_configure `show advanced option',1
With SQL Server, you can configure the transaction isolation level for a connection.
A transaction isolation level remains in effect for the life of the connection unless
the value is modified or the connection is broken.
NOTE: The capability to configure the transaction isolation level is new with SQL Server 6.x.
To set the transaction isolation level, use the SET TRANSACTION ISOLATION LEVEL command, as in the following syntax. For an explanation of the differences among transaction isolation levels, see Table 23.5.
SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ
UNCOMMITTED |
REPEATABLE READ | SERIALIZABLE}
Setting | Purpose |
READ COMMITTED | SQL Server's default transaction isolation level. Prevents dirty reads; nonrepeatable reads may occur with this setting. |
READ UNCOMMITTED | Minimizes locking by issuing locks only for UPDATE commands. Using this setting may result in dirty reads, phantom values, and nonrepeatable reads. |
REPEATABLE READ | See SERIALIZABLE. |
SERIALIZABLE | Prevents dirty reads, phantom values, and nonrepeatable reads. In terms of performance, this setting is the least efficient option. |
With the SELECT statement, you can specify and sometimes override SQL Server's default locking behavior. To specify the locking behavior, use the keyword HOLDLOCK, UPDLOCK, NOLOCK, PAGLOCK, TABLOCK, or TABLOCKX. Note: With the exception of HOLDLOCK, the capability to explicitly control locking is new with SQL Server 6.x.
The following is the syntax to control explicit locking. See Table 23.6 for an explanation of the differences among explicit locking levels.
SELECT select_list FROM table list [HOLDLOCK | UPDLOCK | NOLOCK | PAGLOCK | TABLOCK | TABLOCKX]
Lock | Purpose |
HOLDLOCK | Forces all locks to be held for the duration of the trans-action. |
NOLOCK | Turns off locking. Permits dirty reads. |
PAGLOCK | Forces page locking rather than table locking. |
TABLOCK | Forces table locking rather than page locking and uses a shared lock. |
TABLOCKX | Forces table locking rather than page locking and uses an exclusive lock. |
UPDLOCK | Forces an update lock to be issued rather than a shared lock. This type of lock ensures consistency when you intend to read data and then perform an update based on the values you just read. |
Following are important notes to remember when addressing multi-user issues in SQL Server:
As a DBA, you are guaranteed to run into blocking, deadlocks, and data consistency problems. The more users you have, the more likely you are to experience these problems. Hopefully, the topics discussed in this chapter will help you ward off these problems before they impact your production environment.
Speaking of your production environment, the following chapter can help keep your
databases up and running 24x7 (24 hours a day, 7 days a week).
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.