Database Transaction

A database transaction is a logical unit of work that must either be entirely completed or aborted. An example of a database transaction is SQL’s Data Manipulation Language (DML). DBMS’s also allow user-defined database transactions.

Transactions are used for analysis, as they are defined as the atomic unit of work in databases. Furthermore, they are vital when multiple users need to access the same database.

Definition §

• Has only two states, completed or aborted (cannot be partially completed!)
• A successful transaction changes the database from one consistent state to another. It must ensure that any integrity constraints (IC) held true before the transaction also hold after.

Properties (ACID) §

• Atomicity: A transaction is treated as a single, indivisible, logical unit of work. All operations in a transaction must be completed; if not, then the transaction is aborted
• Consistency: Constraints that hold before a transaction must also hold after it.
  • Multiple users accessing the same data see the same value
• Isolated: Changes made during execution of a transaction cannot be seen by other transactions until this one is completed (or committed)
• Durability: When a transaction is complete, the changes made to the database are permanent, even if the system fails

In SQL §

Transactions in SQL use three keywords:

• START TRANSACTION : Defines a transaction. All following SQL statements up to COMMIT are treated as part of the transaction
• COMMIT: Applies or commits the defined transaction, applying it’s changes. If an error occurs during a commit, all statements in the transactions are undone (i.e. does a ROLLBACK)
• ROLLBACK: INSIDE a transaction, is equivalent to an abort, and rolls back. Cannot be done after a transaction is committed!

Concurrent Transactions §

See Concurrent Transactions

Transaction Log §

In most DMBSs, a transaction log is kept so that any failed transactions can be rolled back, and any modified data is ‘reset’ to it’s previous state.

All updates to any data objects are tracked.

The transaction log contains:

• A record for when a transaction begins (without any actual operations)
• For each SQL statement in the transaction:
  • The name of operation being performed
  • All the data objects affected by the operation
  • Before and After values for every field that was updated
  • Pointers to the next & previous entry in that transaction. These exist because in concurrent transactions, operations from different transactions could be interleaved, so we need to know where the next entry is.
• A record for when a transaction is committed (or rolled back)

A transaction log is also used to restore a corrupted database.