In SQL Server, locking helps improve efficiency by preventing resources from being used simultaneously by different transactions. Issues with SQL Server locking can adversely affect the performance of SQL Server databases. In systems that have many users, the mismanagement of transactions can result in performance problems as contention for resources occurs.
What are Transactions?
In database terminology, a transaction is a sequence of operations that are performed as a single unit of work. To be considered a transaction, a unit of work must exhibit four properties known by the acronym ACID.
Atomicity – Transactions are atomic, meaning that either all or none of the intended data modifications are performed.
Consistency – Transactions must leave all data in a consistent state when it completes. All internal data structures need to be correct when the transaction finishes executing.
Isolation – Transactions operate in isolation of other concurrently running transactions. When accessing data in use by another concurrent transaction, the information is recognized either before or after the second transaction completes. Data is not recognized in an intermediate state while in use by another transaction.
Durability – The effects of a durable transaction are permanently reflected in the system. Starting in SQL Server 2014, delayed durable transactions are supported. Delayed durable transactions commit before transaction log records are saved to disk.
The characteristics of transactions are designed to maintain the veracity of the data in a relational database like SQL Server. Without the constraints enforced by the ACID properties, data would quickly become out of sync and transactions would be executed using unreliable information. Many business-critical applications rely on transactions conforming to ACID requirements.
What is Locking?
Locking is the mechanism by which SQL Server manages transaction concurrency. A lock is a 96-byte in-memory structure that has an owner, type, and the hash of the resource it is protecting.
SQL Server locking enforces the isolation requirement of ACID transactions. A lock holds SQL Server resources to ensure it cannot be used concurrently by multiple transactions. When a transaction locks an object, all other transactions need to wait until the lock is released before access to the entity is permitted. Minimizing SQL Server locks increases concurrency and results in improved system performance.
The following resources are among those that can be locked by SQL Server, making them available to a single transaction at a time.
- Application – Locks an application-specific resource.
- Database – Locks a database including all of its objects.
- Extent – Locks a contiguous group of eight pages.
- File – Locks a database file.
- Key – Locks a row in an index.
- Object – A lock on any object that has an entry in sys.all_objects.
- RID – Row ID locks a single row in a heap.
Various lock modes are available in SQL Server which influence how a given resource will be handled by the lock. They provide flexibility in how resources are protected from concurrent use.
Exclusive (X) – As its name implies, an exclusive lock ensures the resource will be reserved exclusively for a transaction until it releases the lock. Exclusive locks are imposed when a transaction is modifying page or row data. Only one exclusive lock can be set against a specific resource at a time.
Shared (S) – Shared locks allow one or more transactions to reserve a page or row for reading data. A shared lock will allow a write operation, but no Data Definition Language (DDL) changes are permitted.
Update (U) – Update locks can be imposed on records that already have a shared lock. When the transaction with the update lock is ready to modify data, the lock is transformed into and behaves as an exclusive lock.
Intent (I) – Intent locks allow a transaction to inform another transaction that it intends to acquire a lock.
Schema (Sch) – Schema modification locks come in two flavors, modification and stability, that ensure objects aren’t accessed while changes are being performed on them.
Bulk update (BU) – Bulk update locks prevent other processes from accessing a table during a bulk import operation.
Locks are a necessary component of SQL Server databases to ensure data consistency. Too many locks can negatively impact performance and need to be identified so changes can be made to minimize locking and enhance the responsiveness of a database.
Finding and Fixing SQL Server Locks
Precise for Databases supports all versions of SQL Server including physical and virtual instances located on-premises or in the cloud. Precise monitors SQL Server, Sybase, Oracle, and Db2 database transactions to help uncover issues that are impacting performance.
Precise monitors database instances and provides the information database administrators (DBAs) need to address performance problems. Precise’s features furnish a database team with information that:
- Shows where locking is occurring;
- Identifies which session is blocking and which are locked;
- Displays the locking tree for the sessions;
- Shows the statements running in the sessions;
- Identifies executing programs;
- Determines which users are impacted by locking.
Armed with this data, DBAs can take the appropriate action to tune database transactions and increase system performance.
An IDERA video is available that discusses how Precise handles SQL Server locks. The presentation demonstrates how Precise can help a database team improve the performance of their SQL Servers, as well as those of its other supported database platforms.