A transaction is unit of work that is done as a single, atomic operation; that is, the operation succeeds or fails as a whole. For example, consider transferring money from one bank account to another. This involves two steps: withdrawing the money from the first account and depositing it in the second. It is important that both steps succeed; it is not acceptable for one step to succeed and the other to fail. A database that supports transactions is able to guarantee this.
In SQL, the transaction is a set of statements, which are being executed in some order.
Starting a transaction
The first used statement starts a transaction. As there are various level of transaction support there are various rules.
Levels of transaction support
Ending a transaction
Either the COMMIT or ROLLBACK statement ends the transaction. The COMMIT commits all changes and ROLLBACK restores an original state; i.e. the state before the transaction's begin.
Some database engines don't support direct using of COMMIT and ROLLBACK.
Transaction isolation
As the transaction must keep the original state, it must be isolated from other transactions. However there is no standardized statement handling this, thus you have to consult your DBMS reference guide.
| Isolation level | Meaning |
| Read uncommitted | The transaction is not isolated; thus it can read uncommitted data. This is typically for read-only applications. |
| Read committed | The transaction waits until all rows are not locked for writing by other transactions. The transaction can read data only when they are not locked for writing by any other transaction. Transaction keeps read lock until it read all necessary data and write locks until it is committed or rolled back. |
| Repeatable read | The transaction waits until all rows are not locked for writing by other transactions. The transaction holds read locks on all rows it returns to the application and all write locks until it is committed or rolled back |
| Serializable | The transaction waits until all rows are not locked for writing by other transactions. The transaction keeps all read and write locks on all rows it affects until it is committed or rolled back. |
In particular, transaction isolation levels are defined by the presence or absence of the following phenomena:
| Phenomena | Meaning |
| Dirty reads | A dirty read occurs when a transaction reads data that has not yet been committed. |
| Nonrepeatable reads | A nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. |
| Phantoms | A phantom is a row that matches the search criteria but is not initially seen. |
The following table describes the occurrence of each phenomenon (X marked):
| Isolation level | Dirty reads | Nonrepeatable reads | Phantoms |
| Read uncommitted | X | X | X |
| Read committed | - | X | X |
| Repeatable read | - | - | X |
| Serializable | - | - | - |