Transaction Management and Concurrency Control

A logical unit of work that must be either entirely completed or aborted. Successful transaction changes the database from one consistent state to another. It must satisfy all data integrity constraints.

A simple SELECT statement to generate a list of table contents, A series of related UPDATE statements to change the values of attributes in various tables, A series of INSERT statements to add rows to one or more tables, A combination of SELECT, UPDATE, and INSERT statements.

Improper or incomplete transactions can have a devastating effect on database integrity. Some DBMSs provide means by which users can define enforceable constraints based on business rules. Other integrity rules are enforced automatically by the DBMS when table structures are properly defined.

Requires that all operations (SQL requests) of a transaction be completed. If one fails, none are applied.

Indicates permanence of the database's consistent state. Once a transaction is committed, the changes are permanent.

Ensures that the concurrent execution of several transactions yields consistent results, even when transactions run concurrently.

Data used during execution of a transaction cannot be used by a second transaction until the first one is completed. Ensures isolation of transactions.

ANSI has defined standards that govern SQL database transactions. Transaction support is provided by two SQL statements: COMMIT and ROLLBACK.

A sequence of database operations that must be performed to ensure data consistency. Every transaction must start, process, and end successfully without any interruption.

A log file that records every change and operation performed during a database transaction. It stores details like the type of operation, affected objects, and before/after values.

The mechanism by which a database system manages and coordinates multiple transactions running simultaneously. It aims to ensure data integrity and consistency.

A situation where a transaction's updates overwrite another transaction's changes, leading to a loss of data.

A situation where one transaction reads data that another transaction has modified but not yet committed, potentially leading to incorrect results.

A situation where a transaction reads data that is inconsistent because another transaction has modified it partially, leading to confusing or erroneous results.

A database program that schedules database operations, ensuring serializability and isolation of transactions. It orchestrates the order in which different transactions can run.

The property of a database system that ensures transactions are executed in a specific order, even when they happen concurrently. This prevents data inconsistencies.

A mechanism to ensure a transaction has exclusive access to a data item, preventing other transactions from accessing or modifying it.

Responsible for managing locks, assigning locks to transactions, and enforcing lock rules.

The level at which locks are applied. Can be database, table, page, row, or field.

Locks the entire database, preventing any access to any data within it.

Locks the entire table, preventing any access to any rows within it.

Locks a specific disk page. While it can affect multiple rows, it's a finer-grained lock than a table-level lock.

Locks a single row, allowing concurrent transactions to access different rows of the same table.

Locks a single field (attribute) within a row, enabling concurrent transactions to access other fields within the same row.

A locking protocol that prevents dirty reads and premature writes, ensuring data integrity during transactions.

A locking scheme where multiple transactions can read an object concurrently, but only one transaction can write to it at a time.

Locks that allow multiple transactions to read the same data simultaneously.

Locks that grant exclusive access to an object for writing, preventing other transactions from reading or writing it.

A rule that prevents a transaction from writing to an object if another transaction has already read it, ensuring data consistency.

A rule that prevents a transaction from reading or writing an object if another transaction has already modified it, ensuring data accuracy.

Converting a read lock into a write lock, allowing a transaction to modify data it was previously only reading.

Shared locks cannot be promoted into write locks because this could lead to data inconsistency and conflicting access.

A lock acquired for an object during a transaction is released only after the entire transaction commits or aborts.

If a transaction wants to access an object already locked by another transaction, the requesting transaction must wait until the lock is released.

A situation where two or more transactions are waiting for each other to release locks they hold, resulting in a standstill.

Deadlocks occur when one transaction wants an exclusive lock on a data item that is already held by another transaction.

A transaction is granted all read or write locks needed at the start, preventing future wait states.

A deadlock condition is detected and then resolved, typically by aborting one of the affected transactions.

Transactions are scheduled in a way that avoids the possibility of deadlock. For example, a pre-defined order of access for data resources might be enforced.

A unique identifier assigned to each transaction, ensuring a clear order of submission to the database management system (DBMS).

A method for concurrency control in databases where an older transaction waits for a younger transaction to complete, while the younger transaction is rolled back and rescheduled if it conflicts with the older one.

A method for concurrency control in databases where an older transaction rolls back and reschedules a younger transaction if it conflicts with it.

The process of restoring a database to a consistent state from a potentially inconsistent state, often due to transaction failures.

A technique where transaction updates are not applied directly to the database until the transaction commits, relying on the transaction log for tracking changes.

A technique where transaction updates are immediately written to the database, ensuring that changes are persistent even in case of failures.