Lecture 12 Database Management system Transactions.pptx

Transactions in DBMS

1. Transaction processing & Concurrency Control  Instructor: Ms. Sumbul Iqbal

2. What is a transaction A transaction is the basic logical unit of execution in an information system. A transaction is a sequence of operations that must be executed as a whole, taking a consistent (& correct) database state into another consistent (& correct) database state; A collection of actions that make consistent transformations of system states while preserving system consistency An indivisible unit of processing database in a consistent state database in a consistent state database may be temporarily in an inconsistent state during execution begin Transaction end Transaction execution of Transaction Account A Fred Bloggs £1000 Account B Sue Smith £0 Account B Sue Smith £500 Account A Fred Bloggs £500 Transfer £500

3. Definitions  Transaction is a series of reads and writes of database objects.  Database objects are the units in which programs read and write information.  Properties of aTransaction: ACID ◦ Atomicity (all actions in a transaction are carried out or none) ◦ Consistency (as long as each transaction leaves the DBMS in a consistent state,no transaction will see an inconsistent DBMS state) ◦ Isolation(two concurrently executing transactions do not have to consider interference) ◦ Durability (if a transaction completes successfully,its effects persist even if the system crashes)

4. Schedule  Schedule is a list of actions (reading,writing, aborting, or committing) from a set of transactions,and the order in which actions of a particular transaction appear.  A schedule that contains either an abort or a commit for each transaction whose actions are listed in it is called a complete schedule .  A complete schedule must contain all the actions of every transaction that appears in it.  If the actions of different transactions are not interleaved,that is,transactions are executed from start to finish,one by one,the schedule is called serial schedule .

5. Concurrent Execution  To improve ◦ System throughput ◦ Response time

6. Anomalies Associated with Interleaved Execution  A schedule involving two consistency preserving, committed transactions could run against a consistent database and leave it in an inconsistent state.  Two actions on the same data object conflict if at least one of them is a write.  Anomalous situations ◦ Reading Uncommitted Data --Write-Read (WR) conflict  Dirty Read ◦ Unrepeatable Reads -- Read-Write (RW) conflict ◦ Overwriting Uncommitted Data --Write-Write (WW) conflict  BlindWrite or Lost Update Problem

7. Dirty Read or The Temporary Update Problem One transaction updates a database item and then the transaction fails. The updated item is accessed by another transaction before it is changed back to its original value transaction T1 fails and must change the value of X back to its old value meanwhile T2 has read the “temporary” incorrect value of X Joe books seat on flight X Fred books seat on flight X because Joe was on Flight X Joe cancels

8. The Unrepeatable Read Problem One transaction is calculating an aggregate summary function on a number of records while other transactions are updating some of these records. The aggregate function may calculate some values before they are updated and others after. T2 reads X after N is subtracted and reads Y before N is added, so a wrong summary is the result

9. The Lost Update Problem Two transactions accessing the same database item have their operations interleaved in a way that makes the database item incorrect item X has incorrect value because its update from T1 is “lost” (overwritten) T2 reads the value of X before T1 changes it in the database and hence the updated database value resulting from T1 is lost X=4 Y=8 N=2 M=3

10. Isolation Levels Most DBMSs offer a number of transaction isolation levels , which control the degree of locking that occurs when selecting data. The isolation levels defined by the ANSI/ISO SQL standard are listed as follows. Serializable Repeatable reads Read committed Read uncommitted

11. Serializable This is the highest isolation level. With a lock-based concurrency control DBMS implementation, serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon. When using non-lock based concurrency control, no locks are acquired; however, if the system detects a write collision among several concurrent transactions, only one of them is allowed to commit.

12. Isolation Levels Repeatable reads In this isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so the phantom reads phenomenon can occur

13. Phantom reads A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first. This can occur when range locks are not acquired on performing a SELECT ... WHERE operation. The phantom reads anomaly is a special case of Non-repeatable reads when Transaction 1 repeats a ranged SELECT ... WHERE query and, between both operations, Transaction 2 creates (i.e. INSERT) new rows (in the target table) which fulfill that WHERE clause.

14. Phantom reads Note that Transaction 1 executed the same query twice. If the highest level of isolation were maintained, the same set of rows should be returned both times, and indeed that is what is mandated to occur in a database operating at the SQL SERIALIZABLE isolation level. However, at the lesser isolation levels, a different set of rows may be returned the second time.

15. Isolation Levels Read committed In this isolation level, a lock-based concurrency control DBMS implementation keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed (so the non-repeatable reads phenomenon can occur in this isolation level, as discussed below). As in the previous level, range-locks are not managed. Read uncommitted This is the lowest isolation level. In this level, dirty reads are allowed, so one transaction may see not-yet-committed changes made by other transactions.

16. Schedules InvolvingAborted Transactions    Unrecoverable Schedule ◦ When a transaction aborts whose values are read,modify and committed by other transaction. Recoverable Schedule ◦ All transactions commit at same time. Avoid Cascading Aborts ◦ Read only the changes from already committed transactions.

17. StrictTwo-Phase Locking (2PL)  Rules: ◦ If a transaction T wants to read (respectively, modify) an object,it first requests a shared (respectively,exclusive) lock on the object. ◦ If a transaction holds an exclusive lock on an object,no other transaction holds a shared or exclusive lock on the same object. ◦ All locks held by a transaction are released when the transaction is completed. ◦ A transaction cannot request additional locks once it releases any lock.

18. Lock Management  Lock manager keeps track of locks issued to transactions in a lock table.  Lock table entry for an object contains: ◦ The number of transactions currently holding a lock on the object (in shared mode) ◦ The nature of a lock (shared or exclusive) ◦ A pointer to a queue of lock requests.

19. Implementing Lock and Unlock Requests    Request shared lock ◦ Request queue is empty ◦ Object is not currently locked in exclusive mode ◦ Grant lock Request exclusive lock ◦ Request queue is empty ◦ Grant lock Otherwise ◦ Request added in queue

20. Implementing Lock and Unlock Requests T1  S(O) T2  requests X(O) Include in queue T3  requests S(O) Include in queue T1 commits T2  granted lock ----

21. Deadlocks T1  X(A) T2  X(B) T1 requests X(B) T2 requests X(A)

22. Deadlock Prevention  Give each transaction a timestamp when it starts up.  The oldest transaction has the highest priority.  If a transactionTi requests a lock and transactionTj holds the requested object.  Two policies:  Wait-die ◦ IfTi has higher priority,it is allowed to wait;otherwise it is aborted.  Wound-wait ◦ IfTi has higher priority,abortTj;otherwiseTi waits

23. Deadlock Detection   Waits-for graph: A deadlock is resolved by aborting a transaction that is on a cycle and releasing its locks;this action allows some of the waiting transactions to proceed. Use a timeout mechanism:If a transaction has been waiting too long for a lock,we can assume (pessimistically) that it is in a deadlock cycle and abort it.

24. Concurrency Control without Locking Optimistic concurrency control: The basic premise is that most transactions will not conflict with other transactions, and the idea is to be as permissive as possible in allowing transactions to execute. Transactions proceed in three phases: Read: The transaction executes,reading values from the database and writing to a private workspace. Validation: If the transaction decides that it wants to commit, the DBMS checks whether the transaction could possibly have conflicted with any other concurrently executing transaction.If there is a possible conflict,the transaction is aborted;its private workspace is cleared and it is restarted. Write: If validation determines that there are no possible conflicts,the changes to data objects made by the transaction in its private workspace are copied into the database.

25. QUIZ Consider the following two transactions: T 1 : Read ( A ) Read ( B ) If A = 0 then B := B + 1 Write ( B ). T 2 : Read ( B ) Read ( A ) If B = 0 then A := A + 1 Write ( A ).   Add lock and unlock instructions to transactions T 1 and T 2 , so that they observe the two-phase locking protocol. Can the execution of these transactions result in a deadlock?