Transactions and ACID

Why it exists

A bank transfer is two writes: subtract from one account, add to another. If the machine stops between the writes, or another program reads the accounts in between, the data are wrong. A transaction groups operations into one unit that the database system promises to run all or nothing and as if alone. This page defines the unit and the four promises (ACID); the next pages study how the promises are kept for several transactions at once.

Prerequisites

None. Knowing what a table and a record are is enough (relational model if you want the vocabulary).

Precise definitions

A transaction is a sequence of operations on database items that the system treats as one logical unit. In the theory used for GATE only these operations are visible:

• $r_i(X)$: transaction $T_i$ reads item $X$ (a record, a block, a value: the unit is called an item);
• $w_i(X)$: $T_i$ writes item $X$;
• $c_i$: $T_i$ commits (its effects become permanent);
• $a_i$: $T_i$ aborts (its effects are undone).

Each transaction ends with exactly one of commit or abort. A schedule (or history) of several transactions is an interleaving of their operations that keeps the order inside each transaction.

States of a transaction (as in the textbooks; the names differ slightly): active (executing), partially committed (the last operation has run, the commit is not yet safe on disk), committed, failed (it cannot continue) and aborted (rolled back, the database is as before it started; the transaction can then be restarted or killed).

The ACID properties:

Consistency is the only property that is not provided by a mechanism alone: if every transaction is correct when it runs alone, then atomicity and isolation together keep the database consistent.

How it works

The five things that go wrong

Each animation below runs the transactions on real numbers. $A = 100$ unless stated.

Lost update. Two transactions read the same value and both write a result computed from it.

The final value is $150$; every serial execution gives $120$. The update of $T_1$ is lost although both transactions committed.

Dirty read (write-read conflict). A transaction reads a value written by a transaction that has not committed and later aborts.

$T_2$ has used the value $70$, which was removed by the abort.

Non-repeatable read (read-write conflict). A transaction reads the same item twice and gets two values because another transaction committed a change in between.

Inconsistent summary (also called incorrect summary). A transaction that reads several items sees some of them before and some after another transaction's updates.

$T_2$ computes $110$ although the total is $150$ before and after the transfer.

Failure in the middle (violates atomicity):

The textbooks add the phantom problem: a transaction runs a query with a condition twice and a second run returns an extra row inserted meanwhile by another transaction (the item-based model above does not show it because it has no insertions).

Isolation levels in SQL (quoted)

The SQL standard defines four levels by the anomalies they allow. The following table is quoted from the standard as the textbooks present it and needs verification against the edition you study; implementations differ.

SERIALIZABLE means that the outcome equals that of some serial execution; the next pages make this precise.

Worked examples

Cheat box

Traps and GATE patterns

Connections

Isolation is made precise by serializability and enforced by locking; the interaction of aborts with reads is the subject of recoverability; atomicity and durability are implemented by log-based recovery.

PYQs

A company maintains records of sales made by its
salespersons and pays them commission based on each
individual's total sales made in a year. This data is
maintained in a table with following schema:

salesinfo = (salespersonid, totalsales, commission)

In a certain year, due to better business results, the
company decides to further reward its salespersons by
enhancing the commission paid to them as per the
following formula:

If commission < = 50000, enhance it by 2%
If 50000 < commission < = 100000, enhance it by 4%
If commission > 100000, enhance it by 6%

The IT staff has written three different SQL scripts to
calculate enhancement for each slab, each of these scripts
is to run as a separate transaction as follows:

DATABASE MANAGEMENT  SYSTEM
  T1
Update salesinfo Set commission
= commission * 1.02 Where
commission < = 50000;
 T2
Update salesinfo Set commission
= commission * 1.04 Where
commission > 50000 and
commission is < = 100000;
 T3
Update salesinfo Set commission
= commission * 1.06 Where
commission > 100000;

Which of the following options of running these
transactions will update the commission of all
salespersons correctly

An audit of a banking transactions system has found that
on an earlier occasion, two joint holders of account A
attempted simultaneous transfers of Rs. 10000 each from
account A to account B. Both transactions read the same
value, Rs. 11000, as the initial balance in A and were
allowed to go through. B was credited Rs. 10000 twice.
A was debited only once and ended up with a balance of
Rs. 1000.

Which of the following properties is/are certain to have
been violated by the system?

Once the DBMS informs the user that a transaction has
been successfully completed, its effect should persist even
if the system crashes before all its changes are reflected
on disk. This property is called

Which one of the following is NOT a part of the ACID
properties of database transactions?

Consider the following transaction involving two bank
accounts x and y.

read (x) ;

x : =x - 50;

write (x) ;

read (y);

y : = y + 50;

write (y)

The constraint that the sum of the accounts x and y should
remain constant is that of

DATABASE MANAGEMENT  SYSTEM

Amongst the ACID properties of a transaction, the
'Durability' property requires that the changes made to
the database by a successful transaction persist

Self-check

Revision card

References