Transactions
Database Transactions
A database transaction is a unit of work performed within a database management system (DBMS) against a database, and it is often composed of one or more SQL statements. The primary purpose of a transaction is to provide a reliable way to ensure data consistency and integrity.
Transactions are crucial in scenarios where multiple users or processes may concurrently access and modify the database to prevent data corruption and ensure reliability
Transaction Process
Database Transaction Process
Begin Transaction
A transaction begins with the “BEGIN TRANSACTION” statement. This indicates the start of a unit of work.
Execute SQL Statements
SQL statements (e.g., INSERT, UPDATE, DELETE) are executed within the transaction to make changes to the database.
Commit or Rollback
After executing the SQL statements, the transaction can be either committed (if the changes are to be made permanent) or rolled back (if the changes need to be undone). The “COMMIT” statement is used to commit the transaction, and the “ROLLBACK” statement is used to undo the changes and discard the transaction.
ACID
ACID Approach
The ACID approach is a set of properties that guarantee the reliability of database transactions. ACID properties are crucial for ensuring the reliability and integrity of database transactions, especially in scenarios where multiple transactions may be occurring concurrently or in the presence of system failures.
ACID stands for Atomicity, Consistency, Isolation, and Durability.
Atomicity
Atomicity
Atomicity ensures that a transaction is treated as a single, indivisible unit of work.
Either all the changes made by the transaction are committed to the database, or none of them are.
If any part of the transaction fails, the entire transaction is rolled back, and the database is left unchanged.
Consistency
Consistency
Consistency ensures that a transaction brings the database from one valid state to another.
The database should satisfy certain integrity constraints before and after the transaction.
If a transaction violates any integrity constraints, it is rolled back, maintaining the consistency of the database.
Isolation
Isolation
Isolation ensures that the execution of one transaction is isolated from the execution of other transactions.
Even though multiple transactions may be executing concurrently, the final outcome should be as if they were executed one after the other.
Isolation prevents interference between transactions, maintaining data integrity.
Durability
Durability
Durability guarantees that once a transaction is committed, its effects are permanent, even in the face of system failures.
The changes made by a committed transaction persist, and the database can recover to a consistent state after a system failure.
Durability is typically achieved through mechanisms like transaction logs and database backups.