Concurrency can lead to several negative effects, such as the reading of nonexistent data or loss of modified data. Consider this real-world example illustrating one of these negative effects, called dirty read: User U in the personnel department gets notice of an address change for the employee Jim Smith. U1 makes the address change, but when viewing the bank account information of Mr. Smith in the consecutive dialog step, he realizes that he modified the address of the wrong person. (The enterprise employs two persons with the name Jim Smith.) Fortunately, the application allows the user to cancel this change by clicking a button. U: clicks the button, knowing that he has committed no error.
At the same time, user U2 in the technical department retrieves the data of the latter Mr. Smith to send the newest technical document to his home, because the employee seldom comes to the office. As the employee’s address was wrongly changed just before U2 retrieved the address, U2 prints out the wrong address label and sends the document to the wrong person.
To prevent problems like these in the pessimistic concurrency model, every DBMS must have mechanisms that control the access of data by all users at the same time. The Database Engine, like all relational DBMSs, uses locks to guarantee the consistency of the database in case of multiuser access. Each application program requires locks for the data it needs, guaranteeing that no other program can modify the same data. When another application program requests the modification of the locked data, the system either stops the program with an error or makes a program wait.
Locking has several different aspects:
- Lock duration
- Lock modes
- Lock granularity
Lock duration specifies a time period during which a resource holds the particular lock. Duration of a lock depends on, among other things, the mode of the lock and the choice of the isolation level.
The next two sections describe lock modes and lock granularity.
NOTE The following discussion concerns the pessimistic concurrency model. The optimistic concurrency model is handled using row versioning, and will be explained at the end of this chapter.
1. Lock Modes
Lock modes specify different kinds of locks. The choice of which lock mode to apply depends on the resource that needs to be locked. The following three lock types are used for row- and page-level locking:
- Shared (S)
- Exclusive (X)
- Update (U)
A shared lock reserves a resource (page or row) for reading only. Other processes cannot modify the locked resource while the lock remains. On the other hand, several processes can hold a shared lock for a resource at the same time—that is, several processes can read the resource locked with the shared lock.
An exclusive lock reserves a page or row for the exclusive use of a single transaction. It is used for DML statements (INSERT, UPDATE, and DELETE) that modify the resource. An exclusive lock cannot be set if some other process holds a shared or exclusive lock on the resource—that is, there can be only one exclusive lock for a resource. Once an exclusive lock is set for the page (or row), no other lock can be placed on the same resource.
NOTE The database system automatically chooses the appropriate lock mode according to the operation type (read or write).
An update lock can be placed only if no other update or exclusive lock exists. On the other hand, it can be placed on objects that already have shared locks. (In this case, the update lock acquires another shared lock on the same object.) If a transaction that modifies the object is committed, the update lock is changed to an exclusive lock if there are no other locks on the object. There can be only one update lock for an object.
NOTE Update locks prevent certain common types of deadlocks. (Deadlocks are described at the end of this section.)
Table 13-1 shows the compatibility matrix for shared, exclusive, and update locks. The matrix is interpreted as follows: Suppose transaction T1 holds a lock as specified in the first column of the matrix, and suppose some other transaction, T2, requests a lock as specified in the corresponding column heading. In this case, “yes” indicates that a lock of T2 is possible, whereas “no” indicates a conflict with the existing lock.
NOTE The Database Engine also supports other lock forms, such as latches and spinlocks. The description of these lock forms can be found in Microsoft Docs.
At the table level, there are five different types of locks:
- Shared (S)
- Exclusive (X)
- Intent shared (IS)
- Intent exclusive (IX)
- Shared with intent exclusive (SIX)
Shared and exclusive locks correspond to the row-level (or page-level) locks with the same names. Generally, an intent lock shows an intention to lock the next-lower resource in the hierarchy of the database objects. Therefore, intent locks are placed at a level in the object hierarchy above that which the process intends to lock. This is an efficient way to tell whether such locks will be possible, and it prevents other processes from locking the higher level before the desired locks can be attained.
Table 13-2 shows the compatibility matrix for all kinds of table locks. The matrix is interpreted exactly as the matrix in Table 13-1.
2. Lock Granularity
Lock granularity specifies which resource is locked by a single lock attempt. The Database Engine can lock the following resources:
- Row
- Page
- Index key or range of index keys
- Table
- Extent
- Database itself
NOTE The system automatically chooses the appropriate lock granularity.
A row is the smallest resource that can be locked. The support of row-level locking includes both data rows and index entries. Row-level locking means that only the row that is accessed by an application will be locked. Hence, all other rows that belong to the same page are free and can be used by other applications. The Database Engine can also lock the page on which the row that has to be locked is stored.
NOTE For clustered tables, the data pages are stored at the leaf level of the (clustered) index structure and are therefore locked with index key locks instead of row locks.
Locking is also done on disk units, called extents, that are 64K in size (see Chapter 15 for further discussion of extents). Extent locks are set automatically when a table (or index) grows and the additional disk space is needed.
Lock granularity affects concurrency. In general, the more granular the lock, the more concurrency is reduced. This means that row-level locking maximizes concurrency because it leaves all but one row on the page unlocked. On the other hand, system overhead is increased because each locked row requires one lock. Page-level locking (and table-level locking) restricts the availability of data but decreases the system overhead.
3. Lock Escalation
If many locks of the same granularity are held during a transaction, the Database Engine automatically upgrades these locks into a table lock. This process of converting many page-, row-, or index-level locks into one table lock is called lock escalation. The escalation threshold is the boundary at which the database system applies the lock escalation. Escalation thresholds are determined dynamically by the system and require no configuration. (Currently, the threshold boundary is 5000 locks.)
The general problem with lock escalation is that the Database Engine decides when to escalate a particular lock, and this decision might be suboptimal for applications with different requirements. You can use the ALTER TABLE statement to change the lock escalation mechanism. This statement supports the TABLE option with the following syntax:
SET ( LOCK_ESCALATION = { TABLE | AUTO | DISABLE } )
The TABLE option is the default value and specifies that lock escalation will be done at table-level granularity. The AUTO option allows the Database Engine to select the lock escalation granularity that is appropriate for the table schema. Finally, the DISABLE option allows you to disable lock escalation in most cases. (There are some cases in which the Database Engine must take a table lock to protect data integrity.)
Example 13.6 disables the lock escalation for the employee table.
Example 13.6
USE sample;
ALTER TABLE employee SET (LOCK_ESCALATION = DISABLE);
4. 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.
4.1. 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 in the “Setting and Editing Isolation Levels” section 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.
NOTE All these options can be combined in any order if the combination makes sense. (For example, the combination of TABLOCK and PAGLOCK does not make sense, because both options are applied to different resources.)
4.2. 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.)
4.3. 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.7 displays all the locks that are in a wait state.
Example 13.7
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’;
4.4. 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.8 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.8 uses the WAITFOR statement to pause both transactions for ten seconds to simulate the deadlock.
Example 13.8
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’
COMMIT
If both transactions in Example 13.8 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.
As the output of Example 13.8 shows, the Database Engine 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 “victim” session is chosen according to the session’s deadlock priority.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.