Database Transactions Overview

Database Transactions

• Transaction is a logical unit of work made up of a collection of operations.
• Database Request is a single SQL statement within a transaction.
• Database Consistent State adheres to the constraints defined in the database schema.
• Transaction Log is a record kept by the DBMS of all transactions that modify the database.

Transaction Properties (ACID)

• Atomicity ensures all operations within a transaction complete successfully, or none are applied.
• Consistency guarantees only valid data, adhering to all rules and constraints, is written to the database.
• Isolation ensures data accessed by a transaction is not available to other transactions, until the first completes.
• Durability guarantees that once a transaction commits, its changes are permanent and persist despite system failures.

Transaction States

• Active State where a transaction performs READ and WRITE operations.
• Partially committed state when the final query statement has executed but not acknowledged.
• Committed state when all operations complete successfully.
• Failed state when an operation within a transaction cannot be completed.
• Terminated state when the transaction leaves the system, successfully committed or aborted.

Concurrency Control

• Concurrency Control manages simultaneous transactions to preserve data consistency and integrity.
• Lost Update occurs when two transactions modify the same data, and one update overwrites the other.
• Uncommitted Data occurs when a transaction reads data from another transaction that is not yet committed, and later rolls back.
• Inconsistent Retrievals occur when a transaction reads data before and after another transaction modifies the same data, resulting in inconsistent data view.

Locking

• Lock restricts access to data to a single transaction.
• Database-Level Lock locks the entire database.
• Table-Level Lock locks the whole table.
• Row-Level Lock locks a specific row.
• Binary Lock has two states: locked (1) and unlocked (0).
• Exclusive Lock reserves access to the locked object exclusively for the transaction that acquired it.
• Shared Lock allows multiple transactions to read data from the object, but only one to write.

Deadlock

• Deadlock occurs when two transactions wait indefinitely for each other to release a lock.

Deadlock Handling

• Deadlock Prevention actively prevents deadlocks by aborting transactions with potential deadlock issues.
• Deadlock Detection periodically checks for deadlocks and resolves them.
• Deadlock Avoidance requires transactions to obtain all necessary locks before execution.

Transaction Isolation Levels

• Dirty Read allows a transaction to read data that is not yet committed by another transaction.

• Non-repeatable Read allows a transaction to read a row multiple times, with different results, due to updates or deletions by other transactions.

• Phantom Read allows a transaction to repeat a query and retrieve additional rows because another transaction has inserted new rows in the meantime.

• READ UNCOMMITTED is the least restrictive isolation level.

• Ignores locks placed by other transactions.

• Can read uncommitted, "dirty" data.

• READ COMMITTED is the default isolation level for SQL Server.

• Prevents dirty reads by disallowing reading data modified but not yet committed by other transactions.

• Allows other transactions to modify, insert, or delete data between statements within the current transaction, leading to non-repeatable reads or phantom data.

• REPEATABLE READ is more restrictive than READ COMMITTED.

• Encompasses READ COMMITTED behavior.

• Prevents other transactions from modifying or deleting data that has been read by the current transaction until it commits.

• Does not prevent phantom reads because new rows can be inserted by other transactions.

• SERIALIZABLE ISOLATION is the most restrictive level.

• Prevents any modifications or deletions of data read by a transaction, ensuring data consistency.

• Provides a solution to the phantom read problem.