# 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.

- Hardware or software failure.
- Concurrent users updating a database.

## Why transactions are necessary

Here are some examples where transcations would be important.

- Imagine the case of transfering money from one account to another. This consists of two steps, removing the money from one account, and adding it to another account. If the money is removed from one account, and the machine crashes, the database will be inconsistent.
- Suppose two users want to order 70 items, but there are only 100 in the inventory. If these requests are processed sequentially, the second user will be disappointed, but there will be no database error. However, if the inventory is checked for both users before ordering and they see sufficient inventory, then they both subtract their order from inventory because they both think there is sufficient inventory. The inventory will then show a negative number of items.

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.

- Scheduling.
- Locking. Resources can be locked to prevent access that might create inconsistencies. See Locking for a discussion of locking types and granularity.

## Real-life analogy

Ordering at fast-food restaurant.

## Transaction log

## ASID / ACID

See also ACID for a more common acronym.

Atomicity
  The commands in a transaction form an indivisible unit - either they are all done, or none are done.
Serializability
  Multiple transactions can be considered to be logically executed sequentially, even if the scheduler actually overlaps their execution.
Isolation
  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.
Durability
  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.