Controlling SQL SERVER Locking Behaviour with PeopleSoft Application

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:


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.


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

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.

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.


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
AND RowNumber < @DLChainRowNumber
AND EventClass = 41 -- SQL:StmtCompleted
AND TextData like 'COMMIT TRAN%'
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:


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.


• 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:


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.