Oct 22, 2016

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:
TA: Transfer Rs.200 from Account A to Account B.
TB: 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 TA STEP2 fails, then the recovery manager rolls back TA
Account A: Back to Rs.1000 as TA has read original value as Rs.1000.
Account B: Back to Rs.500 as TA has read original value as Rs.500.

So, final amount in the accounts after transaction TA has been rolled back due to failure and transaction TB 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 TB has read value updated by transaction TA 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:
TA: Reads balance of Account A twice while its execution.
TB: 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:
TA: Update the number of correct questions to 15 and incorrect questions to 5.
TB: 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:
TA: Get number of account whose balance > Rs.500 (twice during its execution).
TB: 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. 

10 comments:

  1. Excellent informative blog,transaction are not clear. Can you give me more collective of informations about this concepts
    Java Training in chennai

    ReplyDelete
    Replies
    1. Thank You Karthireva.
      Transactions are simply a collection statements of which either should all be executed successfully or if any statement fails, all previous executed statements should get rollback.

      I will demonstrate the practical use cases in my upcoming tutorials.
      Meanwhile you can go through following article on Isolation levels:
      http://javaterritory.blogspot.com/2016/10/transaction-isolation-levels.html

      Delete
  2. Excellent article! Please write one on spring transactions too.

    ReplyDelete
  3. Nice tips. Very innovative... Your post shows all your effort and great experience towards your work Your Information is Great if mastered very well.
    angularjs Training in bangalore

    angularjs interview questions and answers

    angularjs Training in marathahalli

    angularjs interview questions and answers

    angularjs-Training in pune

    ReplyDelete
  4. A universal message I suppose, not giving up is the formula for success I think. Some things take longer than others to accomplish, so people must understand that they should have their eyes on the goal, and that should keep them motivated to see it out til the end.

    Data Science course in Chennai | Best Data Science course in Chennai
    Data science course in bangalore | Best Data Science course in Bangalore
    Data science course in pune | Data Science Course institute in Pune
    Data science online course | Online Data Science certification course-Gangboard
    Data Science Interview questions and answers
    Data Science Tutorial

    ReplyDelete
  5. I’m planning to start my blog soon, but I’m a little lost on everything. Would you suggest starting with a free platform like Word Press or go for a paid option? There are so many choices out there that I’m completely confused. Any suggestions? Thanks a lot.
    AWS Training in Bangalore electronic city| AWS Training in Bangalore Cost
    AWS Training in Pune with placements | AWS Training in Pune
    AWS Training Course in Chennai |Best AWS Training in Chennai tnagar
    Best AWS Amazon Web Services Training in Chennai | Best AWS Training centers in Chennai
    AWS Online Training in india | AWS online training cost

    ReplyDelete
  6. Ищите, где приообрести модную женскую сумочку? Заходите в интернет-магазин фирмы сумок . Заказывайте и приобретайте стильные сумочки отбороного качества от итальянских брендов Marino Orlandi и Marino Orlandi и.

    ReplyDelete
  7. Комплекс вариантов, сориентированных на предвидение жизненного пути, называют гадание. Мистические силы и многообразные обстоятельства ворожения учеными не подтверждены, но различные люди доверяют такому. Гадание на ближайшее будущее руны - это простой порядок узнать судьбу с использованием каких-либо объектов и порядков.

    ReplyDelete