Affecting Locks, Displaying Lock Information, Deadlock

Affecting Locks

You can use either locking hints or the LOCK_TIMEOUT option of the SET statement to affect locks. The following subsections describe these features.

Locking Hints

Locking hints specify the type of locking used by the Database Engine to lock table data. Table-level locking hints can be used when finer control of the types of locks acquired on a resource is required. (Locking hints override the current transaction isolation level for the session.)

All locking hints are written as a part of the FROM clause in the SELECT statement.

You can use the following locking hints:

• UPDLOCK Places update locks for each row of the table during the read operation. All update locks are held until the end of the transaction.
• TABLOCK (TABLOCKX) Places a shared (or exclusive) table lock on the table. All locks are held until the end of the transaction.
• ROWLOCK Replaces the existing shared table lock with shared row locks for each qualifying row of the table.
• PAGLOCK Replaces a shared table lock with shared page locks for each page containing qualifying rows.
• NOLOCK Synonym for READUNCOMMITTED (see the description of isolation-level hints later in this chapter).
• HOLDLOCK Synonym for REPEATABLEREAD (see the description of isolation-level hints later in this chapter).
• XLOCK Specifies that exclusive locks are to be taken and held until the transaction completes. If XLOCK is specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.
• READPAST Specifies that the Database Engine does not read rows that are locked by other transactions.

LOCK_TIMEOUT Option

If you don’t want your process to wait without any time limitations, you can use the LOCK_TIMEOUT option of the SET statement. This option specifies the number of milliseconds a transaction will wait for a lock to be released. For instance, if you want your processes to wait eight seconds, you write the following statement:

SET LOCK_TIMEOUT 8000

If the particular resource cannot be granted to your process within this time period, the statement will be aborted with the corresponding error message.

The value of –1 (the default value) indicates no time-out; in other words, the transaction won’t wait at all. (The READPAST locking hint provides an alternative to the LOCK_TIMEOUT option.)

Displaying Lock Information

The most important utility to display lock information is a dynamic management view called sys.dm_tran_locks. This view returns information about currently active lock manager resources. Each row represents a currently active request for a lock that has been granted or is waiting to be granted. The columns of this view relate to two groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request. The most important columns of this view are as follows:

• resource_type Represents the resource type
• resource_database_id Specifies the ID of the database under which this resource is scoped
• request_mode Specifies the mode of the request
• request_status Specifies the current status of the request

Example 13.4 displays all the locks that are in a wait state.

USE AdventureWorks;
SELECT resource_type, DB_NAME(resource_database_id) as db_name,
	request_session_id, request_mode, request_status
	FROM sys.dm_tran_locks
	WHERE request_status = 'WAIT;'Code language: PHP (php)

Deadlock

A deadlock is a special concurrency problem in which two transactions block the progress of each other. The first transaction has a lock on some database object that the other transaction wants to access, and vice versa. (In general, several transactions can cause a deadlock by building a circle of dependencies.) Example 13.5 shows the deadlock situation between two transactions.

Note – The parallelism of processes cannot be achieved naturally using the small sample database, because every transaction in it is executed very quickly. Therefore, Example 13.5 uses the WAITFOR statement to pause both transactions for ten seconds to simulate the deadlock.

USE sample;
BEGIN TRANSACTION
UPDATE works_on
	SET job = 'Manager'
	WHERE emp_no = 18316
	AND project_no = 'p2'
WAITFOR DELAY '00:00:10'
UPDATE employee
	SET emp_lname = 'Green'
	WHERE emp_no = 9031
COMMIT

BEGIN TRANSACTION
UPDATE employee
	SET dept_no = 'd2'
	WHERE emp_no = 9031
WAITFOR DELAY '00:00:10'
DELETE FROM works_on
	WHERE emp_no = 18316
	AND project_no = 'p2'
COMMITCode language: PHP (php)

If both transactions in Example 13.5 are executed at the same time, the deadlock appears and the system returns the following output:

Server: Msg 1205, Level 13, State 45
Transaction (Process id 56) was deadlocked with another process and has been chosen as deadlock victim. Rerun your command.Code language: JavaScript (javascript)

As the output of Example 13.5 shows, the database system handles a deadlock by choosing one of the transactions as a “victim” (actually, the one that closed the loop in lock requests) and rolling it back. (The other transaction is executed after that.)

A programmer can handle a deadlock by implementing the conditional statement that tests for the returned error number (1205) and then executes the rolled-back transaction again.

You can affect which transaction the system chooses as the “victim” by using the DEADLOCK_PRIORITY option of the SET statement. There are 21 different priority levels, from –10 to 10. The value LOW corresponds to –5, NORMAL (the default value) corresponds to 0, and HIGH corresponds to 5. The “victim” session is chosen according to the session’s deadlock priority.