12. Transactions

Transactions in SQL

A transaction is a group of one or more SQL statements that are logically related and executed as a single unit of work. Transactions ensure the consistency and reliability of data, even in the presence of concurrent operations or unexpected system failures.

ACID Properties

Transactions in relational databases typically adhere to the ACID properties:

Atomicity

• All or Nothing: Either the entire transaction completes successfully (commit) or none of it takes effect (abort/rollback).

• If a transaction fails at any point, all changes are undone, leaving the database unchanged by that transaction.

Consistency

• Referential and Integrity Constraints: The database must remain consistent before and after the transaction.

• If the initial database state is valid, and the transaction is designed to follow integrity rules, the final state will also be valid.

Isolation

• Concurrent Transactions: Multiple transactions should not interfere with one another. Each transaction behaves as if it is the only one running.

• Uncommitted changes made in one transaction are typically invisible to other transactions.

Durability

• Permanent Changes: Once a transaction commits, its changes are stored in non-volatile memory (e.g., disk). Even if a system failure occurs, the committed changes remain intact.

Example: Bank Transfer

Consider transferring 100 from Account X to Account Y:

1. Debit X by 100 $(X = X - 100)$.

2. Credit Y by 100 $(Y = Y + 100)$.

• Atomicity ensures both operations succeed or both are rolled back.

• Consistency ensures the total amount remains the same before and after the transaction.

• Isolation prevents other concurrent transfers from causing inconsistent sums.

• Durability ensures that once committed, the transfer is permanent even if the system crashes afterward.

Who Ensures the ACID Properties?

• Atomicity: Handled by the Transaction Manager (handles commit or rollback).

• Consistency: Primarily the responsibility of the application programmer (correct logic, constraints).

• Isolation: Managed by the Concurrency Control Manager (locking, multi-version concurrency control, etc.).

• Durability: Provided by the Recovery Manager (writes changes to disk, maintains logs for recovery).

Backup and Restore

Databases typically offer backup and restore capabilities to protect data. Regular backups can be used to restore the database to a known consistent state if corruption or data loss occurs.

Security

Securing the database involves controlling user permissions, encrypting sensitive data, and safeguarding against unauthorized access. Proper security measures ensure that transactions are valid and conducted by authorized entities only.

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version