concurrency control and deadlocks

Concurrency control and locking:

Concurrency control is essential for the correctness of transactions executed concurrently in a DBMS, which is the common execution mode for performance reasons. The main concern and goal of concurrency control is isolation.

Isolation refers to the ability of one transaction to see the results of other transactions. Greater isolation typically reduces performance and/or concurrency, leading DBMSs to provide administrative options to reduce isolation. For example, in a database that analyzes trends rather than looking at low-level detail, increased performance might justify allowing readers to see uncommitted changes ("dirty reads".)

A common way to achieve isolation is by locking. When a transaction modifies a resource, the DBMS stops other transactions from also modifying it, typically by locking it. Locks also provide one method of ensuring that data does not change while a transaction is reading it or even that it doesn't change until a transaction that once read it has completed.

Lock types:

Locks can be shared or exclusive, and can lock out readers and/or writers. Locks can be created implicitly by the DBMS when a transaction performs an operation, or explicitly at the transaction's request.

Shared locks allow multiple transactions to lock the same resource. The lock persists until all such transactions complete. Exclusive locks are held by a single transaction and prevent other transactions from locking the same resource.

Read locks are usually shared, and prevent other transactions from modifying the resource. Write locks are exclusive, and prevent other transactions from modifying the resource. On some systems, write locks also prevent other transactions from reading the resource.

The DBMS implicitly locks data when it is updated, and may also do so when it is read. Transactions explicitly lock data to ensure that they can complete without complications. Explicit locks may be useful for some administrative tasks.

Locking can significantly affect database performance, especially with large and complex transactions in highly concurrent environments.

Lock granularity:

Locks can be coarse, covering an entire database, fine-grained, covering a single data item, or intermediate covering a collection of data such as all the rows in a RDBMS table.

Deadlocks:

Deadlocks occur when two transactions each require data that the other has already locked exclusively. Deadlock detection is performed by the DBMS, which then aborts one of the transactions and allows the other to complete.