Introduction to Database Transactions

A transaction is unit of work that must commit or abort as an atomic unit. By unit of work, we mean a sequence of reads and writes of database objects.

Transaction management means controlling the execution of transactions to ensure data integrity and consistency.

Let’s take an example of bank transaction: Rs. 200 are transferred in Transfer Rs.200 from Account A to Account B. It would normally be done in 2 steps:

	Account A (in Rs.)	Account B (in Rs.)
Original Amount	1000	500
STEP 1: Debit Rs.200 from Account A	1000 - 200 = 800	500	SUCCESS
STEP 2: Credit Rs.200 to Account B	800	500	FAIL

Suppose STEP 1 succeeds and STEP 2 fails, final balance in A’s account will be 800. Rs.200 are lost. This is what transaction management aims to solve, i.e., all steps should either succeed or all steps should be rolled-back, otherwise we are left in an inconsistent state. And all these steps (that either should be executed completely or none of them should have any impact on the state of database) are collectively called transactions.

States of Transactions

A transaction in a database can be in one of the following states –

• Active: Initial state of every 
• Partially Committed: When a transaction executes all its operations but hasn’t been committed 
• Failed: If any operation 
• Aborted: If the transaction fails, then the recovery manager rolls back all its write operations on the database to bring the database back to its original state where it was prior to the execution of the transaction. Transactions in this state are called aborted. The database recovery module can select one of the two operations after a transaction aborts-
• Re-start the transaction
• Kill the transaction
• Committed: If a transaction executes all its operations successfully, it is said to be committed. All its effects are now persisted in the database. 

What are ACID properties ?

A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.

• Atomicity: Either all actions in a transaction are carried out, or none are. There must be no state in a database where a transaction is left partially completed.
• Consistency: If each transaction is consistent, and the database is initially consistent, then it must remain consistent after the execution of the 
• Isolation: Transactions are isolated or protected from the effects of other scheduled transactions. When more than one transaction are being executed simultaneously and in parallel, then transactions will be carried out and executed as if it is the only transaction in the system. Final result should be same as if these transactions are executed sequentially.
• Durability: If a transactions completes successfully, then its effects persist. If a transaction commits but the system crashes before the data could be written on to the disk, then that data will be updated once the system is restored.

Schedule

A schedule is a list of actions from a set of transactions. A well-formed schedule is one where the actions of a particular transaction T are in the same order as they appear in T.

• A complete schedule is one that contains an abort or commit action for every transaction that occurs in the schedule.
• serial schedule is one where transactions are executed in a serial manner, one after the other.
• serializable schedule is a schedule whose effect on any consistent database instance is identical to that of some complete serial schedule
• equivalent schedule is a schedule that has same result on database state i.e. effect of executing the first schedule is identical to the effect of executing the second schedule.

Anamolies with interleaved execution

Let us consider ways in which a schedule involving two consistency-preserving transactions can leave a consistent database inconsistent.

• Dirty Read (WR conflict): When a transaction reads a value that is updated by another transaction which has not completed yet.

Example: We have 2 transactions executing in parallel:

T_A: Transfer Rs.200 from Account A to Account B.

T_B: Transfer Rs.100 from Account C to Account A.

	Account A (in Rs.)	Account B (in Rs.)	Account C (in Rs.)
Original Amount	1000	500	200
TA: STEP 1: Debit Rs.200 from Account A	1000 - 200 = 800
TB: STEP 1: Debit Rs.100 from Account C			200 - 100 = 100
TB: STEP 2: Credit Rs.100 to Account A	800 + 100 = 900
TA: STEP 2: Credit Rs.200 to Account B		500 + 200 = 700
Final Amount	900	700	100

Sum of original amount in all accounts = Sum of final amount in all accounts = Rs.1700

Suppose if T_A STEP2 fails, then the recovery manager rolls back T_A

Account A: Back to Rs.1000 as T_A has read original value as Rs.1000.

Account B: Back to Rs.500 as T_A has read original value as Rs.500.

So, final amount in the accounts after transaction T_A has been rolled back due to failure and transaction T_B has been committed.

	Account A (in Rs.)	Account B (in Rs.)	Account C (in Rs.)
Final Amount	1000	500	100

Sum of original amount in all accounts != Sum of final amount in all accounts

So, Rs.100 being transferred by Account C has been lost because transaction T_B has read value updated by transaction T_A which has not been completed yet.

•  Non-Repeatable Read (RW conflict): When in a transaction a value is read twice and two different results are rendered.

Example: We have 2 transactions executing in parallel:

T_A: Reads balance of Account A twice while its execution.

T_B: Transfer Rs.100 from Account C to Account A.

	Account A (in Rs.)	Account B (in Rs.)	Account C (in Rs.)
Original Amount	1000	500	200
TA: STEP 1: Get balance of Account A	1000
TB: STEP 1: Debit Rs.100 from Account C			200 – 100 = 100
TB: STEP 2: Credit Rs.100 to Account A	1000 + 100 = 1100
TA: STEP 2: Get balance of Account A	1100

• Overwriting uncommitted Data (WW conflict): When a transaction overwrites the value which has already been modified by another transaction while is still in progress.

Example: We have 2 transactions executing in parallel:

T_A: Update the number of correct questions to 15 and incorrect questions to 5.

T_B: Update the number of correct questions to 18 and incorrect questions to 2.

	Correct Questions	Incorrect Questions
Original Data	10	10
TA: STEP 1: Update the number of correct questions to 15	15
TB: STEP 1: Update the number of correct questions to 18	18
TB: STEP 2: Update the number of incorrect questions to 2		2
TA: STEP 2: Update the number of incorrect questions to 5		5
Final Data	18	5

• Phantom Read: When a read operation occurs more than one time in same transaction return different number of rows.

Example: We have 2 transactions executing in parallel:

T_A: Get number of account whose balance > Rs.500 (twice during its execution).

T_B: Transfer Rs.100 from Account C to Account B.

	Account A (in Rs.)	Account B (in Rs.)	Account C (in Rs.)
Original Amount	1000	500	200
TA: STEP 1: Get accounts whose balance > Rs.500	1000
TB: STEP 1: Debit Rs.100 from Account C			200 – 100 = 100
TB: STEP 2: Credit Rs.100 to Account B		500 +  100 = 600
TA: STEP 2: Get accounts whose balance > Rs.500	1000	600

Above mentioned anamolies can lead to inconsistent state and may have disastrous impact. To cater these scenarios, isolation levels are used. Read here for more details on Isolation Levels.

That is all on the basics of Transactions. Follow us at +Java Territory to stay updated.