SQL Server
uses isolation levels and locking as its method of achieving data consistency
(atomicity). SQL Server locks are applied at various levels of granularity in
the database. Locks can be acquired on rows, pages, keys, ranges of keys,
indexes, tables, or databases. SQL Server dynamically determines the
appropriate level at which to place locks for each Transact-SQL statement. SQL
Server uses dynamic locking – which means that little or no configuration is
needed in order for SQL Server to achieve isolation and concurrency.
Choosing an
isolation level does not affect the locks that are acquired to protect data
consistency. Regardless of the isolation level, transactions will always hold
an exclusive lock on the data being modified until the transaction completes;
however, the isolation level does define how a concurrent transaction will be
affected, trying to read the same data.
The
important aspects of SQL Server locking that particularly affect performance
include isolation levels, lock granularity, and lock escalations.
Isolation
Levels
Transaction
isolation levels control the following in a SQL Server database:
• Whether
locks are acquired and the type of locks acquired when data is read.
• Duration
of the read locks.
• Whether a
read operation referencing rows modified by another transaction:
• Blocks
until the exclusive lock on the row is freed.
• Retrieves
the committed version of the row that existed at the time the statement or
transaction started.
• Reads the
uncommitted data modification.
Choosing a
transaction isolation level does not affect the locks acquired to protect data
modifications. A transaction always gets an exclusive lock on any data it
modifies, and holds that lock until the transaction completes, regardless of
the isolation level set for that transaction. For read operations, transaction
isolation levels primarily define the level of protection from the effects of modifications
made by other transactions.
The
recommended isolation level for PeopleSoft applications is the read-committed
snapshot isolation level.
Warning! Ensure that the version of
PeopleTools you are using supports the read-committed snapshot isolation level.
You can only use it if it is supported by PeopleTools.
Under this
isolation level, blocking and deadlocking issues due to lock contention are
greatly reduced. Read operations only acquire an Sch-s lock at the table. No
page or row S locks are acquired and, therefore, do not block transactions that
are modifying data.
Lock
Granularity
SQL Server
supports the following basic locking grains (levels):
• Table
• Page
• Key
• Key Range
• Row (or
RID)
These
locking grains represent the initial locking grain as determined by SQL
Server’s dynamic locking mechanism. When the lock grain is higher (table or
page), it reduces the amount of CPU and memory resources spent on maintaining
the locks.
However, it
reduces concurrency. When lock grain is lower (key or row), the reverse is
true.
In SQL
Server , the ALTER INDEX statement with the ALLOW_ROW_LOCKS and
ALLOW_PAGE_LOCKS options can be used to customize the initial lock grain for an
index or an entire table, including indexes. These options will allow (or disallow)
row or page locks on the specified object. The default for these options is ON,
that is, row and page locks are allowed.
Note. Row locks on non-clustered indexes
refer to the key or row locator entries in the index’s leaf pages.
By
disallowing page locks, you can increase write concurrency, and can reduce
writer – writer deadlocks. For example:
ALTER INDEX
PS_BO ON PS_BO
SET
(ALLOW_PAGE_LOCKS = OFF) ;
Note. In SQL Server, when using the
read-committed snapshot isolation level, lock contention or blocking issues due
to writers blocking readers are eliminated. Therefore, the need to use the
ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options for controlling locking to avoid
blocking and improving concurrency is greatly reduced for writer – reader
blocking scenarios.
If an index
(or table) is dropped and re-created, ALTER INDEX will need to be re-executed
to reestablish the customized locking for the table or index.
Note. In versions prior to SQL Server, the sp_indexoption stored procedure was used to allow or disallow
page and row locks. This feature will be removed in future versions of SQL
Server. Avoid using this stored procedure; instead use ALTER INDEX.
Lock
Escalations
Lock
escalation is a technique to lower the number of locks taken by a transaction,
and to manage the total amount of memory used for locks in control. SQL Server
automatically escalates row, key, and page locks to coarser table locks as
appropriate.
Lock
escalation converts many individual rows or page locks to table locks.
Lock
Escalation Triggers and Mechanism
Lock
escalation is triggered when the lock count for one transaction exceeds 5000,
or the lock count for one index or table exceeds 765. The lock manager
determines how much memory is allocated for locks. If more than 40 percent of
the memory pool is used for locks, SQL Server attempts to escalate multiple
page, key, or RID locks to table locks. SQL Server tries to find a table that
is partially locked by the transaction, and holds the largest number of locks
for which no escalation has already been performed and is capable of
escalation. If any other process holds a contradictory lock on any rows, pages,
or keys on the table, lock escalation cannot be done on that table.
When the
threshold is reached (that is, lock memory is greater than 40 percent of SQL
Server memory), SQL Server attempts to escalate locks to control the amount of
memory used for locks. It identifies a table within the transaction that holds
the maximum amount of locks as a good candidate for lock escalation. However,
if it finds any incompatible locks held on the table, it skips that table.
If the
table lock requested cannot be granted, escalation is not blocked; the
transaction continues and escalation will be requested when the next multiple
of 1250 locks have been acquired by the transaction.
Lock
Escalation
Hierarchy
Lock
escalation never converts row locks to page locks, but always converts them to
table locks. The escalation is always directly to a table lock.
Lock
Escalation and Performance
Though lock
escalation may result in blocking and deadlocks, they are not the only cause of
blocking and deadlocks. Often blocking and deadlocks happen because of the
application and the nature of the usage even without any lock escalation.
If lock
escalation is causing performance issues through excessive blocking or
deadlocking, lock escalation can be prevented.
Use the
following techniques to prevent lock escalation:
• Use SQL
Server Profiler and monitor lock escalations to find out how frequently lock
escalation occurs and on what tables.
• Ensure
that SQL Server has sufficient memory. Use the SQL Server Performance Monitor
to monitor Total Server Memory (KB) and Lock Memory.
• Determine
if the transaction provides a way to control the commit frequency. If yes,
increase the commit frequency. A good example is Global Payroll – PAYCALC.
• Disable
lock escalation with one of the following two methods (DBCC TRACEON (1121) or
UPDLOCK HOLDLOCK).
Method 1
Use this
SQL Server command DBCC TRACEON (1211) to disable lock escalation on the entire
database instance. Although this eliminates lock escalations, it also defeats
the purpose of lock escalations and is why this command is undocumented. When this
command is enabled, your system can potentially allocate its whole memory for
the locks, which affects the performance of every user. In addition SQL Server introduces a new trace flag 1224 which is very similar to trace flag 1211,
but will only disable lock escalation until the lock structures consume 40% of
the memory used by SQL Server in the low 2GB (i.e. excluding the AWE memory).
If both trace flags 1224 and 1211 are specified, trace flag 1224 takes
precedence over 1211.
Note. Do not try the above setting in your
production environment.
Method 2
With this
method you can disable lock escalation for a specific table.
Warning!
Only experienced
database administrators should use this method, and with caution.
From SQL
Server Profiler you know that lock escalation mostly happens on one table. You
want to eliminate the lock escalation on that table, for example PS_BO_CM.
1. Open a
query window in Management Studio and connect to the database as dbowner.
2. Enter
the following to start a transaction:
begin tran
SELECT * FROM PS_BO_CM WITH (UPDLOCK HOLDLOCK) WHERE 1 =2
This
selects 0 records because the condition 1 =2 is always false. However, with the
hint WITH (HOLDLOCK), the transaction places an Intent Shared Lock on the
table. This prevents any SQL statements from taking an X- Table lock on this
table, including the escalation.
3. At the
end of the period or the day, you can simply come back to the query window and
enter the following to release your Intent Shared Lock on the table:
Note. This approach is useful if you want
to ensure no lock escalation on a particular table.
In SQL
Server, the read-committed snapshot isolation level is the recommended
setting for PeopleSoft applications. This isolation level has no direct effect
on lock escalation; however, it does alleviate lock contention or blocking
problems caused by lock escalation. For instance, if an UPDATE statement causes
lock escalation and the entire table is locked, under readcommitted snapshot
isolation level, a concurrent read transaction on the table would not be
blocked.
Warning!
Ensure that the
version of PeopleTools you are using supports the read-committed snapshot
isolation level. You can only use it if it is supported by PeopleTools.
Locking
Trace Flags
In very
specific cases, trace flag 1211 can be used to suppress the request for table
locks. This also disables lock escalation due to memory pressure. Use of this
trace flag is not advisable for PeopleSoft applications.
As an
alternative, use trace flag 1224 in SQL Server to suppress lock escalation
rooted in the estimated number of locks, but allow lock escalation due to
memory pressure. Trace flag 1224 is new in SQL Server and does not exist
in earlier versions of SQL Server. For PeopleSoft applications, trace flag 1224
is better suited to suppress lock escalation than trace flag 1211.
Deadlocks
In SQL
Server , with the introduction of the new read-committed snapshot isolation
level, lock contention or blocking problems are greatly reduced. Read-committed
snapshot isolation eliminates writers blocking readers and readers blocking writers.
This, in turn, would also eliminate the read – write deadlock scenarios, where
an UPDATE and a SELECT transaction deadlock. However, deadlocks caused by two
concurrent UPDATE transactions and other writer – writer scenarios still may exist.
The information that follows will help you analyze and debug these deadlocks.
A deadlock
occurs when two processes are waiting for a resource and neither process can
advance because the other process prevents it from getting the resource.
Knowing the
following information is a starting point to resolve a deadlock:
• Processes
that caused the deadlock.
• Deadlock
trace.
• SQL
statements that caused the deadlock.
• SQL statements
within the transaction, where the deadlock happened.
• Execution
plan on each SQL statement that resulted in a deadlock.
Deadlocks
can be monitored in one of three ways: using trace flag 1204, using trace flag
1222, or using SQL Server Profiler.
Using
Trace Flag 1204 or Trace Flag 1222
When
deadlocks occur, trace flag 1204 and trace flag 1222 return information that is
captured in the SQL Server error log. Trace flag 1204 reports deadlock
information formatted by each node involved in the deadlock. Trace flag 1222
formats deadlock information, first by processes, and then by resources in an
XML format. It is possible to enable both trace flags to obtain two
representations of the same deadlock event.
SQL Server
can be started with trace flag 1204 or 1222 enabled. To do so, open SQL Server
Enterprise Manager, right-click on your server instance, and select Properties.
In the General tab, click Startup Parameters. Add the following
line:
-T1204
-T3605
The output
of the deadlock trace will be logged into the SQL Server error log specified by
the -e parameter in Startup Parameters. The 3605 flag causes the output
to go to the error log rather than the screen. This is set as the default in
SQL Server.
Alternatively,
deadlock tracing can be enabled with the following command:
DBCC
TRACEON (1204, 3605,-1)
Here is
sample output from DBCC TRACEON for 1204:
The output
contains the following information:
• The object
involved in the deadlock. In this example, it is 10:2045302396:2, where 10 is
the database ID, 2045302396 is the object ID, and the final 2 is the index ID.
Entering
the following gives you the name of the database where the deadlock occurred:
SELECT
DB_NAME(captured_database_id)
From the
deadlocked database, entering the following shows the table involved:
SELECT
OBJECT_NAME(captured_object_id)
Entering
the following shows the index involved:
SELECT NAME
FROM sys.indexes WHERE object_id = captured_object_id AND indid = captured_index_id
• The statement
type shows what kind of statement it is (for example, INSERT or UPDATE).
• ‘Input
buf:’ (input buffer) shows the actual statement. However, in a PeopleSoft
environment you see either sp_prepexec or sp_cursorexec. This is not very
useful in identifying the SQL statement.
Using
SQL Server Profiler
A better
alternate is to enable SQL Server Profiler.
To use this
profiler output to determine the cause of a deadlock:
1. Save the
output into a trace table. From the File menu, select Save As,
and choose Trace Table.
2. Use the
following SQL statement to find the list of Deadlock Chain events.
SELECT *
FROM DLTRACE1 WHERE EventClass =59
DLTRACE1 is
the trace table and EventClass 59 is for deadlocks.
From the
output you can determine which SPID is involved in the deadlock and note down
the row number for this Deadlock
Chain event.
3.
Substitute the values in the following query and you will find all the SQL
statements used by that process as part of the deadlocked transaction.
DECLARE
@LastCommitPoint int, @DLSpid int, @DLChainRowNumber int
/* Set the
Deadlock SPID and the Deadlock Chain’s rownumber */
SET @DLSpid
= 134
SET
@DLChainRowNumber = 159501
SELECT
@LastCommitPoint = max(RowNumber) FROM DLTRACE1
WHERE SPID
= @DLSpid
AND
RowNumber < @DLChainRowNumber
AND
EventClass = 41 -- SQL:StmtCompleted
AND
TextData like 'COMMIT TRAN%'
SELECT *
FROM DLTRACE1
WHERE SPID
= @DLSpid
AND
RowNumber < @DLChainRowNumber
AND
RowNumber > @LastCommitPoint
AND
EventClass = 45 -- SP:StmtCompleted
4. Repeat
the previous steps for the other Deadlock Chain events. These SQL statements
will present a clear picture of how the deadlock happened.
The
following EventClass classes and their corresponding IDs are relevant to the
PeopleSoft environment.
/*
RPC:Completed
- 10
Show Plan
Text -96
Execution
Plan - 68
RPC:Starting
- 11
Lock:Escalation
- 60
Lock:Deadlock
- 25
Lock:Deadlock
Chain - 59
SP:StmtStarting
- 44
SP:StmtCompleted
– 45
SQL:StmtStarting
- 40
SQL:StmtCompleted
- 41 (COMMIT TRAN)
*/
This
document’s appendix includes an example of a procedure that automates the
process. You can use this procedure as a model and
modify it for your purposes.
Resolving
a Deadlock
The following
actions can help when resolving a deadlock
• Determine
whether read-committed snapshot isolation is enabled. (Only applicable to those
versions of PeopleTools that support read-committed snapshot isolation level.)
• Determine
whether the table contains recent statistics.
• Use the
INDEXPROPERTY statement to determine whether page locks are disallowed on a
table.
For
example:
SELECT
INDEXPROPERTY(OBJECT_ID('PS_BO'), 'PS0BO', 'IsPageLockDisallowed')
A return
value of 0 means that page locks are allowed; a value of 1 means page locks are
disallowed.
You can use
ALTER INDEX to disallow page locks.
ALTER INDEX
PS_BO ON PS_BO
SET
(ALLOW_PAGE_LOCKS = OFF) ;
• Create
any additional indexes that could help resolve the deadlock. Review the
execution plans of the SQL statements that caused the deadlock and determine if
they do an index scan. If they do, see if creating an additional index changes the
access path for the SQL statement from index scan to index seek.
For
example, examine the following SQL statement:
SELECT
DISTINCT EOEW_MAP_OBJ
FROM
PS_EOEW_RUN_PROC
WHERE
RUN_CNTL_ID LIKE :1 %CONCAT '.%'
The SQL
statement does a clustered index scan because the leading key of the existing
index is OPRID, but the SQL statement does not use OPRID as part of the WHERE
clause.
The
solution is to add another index with RUN_CNTL_ID as a leading key:
Note. PeopleSoft applications are
delivered with the necessary indexes that are required for an application and
its performance for typical usage. They are not delivered with all the possible
indexes because an index creates unnecessary overhead (on INSERT, UPDATE, and
DELETE) if it is not useful for an implementation. Your implementation (data
and business processes) may warrant some additional indexes.
• Adding an
index cover for a non-clustered index to cover the query could help resolve the
deadlock. In the previous example, the SQL statement would use the new index
first, but to get the EOEW_MAP_OBJ, it has to go to the table.
It would
use the available clustered index to perform this task. If EOEW_MAP_OBJ is also
added to the new nonclustered index, the query becomes a covered query. In
other words, SQL Server could build the result set entirely by reading the
index. If the column you are trying to add to a non-clustered index is part of
clustered index, there is no need to add that column to the non-clustered index
for the purpose of index cover.
• Pay attention
to lock escalations.
