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.

Explore the fundamental concepts of database transactions, including their properties defined by the ACID principles. This quiz covers transaction states, database requests, and the importance of transaction logs in ensuring consistent database management. Test your knowledge on how these elements interact to maintain data integrity.