Lock Definition

Locking is the process by which the DBMS restricts access to rows in a multi-user environment. Locks rows or columns exclusively, denying other users access to the locked data until the lock is released. This ensures that two users cannot update the same column in a row at the same time.

Locks can be high from a resource perspective and data integrity should be maintained only when needed. Hundreds or thousands of users in a database may try to access records per second — if the database is connected to the Internet, unnecessary locking can quickly cause performance degradation in the application.

Setting the property recordset before LockType opens specifies what type of lock should be used for the provider when it is opened. The recordset object is read from an open type property used in the lock to be returned.

The provider may not support all lock types. If the provider cannot support the request LockType setting, it will replace it with another type of lock. To determine which actual locking function recordset objects are available, replace adUpdate and adUpdateBatch with the support method.

The adlockglobalization does not support setting the CursorLocation property to adUseClient. If an unsupported value is set, no error is generated. The nearest supported LockType will be used instead.

The LockType property is closed when the recordset is open for read/write and is read-only.

Lock resources

The value that identifies the locked resource. The format of the value depends on the identified resource type type column: Type value: resource value.

RID: The RID identifier is in the format fileID: Pagenumber: The RID identifier, where fileID identifies the file containing pages, pagenumber identifies the page containing rows, and RID identifies the specific row on the page. Fileid matches the column sys.database_files in file_ID directory view.

KEY: hexadecimal number used internally by the database engine.

PAG: a number in the format of FileID: Pagenumber, where fileID identifies the file containing the page and pagenumber identifies the page.

EXT: number of the first page in the identification area. The number is in the format of FileID: Pagenumber.

TAB: No information is provided because ObjId columns are identified in the table.

DB: No information is provided because of the identified database DBID column.

FIL: file matching identifier file_id column sys.database_files directory view.

APP: Unique identifier of the locked application resource. Format: DbPrincipleId:< first two of a 16-character resource string >< hash value >.

MD: Varies with the resource type. For more information, see the column sys.dM_TRAN_LOCKS (transact-SQL) in the description resource_DESCRIPTION.

HBT: No information was provided. Change to dynamic management view using sys.dM_tran_LOCKS.

AU: No information was provided. Change to dynamic management view using sys.dM_tran_LOCKS.

Lock Type (Lock granularity) (Type)

The granularity of a lock is the granularity of the resource where the lock resides.

RID: Lock on a single row in a table, identified by the row identifier (RID).

KEY: Lock within an index that protects a series of keys in a serializable transaction.

PAG: Locks on data pages or index pages.

EXT: Lock on an area.

TAB: Locks the entire table (including all data and indexes).

DB: locks the database.

FIL: Database file lock.

APP: Locks the specified application resource.

MD: lock for metadata or directory information.

HBT: Lock for heap or B-tree index. This information is incomplete in SQL Server.

AU: Lock of the allocation unit. This information is incomplete in SQL Server.

The lock Mode (Mode)

When SQLServer requests a lock, it selects a mode that affects the lock. The mode of the lock determines the level of compatibility with any other lock. If a query finds that the lock on the requested resource is compatible with its own lock, the query can proceed, but if it is incompatible, the query is blocked. Until the lock is released on the requested resource.

NULL: does not grant access to resources. Used as a placeholder.

Sch-s: Architecture stability. Ensure that schema elements (such as tables or indexes) are not dropped while any session holds a schema stability lock on that schema element.

Sch-m: Architecture modification. Must be held by any session that is changing the schema of the specified resource. Ensure that no other session is referring to the indicated object.

S: Share. Grant the session holding the lock shared access to the resource.

U: Update. Indicates the update lock acquired on a resource that may eventually be updated. Used to prevent a common deadlock that occurs when multiple sessions lock a resource for later update.

X: Exclusive. Grant exclusive access to resources to the session that holds the lock.

IS: intention sharing. Indicates that the S lock is intentionally placed on a dependent resource in the lock hierarchy.

IU: Intention update. Indicates that the U lock is intentionally placed on a subordinate resource in the lock hierarchy.

IX: Intentional exclusion. Indicates that the X lock is intentionally placed on a subordinate resource in the lock hierarchy.

SIU: Shared intention update. Indicates shared access to resources that intend to acquire updated locks on slave resources in the lock hierarchy.

SIX: Shared intention exclusive. Indicates shared access to resources that intend to acquire exclusive locks on slave resources in a lock hierarchy.

UIX: Updated intention exclusion. Indicates the update lock held on a resource that intends to acquire an exclusive lock on a slave resource in a lock hierarchy.

BU: Large-capacity update. Used for large capacity operations.

RangeS_S: Shared key range and shared resource lock. Indicates serializable range scanning.

RangeS_U: Share key ranges and update resource locks. Indicates a serializable update scan.

RangeI_N: Insert key range and Null resource lock. Used to test the scope before inserting the new key into the index.

RangeI_S: key range conversion lock. Created by the overlap of RangeI_N and S locks.

RangeI_U: Key range transition lock created by overlapping RangeI_N and U locks.

RangeI_X: Key range transition lock created by overlapping RangeI_N and X locks.

RangeX_S: Key range transition lock lock created by the overlap of RangeI_N and RangeS_S locks.

RangeX_U: Key range transition lock created by the overlap of RangeI_N and RangeS_U locks.

RangeX_X: Exclusive key range and exclusive resource lock. This is the transition lock used when updating keys in a scope.

Lock request Status

CNVRT: The lock is being converted from another mode, but the transformation is blocked by another process holding the lock (with conflicting modes). GRANT: Lock obtained. WAIT: The lock is blocked by another process holding the lock (with conflicting modes).

Lock escalation (Upgrade)

Lock upgrade is the process of converting many fine-grained locks into fewer coarse-grained locks, which reduces system overhead but increases the likelihood of concurrent contention.

When the SQL Server database engine acquires a lower-level lock, it also places an intent lock on an object that contains lower-level objects:

When a row or index key range is locked, the database engine places an intent lock on the page containing those rows or keys. When pages are locked, the database engine places intent locks on higher-level objects that contain those pages. In addition to intent locks on objects, intent page locks are required on the following objects: leaf pages for non-clustered indexes, data pages for clustered indexes, and heap data pages.

Lock upgrade threshold:

A single Transact-SQL statement acquires at least 5,000 locks on a single non-partitioned table or index. A single Transact-SQL statement obtains at least 5,000 locks on a single partition of a partitioned TABLE, with the ALTER TABLE SET LOCK_ESCALATION option SET to AUTO. The number of locks in the database engine instance exceeded memory or configuration thresholds.

Lock Application Scenario

Shared locks are used for all read-only data operations. A shared lock is non-exclusive and allows multiple concurrent transactions to read its locked resource.

Modify locks are used to lock resources that may be modified during the initialization phase of a modify operation to avoid deadlocks caused by shared locks.

Exclusive locks are reserved for modifying data. It locks resources that cannot be read or modified by other transactions.

Structural locks are divided into structural modification locks (SCH-M) and structural stability locks (SCH-S). SQL Server uses a SCH-M lock when performing table definition language operations and a SCH-S lock when compiling queries.

Intent lock indicates that SQLServer intends to acquire a shared or exclusive lock at a lower level of the resource.

Batch change lock is used for batch data replication.

Concurrency in the data source and ADO cursor library is managed by selecting the appropriate locking option.

Lock advantages (Advantage)

Using locks to solve data integrity and consistency problems:

Dirty read: A dirty read occurs when a transaction reads a record that is an unfinished part of another transaction.

No-repeatable Read: When a record is Read twice in a transaction and a separate transaction modifies the data between reads, this results in an unrepeatable Read.

Phantom: In the same transaction, the same operation is read twice, resulting in a different number of records.

Lost updates: Lost updates occur when an update is successfully written to the database, but is accidentally overwritten by another transaction.

Lock faults (Disadvantage)

Multiple agents competing for resources are prone to deadlock.

Resources are locked for a long time due to improper lock usage, and other operations cannot be performed.