A Simple Transaction Analysis

read(X): which transfers the data item X from the database to a variable, also called X, in a buffer in main memory belonging to the transaction that executed the read operation.

write(X): which transfers the value in the variable X in the main-memory buffer of the transaction that executed the write to the data item X in the database.

for now, we will assume that the write operation updates the database immediately. Real life case is not like this

Let Ti be a transaction that transfers $50 from account A to B. This transaction can be defined as:

Ti =

    read(A);
    A := A − 50;
    write(A);
    read(B);
    B := B + 50; 
    write(B);

Lets analyze ACID properties of this transaction:

• Consistency: The consistency requirement here is that the sum of A and B be unchanged by the execution of the transaction. Without the consistency requirement, money could be created or destroyed by the transaction! It can be verified easily that, if the database is consistent before an execution of the transaction, the database remains consistent after the execution of the transaction. Ensuring consistency for an individual transaction is the responsibility of the application programmer who codes the transaction. This task may be facilitated by automatic testing of integrity constraints

• Atomicity: We can easily see that if system(computer which is executing transaction) crashes just after write(A);, the database will end up in inconsistent state. Now there has to be a mechanism by database itself so that it brings back the database in consistent state.

  • The basic idea behind ensuring atomicity is this: The database system keeps track (on disk) of the old values of any data on which a transaction performs a write. This information is written to a file called the log. If the transaction does not complete its execution, the database system restores the old values from the log to make it appear as though the transaction never executed. We discuss these ideas further in Section 14.4. Ensuring atomicity is the responsibility of the database system; specifically, it is handled by a component of the database called the recovery system

• Durability: We assume for now that a failure of the computer system may result in loss of data in main memory, but data written to disk are never lost. We can guarantee durability by ensuring that either:

  • The updates carried out by the transaction have been written to disk before the transaction is marked as complete.

    Information about the updates carried out by the transaction and written to disk is sufficient to enable the database to reconstruct the updates when the database system is restarted after the failure. The recovery system of the database is responsible for ensuring durability, in addition to ensuring atomicity.

• Isolation: If several transactions, Ti, are executed concurrently, their operations may interleave in some undesirable way, resulting in an inconsistent state. e.g. account A has Rs. 100 initially. T1 transfers Rs. 20 from account B to A. At the same time, T2 transfers Rs. 50 from account C to A. Ideally final output should be Rs. 170 in account A. But consider this sequence:

    T1 reads from account A in variable X.   X = X + 20   T2 reads from account A in variable Y.   Y = Y + 50   T1 writes from X to account A   T2 writes from Y to account A

  • The final output in this sequence will be Rs. 150. The total money drawn from the system (from account B and C) is Rs. 170. The total money added back to system after T1 and T2 are complete is Rs. 150. Hence inconsistent

  • Ensuring the isolation property is the responsibility of a component of the database system called the concurrency-control system.