Database: Transactions

What a transaction is

A transcation is a group of (SQL) commands that must be executed together in order to avoid an inconsistent result or database. The database engine must either perform all commands or none of them in a transaction.

Transactions are important for any database which is being modified with updates, deletions, or additions. Clearly, a read-only database doesn't need transactions.

Reasons that might cause an inconsistent database.

Why transactions are necessary

Here are some examples where transcations would be important. The purpose of transactions is to avoid cases like these.

SQL support for transactions - COMMIT, ROLLBACK

A transaction is defined by a group of SQL commands. In some systems this group must be preceded by BEGIN TRANSACTION. The end of a transaction is marked either by a COMMIT, which means that all changed made by the transaction can take place, or a ROLLBACK, which means that the entire transaction should not take effect.

Scheduling and Locking

Two techniques that are used by DMBSs to make transactions effective are.

Real-life analogy

Ordering at fast-food restaurant.

Transaction log


See also ACID for a more common acronym.
The commands in a transaction form an indivisible unit - either they are all done, or none are done.
Multiple transactions can be considered to be logically executed sequentially, even if the scheduler actually overlaps their execution.
The commands in a transaction can not allow another user of the database to create an inconsistent state. This can be done with by blocking a second user until the first user's entire transaction is completed, or by stopping the second user's transaction from being successful at some intermediate stage, therefore causing a ROLLBACK.
The database must remain in a consistent state, with every transaction either completed or rolled back. If a machine failure occurs, upon restart the database engine will examine the transaction log may rollback (or complete) incomplete transactions.