Concurrency - Lock

> (Data|State) Management and Processing > (Concurrency | Parallel | Asynchronous) programming

1 - About

Lock is a mechanism to enforce data consistency in a multi-user database environment with then multiple concurrent transactions.

Locks are simply names used by convention within the system to represent either physical items (e.g., disk pages) or logical items (e.g., tuples, files, volumes) that the application (database for instance) manages.

In general, multiuser databases use some form of data locking to solve the problems associated with:

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.

This helps maintain the integrity of the data by ensuring that only one process at a time can modify the same data.

Such locks can be applied on:

  • a row level,
  • a page (a basic data block),
  • a extent (multiple array of pages)
  • or even an entire table.

In basic file system files or folders, only one lock at a time can be set, restricting the usage to one process only. Databases, on the other hand, can set and hold multiple locks at the same time on the different levels of the physical data structure.

The number, nature of locks and time the lock holds a data block can have a huge impact on the database performances. Bad locking can lead to disastrous performance response (usually the result of poor SQL requests, or inadequate database physical structure)


3 - Manager

The lock manager provides a place to register and check for the lock.

Every lock is associated with a transaction and each transaction has a unique transaction ID.

The lock manager makes use of a deadlock detector that periodically examines the lock table to detect waits-for cycles (a cycle of workers where each is waiting for the next and a cycle is formed). Upon detection of a deadlock, the deadlock detector aborts one of the deadlocked transactions. A more detailed description of a lock manager implementation is given in Granularity of locks in a shared data base (Gray and Reuter).

4 - Lock Mode (Shared and exclusive locks)

Locks come in different lock modes and these modes are associated with a lock-mode compatibility table. In most systems, this logic is based on the well-known lock modes that are introduced in Granularity of locks in a shared data base (Gray and Reuter)

Most DBMS systems use :

  • shared
  • and exclusive


4.1 - Exclusive

Exclusive locks mean that no other lock can acquire the current data object as long as the exclusive lock lasts. DBMSs usually set exclusive locks when the database needs to change data, as during an UPDATE or DELETE operation.

4.2 - Shared

Shared locks can take ownership one from the other of the current data structure. Shared locks are usually used while the database is reading data (during a SELECT operation).

4.3 - Others Reasons

Databases can also be locked for other reasons, like access restrictions for given levels of user. Some DBAs also lock databases for routine maintenance, which prevents changes being made during the maintenance.

5 - Isolation level

The isolation level of the data server enforces default locking behaviour. Changing the isolation level will affect how shared or exclusive locks must be set on the data for the entire database system. Default isolation is generally 1, where data can not be read while it is modified, forbidding the return of “ghost data” to end users.

6 - Deadlock

At some point intensive or inappropriate exclusive locking can lead to a deadlock situation between two locks, where none of the locks can be released because they try to acquire resources mutually from each other.

7 - Documentation / Reference