Transaction and concurrency control

Transaction and concurrency control

• 1.
  - A ni l S h r e s t h a ( 0 7 0 / B C T / 0 1 ) - B i j a y S a h a n i ( 0 7 0 / B C T / 0 5 ) - B i m a l S h r e s t h a ( 0 7 0 / B C T / 1 0 ) - D e s h b h a k t a K h a n a l ( 0 7 0 / B C T / 1 3 ) 17 March 2017 1 TRANSACTION & CONCURRENCY CONTROL
• 2.
  What is aTransaction? 17 March 2017 2  Logical unit of work in database  An entire program  A portion of program  A single command  Must be either entirely completed or aborted  No intermediate steps are acceptable  Successful transactions change database from one consistent state to another
• 3.
  ACID Properties OfTransaction 17 March 2017 3  Atomic – All or nothing  All parts of transaction must be completed and committed or must be aborted and rolled back  Consistent  Conducts transactions in serial order  Important in multi-user and distributed databases
• 4.
  ACID Properties OfTransaction 17 March 2017 4  Isolation  There may be execution of multiple transaction and each transaction must be unware of each other  Durability  Changes made to database persists even if there are system failures
• 5.
  States Of Transaction 17March 2017 5  Active  Partially Committed  Committed  Failed  Aborted
• 6.
  Nested Transaction 17 March2017 6  A transaction that is created inside another transaction  While the nested (child) transaction is active, the parent transaction may not perform any operations other than to commit or abort, or to create more child transactions  When a parent aborts, all of its sub-transactions are aborted  When a sub-transaction aborts, parent can decide whether to abort or not
• 7.
  Lock 17 March 2017 7 A lock guarantees exclusive use of a data item to a current transaction  To access data item (lock acquire)  After completion of transaction(release lock)  All data item must be accessed in mutual exclusive manner
• 8.
  Types of Lock 17March 2017 8  Shared lock  Exclusive lock
• 9.
  Shared Lock 17 March2017 9  When we want read the data item value at that time shared lock is used  Lock-s
• 10.
  Exclusive Lock 17 March2017 10  It is used for both read and write  Lock-x
• 11.
  Compatibility Between LockModes 17 March 2017 11 shared exclusive shared true false exclusive false false
• 12.
  Example of lock 17March 2017 12 T1 T2 Lock-x(B) R(B) B-50 W(B) Unlock(B) lock-s(B) R(B) unlock(B)
• 13.
  Optimistic Concurrency Control 17March 2017 13  Optimistic concurrency control does not involve locking rows when reading  Optimistic concurrency control (OCC) helps increase database performance  Fewer resources are used to hold the locks during the update process  Records are locked for a shorter length of time
• 14.
  Method For ConcurrentControl 17 March 2017 14  Deadlock:  A state in which each member of a group of transactions is waiting for some other member to release a lock.  Prevention: Lock all the objects used by a transaction when it starts  not a good way.
• 15.
  Method For ConcurrentControl 17 March 2017 15  Drawbacks of locking: Lock maintenance represents an overhead that is not present in systems that do not support concurrent access to shared data.  Deadlock: Deadlock prevention reduces concurrency
• 16.
  Method For ConcurrentControl 17 March 2017 16  Timestamp ordering:  Each transaction is assigned a unique timestamp values when it starts  Timestamp defines its position in the time sequence of transaction
• 17.
  Method For ConcurrentControl 17 March 2017 17  Timestamp ordering write rule: if (Tc ≥ maximum read timestamp on D && Tc > write timestamp on committed version of D) perform write operation on tentative version of D with write timestamp Tc else /*write is too late*/ abort transaction Tc
• 18.
  Method For ConcurrentControl 17 March 2017 18  Timestamp ordering read rule: If (Tc> write timestamp on committed version of D) { let Dselected be the version of D with the maximum write timestamp ≤ Tc if (Dselected is committed) perform read operation on the version Dselected else wait until the transaction that made version Dselected commits or aborts then reapply the read rule } Else abort transaction Tc
• 19.
  Method For ConcurrentControl 17 March 2017 19  Multi-version timestamp ordering:  A list of old committed versions as well as tentative versions is kept for each object.  Read operations that arrive too late need not be rejected.