Concurrency Control

Concurrency control

A DBMS feature that coordinates simultaneous execution of transactions in a multi-user system while preserving data integrity.

Which ACID property does concurrency control mostly preserve

Isolation.

The three concurrency control problems

Lost update, uncommitted data (dirty read), inconsistent retrieval.

Lost update

A concurrency problem in which a data update is overwritten and lost during concurrent execution of transactions.

Uncommitted data (dirty read)

A concurrency problem in which a transaction reads data written by another transaction that later rolls back.

Inconsistent retrieval

A concurrency problem in which a transaction uses an aggregate function on data while other transactions are updating that data, producing incorrect aggregate results.

Scheduler

A DBMS component that establishes the order in which concurrent transaction operations are executed, interleaving them to ensure serializability.

Serializable schedule

A schedule of operations whose interleaved execution yields the same result as some serial execution.

Lock

A device that guarantees unique use of a data item for a particular transaction operation.

Pessimistic locking

Use of locks based on the assumption that conflicts between transactions will occur.

Lock manager

A DBMS component responsible for assigning and releasing locks.

Lock granularity

The level at which locks are applied: database, table, page, row, or field (broadest to most fine-grained).

Database-level lock

A lock that restricts database access to the lock owner; only one user at a time can use the database.

Table-level lock

A lock that allows only one transaction at a time to access a given table.

Page-level lock

A lock that restricts access to a disk page (a section of disk).

Row-level lock

A lock that allows concurrent transactions to access different rows of the same table, even if those rows live on the same page.

Field-level lock

A lock that allows concurrent transactions to access the same row but different fields; most flexible, highest overhead.

Trade-off as lock granularity gets finer

More concurrency, but higher overhead cost.

Binary lock

A lock with only two states: locked and unlocked.

Exclusive lock

A lock issued when a transaction requests permission to update a data item and no other locks are held on it.

Shared lock

A lock issued when a transaction requests permission to read a data item and no exclusive lock is held on it by another transaction.

Deadlock

A condition in which two or more transactions wait indefinitely for each other to release locks (also called a deadly embrace).

Deadlock prevention

A transaction requesting a lock is aborted if there is any chance of deadlock.

Deadlock detection

The DBMS periodically tests the database for deadlocks; if found, one transaction is aborted.

Deadlock avoidance

Transactions must obtain every lock they will need before being allowed to execute.