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.