Advanced Database Systems: Transaction Lecture - PDF

CSC-313 Advanced Database Systems Lecture 3 Transaction By Farah Shaheen Acknowledgement: Department of Information Technology – Th...

CSC-313 Advanced Database Systems Lecture 3 Transaction By Farah Shaheen Acknowledgement: Department of Information Technology – The UniversityLecture slides material from of Haripur Stuart Russell  Transaction  A transaction is defined as a logical unit of work.  It consists of one or more operations on a database.  The operations must be completed together.  So all the database remains in a consistent state.  A transaction must either complete successfully or fail.  Example: a. An ATM Transaction. b. Stock database. Department of Information Technology – The University of Haripur 15  Commit and Rollback  Commit: A transaction is committed if it completes successfully and changed the data.  Rollback: If a transaction is fails and leaves the data unchanged, it is said that the transaction has been rolled back.  Rollback undo the work done in the current truncation.  Read: Read operation access an account.  Write: Write operation make changes in an account. Department of Information Technology – The University of Haripur 16  ACID Properties  A transaction must have the following four properties called ACID. 1) Atomicity 2) Consistency 3) Isolation 4) Durability Department of Information Technology – The University of Haripur 16  Atomicity  A transaction must be an atomic unit of work.  A transaction must completely succeed or completely fail.  If any statement in transaction fails, the entire transaction fails completely.  If a transaction is executed successfully it is said to be committed.  In case of any statement failure, all previous successful statements are rolled back or reverted to previous state of consistency. Department of Information Technology – The University of Haripur 16  Consistency  A transaction must leave the data in a consistent state after compilation.  For example:  In a bank database, money should never “created” or “deleted” without an appropriate deposit or withdrawal. Department of Information Technology – The University of Haripur 16  Isolation  All transactions that modify the data are isolated from each other.  They do not access the same data at the same time.  Transaction must have no dependence or effect on other transactions.  A modifying transaction can access the data only before or after another transaction is completed. Department of Information Technology – The University of Haripur 16  Durability  The durability means that the modifications made by a transaction are permanent and persistent.  If the system is crashed or rebooted, data should be guaranteed to be completed when the computer restarts. Department of Information Technology – The University of Haripur 16  Concurrency  Concurrency is a situation in which two or more users access the same piece of data at the same time.  In multi user environment, concurrency occur frequently.  In some situations, the concurrent access may arise some serious problems.  1. Lost Update Problem  2. Uncommitted Dependency problem  3. Inconsistent Analysis problem Department of Information Technology – The University of Haripur 16  Lost Update Problem  The problem arises when two or more transactions update the same data concurrently.  It occurs when two or more transactions select the same row and then update the row based on the value originally selected.  Each transaction is unaware of other transactions.  The last update overwrites updates made by the previous transactions.  It results in loss of data. Department of Information Technology – The University of Haripur 16  Example  Stock table (Product code, Description and Quantity)  Two teams of staff  Team A responsible for stock-in products  Team B responsible for stock-out products Time Team A Stock Team B Table 9:00 Qty = 100 10:30 Retrieve Qty. Retrieve Qty. Qty = 100 Qty = 100 10:31 Update Qty = Qty -90 10:32 Qty = 10 10:33 Update Qty = Qty + 30 10:34 Qty = 130 Team B`s update is lost at 10:33 which is overwritten by team A`s update Department of Information Technology – The University of Haripur 16  Uncommitted Dependency Problem  This problem arises when one transaction retrieves or updates certain parts of data before other transaction rollbacks update on the same data. Tim T1 T2 Balance x e t1 Begin Transaction 100 t2 Read (balance x) 100 t3 Balance x = balance x 100 + 100 t4 Begin Transaction Write balance x 200 t5 Read (Balance x) 200 t6 Balance x = balance rollback 100 x -10 t7 Write balance x 190 t8 commit 190 Department of Information Technology – The University of Haripur 16  Inconsistent Analysis Problem  Inconsistent analysis problem occurs when a transaction reads several values from the database but another transaction updates some of them during the executing of the first transaction.  In inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change.  Inconsistent analysis involves multiple reads (two or more) of the same row and each time the information is changed by another transaction. Department of Information Technology – The University of Haripur 16  Example Tim T1 T2 Acc1 Acc2 Acc3 Sum e t1 Begin 100 50 25 Transactio n t2 Begin Sum=0 100 50 25 0 Transaction t3 Read (Acc1) Read 100 50 25 0 (Acc1) t4 Acc1 = Acc1 - 10 Sum = 100 50 25 100 sum + Acc1 t5 Write (Acc1) Read(Acc 90 50 25 100 2) t6 Read (Acc3) Sum= 90 50 25 150 sum + Acc2 t7 Acc3 = Acc3+10 Read(Acc 90 50 25 150 3) t8 Write(Acc3) 90 50 35 150 t9Department Commit of Information Read(Acc Technology 90 –50The University 35 of Haripur 150 3) 16