Locks provide the isolation aspect of transactions. They are acquired on data resources to prevent data inconsistency problems. SQL Server allows you to control the level of consistency you will get from your data by setting the isolation level of your session or query. I’ll cover isolation levels later in the chapter. In this section, I’ll focus on locks.

Locks can be obtained on resources at different granularity levels of data. The smallest granularity of data is the row level. If a row of a heap is locked (a heap is a table without a clustered index), the locked resource is a row identifier (RID). If a row in an index is locked, the locked resource is a key. A lock can also be obtained on a page, an extent, a table, and on other types of resources as well. SQL Server automatically chooses which resource type to lock. In your queries, you can specify a table hint where you mention the resource type that you want to be locked (ROWLOCK, PAGLOCK, TABLOCK). SQL Server might choose not to adhere to your request if it doesn’t have enough resources to satisfy it or if your request conflicts with other locks.

Lock modes determine how resources can be accessed by concurrent transactions. That is, if a process is granted a certain lock mode on a resource, another process attempting to acquire an incompatible lock mode on the same resource will be blocked. When you modify data, your transaction needs to acquire an exclusive (X) lock on the resource. If granted, the transaction will keep the exclusive lock until it is committed or rolled back. When you read data, by default (in the default isolation level) your transaction will need to acquire a shared (S) lock on the resource. If a shared lock is granted, your transaction will keep it only until the resource has been read.

Exclusive locks are incompatible with all lock modes. Similarly, all lock modes are incompatible with exclusive locks. That is, if one process holds a lock of any mode on a resource, another process cannot obtain an exclusive lock on that resource. Similarly, if one process holds an exclusive lock on a resource, another process cannot obtain a lock of any mode on that resource–thus the mode name exclusive. Shared locks, on the other hand, can be obtained while other processes hold shared locks on the same resource–thus the mode name shared.

SQL Server also supports other lock modes. An update (U) lock is used on resources that can be updated. Only one process at a time can hold an update lock on a resource. You can use an update lock to prevent deadlocks that can take place when processes are reading, locking (and maintaining the locks), and later updating the resource.

When SQL Server intends to acquire a lock on a resource, it first requests Intent locks on resources higher in the lock hierarchy (row → page → table). This lock mode is used to "signal" the intent to lock a resource lower in the lock hierarchy, and to simplify the lock incompatibility detection between different levels of granularity. For example, suppose your process requests to lock a row exclusively. SQL Server will first request intent exclusive (IX) locks at the table and page levels. Assume your process obtained all locks it requested. If another process attempts to acquire an incompatible lock on the same row, page, or table, SQL Server will detect the conflict immediately thanks to the intent locks.

Schema locks are acquired by processes that either change or depend on the schema of an object. A schema modification (Sch-M) lock is obtained when a data-definition-language (DDL) activity takes place against an object, and it blocks all activities against the object issued by other processes. Schema stability (Sch-S) locks are used when SQL Server compiles queries to block other processes from performing schema changes against the object.

Bulk update (BU) locks are used to allow multiple sessions to bulk load data into a table, while blocking processes from performing activities against the table other than bulk loads.

Finally, key-range locks protect ranges of rows. These are used by the serializable isolation level. I’ll elaborate on this lock mode later in the chapter when discussing isolation levels.

For your convenience, Table 9-3 has a summary adapted from Books Online of lock compatibilities between some different lock modes.

As it does with lock resource types, SQL Server determines lock modes automatically. You can use a table hint to request that SQL Server use a specific lock mode (for example, XLOCK or UPDLOCK).

To demonstrate a blocking scenario, open three connections, and call them connections 1, 2, and 3.

Run the following code in connection 1 to open a transaction and update a row in table T1:

SET NOCOUNT ON;
USE testdb;
GO
BEGIN TRAN
  UPDATE dbo.T1 SET col2 = 'BB' WHERE keycol = 2;

The UPDATE transaction was granted with an exclusive lock, and the change was applied. Update locks are held until the end of a transaction, and because this transaction remains open, the process preserves the lock.

Run the following code in connection 2 to attempt to select all rows from T1:

SET NOCOUNT ON;
USE testdb;
GO
SELECT keycol, col1, col2 FROM dbo.T1;

Connection 2 needs a shared lock to read the data, but it cannot obtain one because an exclusive lock is held by connection 1 on one of the rows. Connection 2 is blocked. By default, SQL Server does not set any lock timeout, so if connection 1 will not release the lock, connection 2 will just keep on waiting.

To troubleshoot blocking scenarios, SQL Server 2005 gives you a whole array of new dynamic management views (DMV) and functions (DMF). For example, the sys.dm_tran_locks view gives you information about locks. Run the following query in connection 3, which generates the output shown in Table 9-4:

SET NOCOUNT ON;
USE testdb;
GO
-- Lock info
SELECT
  request_session_id            AS spid,
  resource_type                 AS restype,
  resource_database_id          AS dbid,
  resource_description          AS res,
  resource_associated_entity_id AS resid,
  request_mode                  AS mode,
  request_status                AS status
FROM sys.dm_tran_locks;

You can observe that processes 53 and 54 are in conflict (of course, you might get different session IDs). Process 53 was granted an exclusive key lock, while process 54 is waiting for a shared lock on the same resource (last row in Table 9-4). To get similar information in SQL Server 2000, use the stored procedure sp_lock or the system table syslockinfo.

Query the sys.dm_exec_connections view to obtain information about the connections involved in the conflict (connect_time, last_read, last_write, most_recent_sql_handle, etc.):

SELECT * FROM sys.dm_exec_connections
WHERE session_id IN(53, 54);

In SQL Server 2000, the terms connection and session were synonymous; however, SQL Server 2005 supports multiple active result sets (MARS), where a single connection can have multiple active sessions running asynchronously. Therefore, SQL Server 2005 separates them into two views: sys.dm_exec_connections for connections, and sys.dm_exec_sessions for each session. Query the sys.dm_exec_sessions view to obtain information about the sessions involved in the conflict (login_time, host_name, program_name, login_name, last_request_start_time, last_request_end_time, state of set options, transaction_isolation_level, etc.):

SELECT * FROM sys.dm_exec_sessions
WHERE session_id IN(53, 54);

To get similar information in SQL Server 2000, use the sp_who/sp_who2 stored procedure or the sysprocesses system table.

SQL Server 2005 also provides you with the sys.dm_exec_requests view, which gives you information about currently executing requests. For a blocked session, the blocking_session_id attribute of the view will give you the session ID of the blocking session. Query the view to obtain information about blocked requests (blocked session_id, blocking_session_id, wait_type, wait_time, wait_resource, state of set options, etc.):

SELECT * FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

The sys.dm_exec_connections view contains a binary handle that you can provide to the function sys.dm_exec_sql_text to get the code text of the last request. For example, to get the code text for sessions 53 and 54, issue the following query, which generates the output shown in Table 9-5:

SELECT session_id, text
FROM sys.dm_exec_connections
  CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE session_id IN(53, 54);

BEGIN TRAN
    UPDATE dbo.T1 SET col2 = 'BB' WHERE keycol = 2;

SELECT keycol, col1, col2 FROM dbo.T1;

In SQL Server 2000, you use the DBCC INPUTBUFFER command or the fn_get_sql function to get the code buffer of a session.

There are other dynamic management objects that give you valuable concurrency-related information, as well as information about other aspects of the product. Please refer to Books Online for details.

Back to our blocking scenario–remember that by default a blocked session will wait for the blocking session to relinquish the resource, with no time limit. If you want to set a limit for waiting, use the LOCK_TIMEOUT session setting, specifying a value in milliseconds. If the timeout value you specified in LOCK_TIMEOUT expires and your session did not get the desired lock, SQL Server will generate error 1222.

Note

When a lock timeout expires and SQL Server generates the error 1222, you know what the cause of the error was. SQL Server terminates the activity, and it tells you why. On the other hand, if you set a client timeout value (for example, command timeout), the client initiates the termination of the activity, and it doesn’t care why the activity did not finish in the allotted time. In such a case, you can’t tell what the reason for the error was. It might be blocking, it might just be a slow-running query, or it could be something else.

As an example of setting a lock timeout, first cancel the executing query in connection 2, which should still be waiting. The transaction in connection 1 is still open, holding an exclusive lock on a row in T1. Then run the following code in connection 2, which sets the lock timeout value to 5 seconds and then queries T1:

SET LOCK_TIMEOUT 5000;
SELECT keycol, col1, col2 FROM dbo.T1;

After 5 seconds, you will get the following error:

Msg 1222, Level 16, State 51, Line 2
Lock request time out period exceeded.

To reset the lock timeout setting to its default (no timeout, or wait forever), set the LOCK_TIMEOUT value to –1:

SET LOCK_TIMEOUT -1;

To terminate the transaction in connection 1 without committing the change, issue a rollback:

ROLLBACK TRAN;

If transactions are kept open for a long time, they might be keeping locks and preventing access to the data from other processes. To improve concurrency, you should try to keep your transactions as short as possible.

SQL Server maintains a memory block for each lock. If there is no limit on the number of locks that a single transaction can acquire, SQL Server could potentially suffer from memory starvation. Therefore, when SQL Server deems a transaction as holding too many locks (typically, a total in the area of thousands), it will attempt to escalate the fine-grained locks to a single table lock. Such an attempt will fail if there are conflicting locks on the table, in which case SQL Server will keep attempting to achieve lock escalation after each additional 1,250 acquired locks. Lock escalation attempts to reduce lock overhead, but of course results in reduced concurrency, because SQL Server ends up locking more data than is logically needed.

Tip

When you set the database option to READ_ONLY, SQL Server does not bother to acquire shared locks for SELECT queries. It knows that changes cannot be applied to the database, meaning that SELECT queries can never be in conflict with any other activity. This means that queries produce less overhead and you get better performance. In systems where you only read data from the database—for example, data warehouses—consider setting the database update option to READ_ONLY. You can set the database to READ_WRITE just before invoking the extract, transform, and load (ETL) process that periodically loads changes to the data warehouse. When the ETL process finishes, set the database mode back to READ_ONLY. You can automate the changes to the database option as part of the ETL process.