Concurrency Control Transactions A transaction is a collection

Concurrency Control

Transactions A transaction is a collection of actions that belong logically together q Example: Money transfer q I Withdraw amount X from account A I Deposit amount X on account B Database in Consistent State Begin Transaction Database might temporarily be in inconsistent state Execution of Transaction 421 B: Database Systems - Concurrency Control Database in Consistent State End Transaction

Concurrent Execution: Example q Consider two transactions (Xacts/Txn): T 1: A=A+100, B=B-100 END q q q T 2: A=1. 06*A, B=1. 06*B END Same as: T 1: R(A) W(A) R(B) W(B) END T 2: R(A) W(A) R(B) W(B) END T 1 transfers $100 from B’s account to A’s account. T 2 credits both accounts with a 6% interest payment. Two different users submit T 1 and T 2 at the same time: I No guarantee that T 1 will execute before T 2 or vice-versa I The net effect must be equivalent to these two transactions running serially in some order. 421 B: Database Systems - Concurrency Control

Example (Contd. ) q Consider T 1: A=A+100, B=B-100 two interleavings (schedules): T 2: A=1. 06*A, B=1. 06*B q good 421 B: Database Systems - Concurrency Control T 1: A=A+100, B=B-100 T 2: A=1. 06*A, B=1. 06*B q A bad one: I The 100$ that are transferred are included twice in the interest rate calculation

Concurrency Control q Example I Txn 1: w(x), w(y) I Txn 2: w(x) (conflicts with Txn 1) I Txn 3: w(y) (conflicts with Txn 1 but not with Txn 3) Txn 1 consistent database Txn 2 Txn 1 Txn 2 Txn 3 consistent database inconsistent database CONCURRENCY CONTROL Txn 1 421 B: Database Systems - Concurrency Control consistent database Txn 2 Txn 3 consistent database

Schedules q Transaction: I A sequence of read and write operations on objects of the DB (denoted as r(x)/w(x)) I Each transaction must specify as its final action either commit (c), i. e. complete successfully or abort (a), i. e. , terminate and undo all the actions carried out so far. q Schedule: sequence of actions (read, write, commit, abort) from a set of transactions (which obeys the sequence of operations within a transaction) I Reflects how the DBMS sees the execution of operations; ignores things like reading/writing from OS files etc. Complete Schedule: Contains commit/abort for each of its transactions. q Serial schedule: Schedule where transactions are executed one after the other. q 421 B: Database Systems - Concurrency Control

Examples q Serial T 1 Schedule T 2 R 1(A) W 1(A) R 1(B) W 1(B) c 1 q Non serial Schedule T 1 T 2 R 1(A) W 1(A) R 2(A) W 2(A) R 2(B) W 2(B) c 2 421 B: Database Systems - Concurrency Control R 1(B) W 1(B) c 1 R 2(A) W 2(A) R 2(B) W 2(B) c 2

Reading Uncommitted Data (WR Conflict) q If T 2 reads from T 1 before T 1 commits, it might read inconsistent data (Inconsistent or Dirty Reads) A=A+100 A=1. 06*A B=B-100 B=1. 06*B q T 1: R(A) W(A) R(B) W(B) T 2: A=A+100 R(A) W(A) A=1. 06*A R(B) W(B) B=B-100 T 1: R(A) W(A) B=1. 06*B R(B) W(B) T 2: R(A) W(A) R(B) W(B) The user perspective: I Example 1: T 2 executes after T 1 I Example 2: T 2 executes after T 1 because it reads the A value T 1 has written; T 2 executes before T 1 because it writes the B value that T 1 reads 421 B: Database Systems - Concurrency Control

Unrepeatable Reading (RW Conflict) q If T 1 reads twice the same data item, but T 2 changes the value between the first and second read, then we have unrepeatable read situation. T 1: R(A) q T 2: W(A) The user perspective: I T 1 executes before T 2 because it reads A before T 2 writes it I T 1 executes after T 2 because it reads A after T 2 writes it 421 B: Database Systems - Concurrency Control

Overwriting Uncommitted Data (WW conflict) q Can lead to lost update I T 1: A=A+5, T 2: A=A+10 +5 q A=10: A=? +10 A=20 A=15 T 1: R(A) W(A) T 2: R(A) W(A) The user perspective: I It is as if T 2’s update has never taken place; it is not reflected in the database I If it were reflected the final value of A should be 25 and not 15 421 B: Database Systems - Concurrency Control

Committed and Aborted Transactions If a transaction aborts, all its actions are undone. q It is if they were never carried out q Dirty Read: q T 1: R(A) W(A) abort q T 2: R(A) commit The user perspective: I T 2 reads a value for A that actually will never exist! 421 B: Database Systems - Concurrency Control

Conflicting Operations q Conflicting operations: Two operations conflict if I They access the same object I Both operations are writes, or one is write and one is read q Note the difference between a serial schedule and our problematic examples: I Serial schedule: If two transactions T 1 and T 2 have two sets of conflicting operations, then either both operations of T 1 are executed before both of T 2’s operations or vice versa. I Our examples: one operation of T 1 was ordered before the conflicting operation of T 2, the other operation was ordered afterwards 421 B: Database Systems - Concurrency Control

Conflict Serializable Schedules Two schedules are conflict equivalent if: I Involve the same actions of the same (committed) transactions I Every pair of conflicting actions of (committed transactions) is ordered the same way q Schedule S is conflict serializable if I S is conflict equivalent to some serial schedule which contains the committed transactions of S I Textbook differentiates between q l l l Serializable Conflict-serializable View-serializable I Here: l conflict-serializable = serializable l Ignore view-serializable 421 B: Database Systems - Concurrency Control

Examples S 1: T 1 r 1(x) w 1(y) c 1 T 2 S 2: T 1 r 1(x) r 2(z) r 2(y) w 2(x) c 2 w 1(x) w 1(y) c 1 T 2 r 2(z) r 2(y) S 3: T 1 r 1(x) w 1(x) W 1(y) c 1 w 2(x) c 2 S 1: r 1(x) w 1(x) r 2(z) r 2(y) w 2(x) c 2 w 1(y) c 1 S 2: r 1(x) r 2(z) r 2(y) w 1(x) w 1(y) c 1 w 2(x) c 2 S 3: r 1(x) r 2(z) w 1(x) w 1(y) c 1 r 2(y) w 2(x) c 2 421 B: Database Systems - Concurrency Control T 2 r 2(z) r 2(y) w 2(x) c 2

Serializability and Dependency Graphs q Dependency graph / Serialization graph / precedence graph / Serializability graph for a schedule: I Let S be a schedule (T, O, <) l l T 1: R(A) W(A) R(B) W(B) Each transaction Ti in T is represented by a node There is an edge from Ti to Tj if an operations of Ti precedes and conflicts with on of Tj’s operations in the schedule. T 1: R(A) W(A) T 2: R(A) W(A) T 1 R(B) W(B) 421 B: Database Systems - Concurrency Control T 2 T 1 T 2 R(B) W(B) T 2: R(A) W(A) R(B) W(B)

Dependency Graphs q Theorem: Schedule is conflict serializable if and only if its dependency graph is acyclic q Generating an equivalent serial schedule Continue until no nodes are left Choose a source (i. e. a node without incoming edges) put the corresponding transaction next in the serial order Delete the node and all outgoing edges T 1 T 2 T 4 T 3 421 B: Database Systems - Concurrency Control T 1 -> T 2 -> T 4 -> T 3 T 1 -> T 4 -> T 2 -> T 3

Schedule classes q q q Serial Schedule Serializable Schedule Recoverable Schedule: If transaction Ti reads a value written by transaction Tj then Ti only commits after Tj committed (and aborts if Tj aborts) T 1: R(A) W(A) abort T 2: R(A) commit Non-recoverable schedule T 1: R(A) W(A) abort T 2: R(A) abort Recoverable schedule with cascading abort 421 B: Database Systems - Concurrency Control T 1: R(A) W(A) commit T 2: R(A) commit Recoverable schedule with commit

Schedule classes II q Avoiding cascading aborts: A transaction reads only values written by committed transactions. T 1: R(A) W(A) abort T 2: R(A) T 1: R(A) W(A) abort Recoverable schedule with cascading abort T 2: R(A) commit T 2 does not read the value written by T 1: Avoids cascading abort 421 B: Database Systems - Concurrency Control T 1: R(A) W(A) commit T 2: R(A) commit T 2 can safely read the value written by T 1; T 1 has committed

Schedule classes III q Strict: A transaction only reads or overwrites value written by committed transactions I Usually, whenever a transactions updates an object, it logs its before image I At abort, the transaction restores the before image of the object A=10 T 1: A=20 A=30 W(A) A=10 abort T 2: W(A) commit When T 1 restores the before image of A=10, T 2’s update is lost 421 B: Database Systems - Concurrency Control T 1: T 2: W(A) abort W(A) commit R(A) commit

Schedule Classes III serializable All schedules recoverable Avoiding cascading aborts strict 421 B: Database Systems - Concurrency Control

Concurrency Control q Given an execution (schedule) we can test whether the execution was serializable I If execution was serializable, then ok I If not serializable, then it’s too late! Concurrency control: during execution take measures such that a non-serializable execution can never happen q 1 st Method: continuous testing of serialization graph q I Start: Empty Graph G, empty schedule S I Upon submission of o 1(x) (o=r/w) l For each transaction T 2, such that conflicting o 2(x) in S s Add edge from T 2 to T 1 in G (create T 2/T 1 if necessary) s If G has cycle, abort T 1, perform cascading abort if necessary, remove all aborted transactions from G s If G has no cycle, add o 1(x) to S 421 B: Database Systems - Concurrency Control

Concurrency Control: Locking No conflict: transactions can execute at the same time Upon first conflict: the second transaction has to wait until the first transaction commits/aborts q Locks: Two types, because two read operations do not conflict q Basics of locking: I Each transaction Ti must obtain a S (shared) lock on object before reading, and an X (exclusive) lock on object before writing. I If an X lock is granted on object O, no other lock (X or S) might be granted on O at the same time. I If an S lock is granted on object O, no X lock might be granted on O at the same time. I Conflicting locks are expressed by the compatibility matrix: q q S X 421 B: Database Systems - Concurrency Control S X -- -- --

Two Phase Locking q q q Each transaction Ti must request a S (shared) lock on object before reading, and an X (exclusive) lock on object before writing. I If no conflicting lock is active is set, the lock can be granted (and the transaction can execute the operation), otherwise the transaction must wait until conflicting locks are released A transaction does not request the same lock twice. A transaction does not need to request a S lock on an object for which it already holds an X lock. If a transaction has an S lock and needs an X lock it must wait until all other S locks (except its own) are released After a transaction has released one of its lock (unlock) it may not request any further locks (2 PL: growing phase / shrinking phase) Using strict two-phase locking (strict 2 PL) a transactions releases all its lock at the end of its execution 2 PL allows only serializable schedules 2 PL allows only serializable strict schedules 421 B: Database Systems - Concurrency Control

Example: strict 2 PL T 1: S(A) R(A) X(B) W(B) Abort U(A) U(B) T 2: T 3: S(A) R(A) X(A) W(A) Commit U(A) S(B) R(B) Commit U(A), U(B) 421 B: Database Systems - Concurrency Control Lock Table: A: T 1 -S, T 3 -S, B: T 1 -X A: T 1 -S, T 3 -S, T 2 -X B: T 1 -X, T 3 -S A: T 3 -S, T 2 -X, B: T 3 -S A: T 2 -X Note: strict 2 PL avoids cascading aborts, simple 2 PL is not even recoverable!

Lock Management Locks are managed using a lock table q The lock table has a lock table entry for each object that is currently locked q I I I q Pointer to queue of granted locks (or simply the number of transactions currently holding a lock) Type of lock held (shared or exclusive) Pointer to queue of lock requests (waiting transactions) A transaction T contains only one lock per object I if a T has an S lock and requests an X lock, the S lock is upgraded to an X lock q Locking and unlocking have to be atomic operations q Transaction table: I Set latch/semaphore when accessing lock table I For each transaction T contains pointer to a list of locks held by T 421 B: Database Systems - Concurrency Control

Implementing strict 2 PL q Lock request I If lock is S, no X lock is active and the request queue is empty: add the lock to the granted lock queue and set lock type to S I If lock is X and no lock active (=> the request queue is also empty): add the lock to the granted lock queue and set lock type to X I Otherwise: add the lock to the request lock queue I In the first two cases, the transaction can continue immediately. In the last case the transaction is blocked until the lock is granted q Lock release (at end of transaction) I Remove the lock from the granted lock queue I If this was the only lock granted on the object: grant one write lock (if the first lock in the request queue is a write) or n read locks (if the first n locks in the request queue are reads) as described above. 421 B: Database Systems - Concurrency Control

Why does 2 PL work? q When is a schedule not serializable? I If there are operations of transactions T 1 and T 2 such that T 1 should be ordered before T 2 AND after T 2 in the schedule T 1 -> T 2 -> T 1 R 1/W 2 + W 1/W 2 + … W 1/R 2 + … R 2/W 1 W 2/R 1 R 2/W 1 W 2/W 1 R 2/W 1 421 B: Database Systems - Concurrency Control In all cases, T 1 would acquire a lock after having released a lock Intuitively you can order all transactions according to the time point at which they release their first lock

Deadlocks q q Deadlock: Cycle of transactions waiting for locks to be released by each other. Waits-for graph: I I q Nodes are transactions There is an edge from Ti to Tj if Ti is waiting for Tj to release a lock Deadlock detection: look for cycles in the wait-for graph T 1: S(A) R(A) T 2: S(B) R(B) X(A) 421 B: Database Systems - Concurrency Control T 1 T 2

Dependency graph - wait-forgraph q Note: is similar to dependency graph with the following difference I If there is an edge from T 2 to T 1 in the wait-for-graph, then T 2’s operation will execute after T 1’s operation (T 2 waits for T 1 to release the lock), hence, in the dependency graph there is an edge from T 1 to T 2 I Deadlocks can happen because 2 PL avoids unserializable schedules by locking objects! T 1: S(A) R(A) T 2: S(B) R(B) X(A) Wait-for-graph X(B) 421 B: Database Systems - Concurrency Control Depend. graph T 2 T 1

Deadlock Detection (Continued) Example: T 1 T 2 S(A) R(A) X(B) W(B) S(B) X(C) T 3 T 4 S(C) R(C) X(A) T 1 T 2 T 4 T 3 T 1 T 2 T 3 X(B) 421 B: Database Systems - Concurrency Control

Deadlock (contd). q Timeout Mechanism I if a transaction waits for a lock longer than a predefined timeout interval, assume it is in a deadlock cycle and abort the transaction I Disadvantage: choice of adequate timeout interval is crucial q Alternative Prevention: Conservative 2 PL I Request all locks at begin of transaction 421 B: Database Systems - Concurrency Control

Multiple-Granularity Locks q Support different granularities of locks (tuples vs. pages vs. tables). I SELECT * from Employee I UPDATE Employee SET salary = salary + 1000 WHERE eid = 1008 q Data “containers” are nested and have tree form Database contains Tables R 1 Table R 2 Page 1 Page 2 … Tuple 1 Tuple 2 … 421 B: Database Systems - Concurrency Control …

Solution: Hierarchical Locking q o o Allow a transaction T to lock at each level, but with a special protocol using new “intention” locks: Before locking an object, T must set “intention locks” on all its ancestors. An intention lock IS (IX) indicates that T wants to read (update) an successor object on a lower level of the tree Lock types o If T has an IS lock, it may set IS and S locks on successors o If T has an IX lock, it may set any type of lock on successors o If T has an S lock, it may read the object and all successors o If T has an X lock, it may read and write the object and all successors SIX mode: Like S & IX at the same time. 421 B: Database Systems - Concurrency Control

Solution: Hierarchical Locking q Compatibility Matrix: IS IX S SIX X o IS -- IX S --- -- -- SIX -- -- -- Partial Order: X SIX S IX 421 B: Database Systems - Concurrency Control IS

Hierarchical Lock Protocol Each transaction starts from the root of the hierarchy. q To get S or IS lock on a node, must hold IS or IX on parent node. q I What if transaction holds SIX on parent? S on parent? To get X or IX or SIX on a node, must hold IX or SIX on parent node. q Must release locks in bottom-up order. q Protocol is correct in that it is equivalent to directly setting locks at the leaf levels of the hierarchy. 421 B: Database Systems - Concurrency Control

Examples q SELECT * from Employee I Request a S lock on Employee q UPDATE Employee SET salary = salary + 1000 WHERE eid = 1008 I Assume an index that leads directly to tuple 1008: request an IX lock on Employee and a X lock on the tuple with eid=1008 q SELECT name FROM Employee WHERE depid = 5; I Assume scan through table: request S lock on Employee OR I Assume index for depid: request IS lock on Employee, request successively S locks for employee tuples of dep 5 l If too many locks, perform lock escalation (replace tuple locks by table lock) q UPDATE Employee SET salary = salary + 1000 WHERE depid = 5 I Without index: request SIX on Employee, request successively X locks on employee tuples of department 5 OR I With index: request IX on Employee, request repeatedly X locks 421 B: Database Systems - Concurrency Control

Phantoms q If we relax the assumption that the DB is a fixed collection of objects, even Strict 2 PL will not assure serializability: I T 1: SELECT max(age) FROM Sailors WHERE rating = 5 l l T 1 has IS on Sailors and S on all existing tuples with rating = 5 Assume that the result is 50 I T 2: INSERT INTO Sailors (sid, age, rating) VALUES (11, 55, 5) l T 2 has IX on Sailors and X on new tuple I T 2: INSERT INTO Sailors (sid, age, rating) VALUES (12, 60, 6) l T 2 has X on additional new tuple l Assume that now the oldest sailor with rating 6 is the newly inserted one I T 2 commits and releases all locks I T 1: SELECT max(age) FROM Sailors WHERE rating = 6 l T 1 has S on all tuples with rating = 6 (including the one inserted by T 2) I T 1 -> T 2 because if it were serialized after T 2 the result of the first query should be 55 I T 2 -> T 1 because if it were serialized before T 1 the result of the second query should be some age below 60. 421 B: Database Systems - Concurrency Control

The Problem q T 1 implicitly assumes that it has locked the set of all sailor records with rating = 5. I Assumption only holds if no sailor records with this rating are added while T 1 is executing! l l Insert (value for rating = 5) UPDATE sailors SET rating = 5 where sid = 123 I Simple solution: request table level X lock. I Other solutions: Index locking and predicate locking. 421 B: Database Systems - Concurrency Control

Predicate Locking q Grant lock on all records that satisfy some logical predicate, e. g. depid= 5, age > 2*salary. q In general, predicate locking has a lot of locking overhead (I. e. , it is NP-complete) I Assume a set of tuples S 1 determined by predicate P 1 and a set of tuples S 2 covered by predicate P 2 I The lock covering S 1 conflicts with the lock covering S 2 if the intersection of S 1 and S 2 is non-empty I Wish: given P 1 and P 2 decide whether they have overlapping tuple sets l Problem is NP complete 421 B: Database Systems - Concurrency Control

Problems with locking q Assume two transactions I T 1: UPDATE Sailors set rating = 7 WHERE sid = 123 I T 2: SELECT max(age) FROM Sailors WHERE rating = 5 Assume T 1 executes first, has X- lock on sailor with sid=123 q Assume T 2 has to scan the entire table to find all sailors with rating = 5. q I For each tuple: l set S-lock on tuple l Check condition l If condition fulfilled, keep S-lock, return value to user l If condition not fulfilled, release S-lock I It has to read the tuple of sailor sid=123 to check whether rating = 5 I Hence, it blocks because T 1 has lock I T 2 is blocked by T 1 although they do not conflict! 421 B: Database Systems - Concurrency Control

Isolation levels in SQL 2 q Many systems implement hierarchical strict 2 PL locking I Very restrictive, low concurrency, problem for long queries I More and more exception, e. g. Oracle/Postgre. SQL (uses mix between locking and multiversion concurrency control) q In order to allow for more concurrency, SQL 2 defines various levels of isolation I I q Assumed to be implemented by different forms of locking Avoid different levels of anomalies Used for non-critical transactions or read-only transactions Lower levels of isolation do NOT provide serializability Problem I Definitions are no more appropriate if systems do not use locking but other forms of concurrency control I For instance, Oracle’s “serializable” level does not provide serializable schedules as defined in the literature 421 B: Database Systems - Concurrency Control

Isolation Levels Isolation LevelAnomaly Read Uncommitted Read Committed Repeatable Reads Serializable Dirty Read maybe no no no Unrepeatable Read maybe no no Phantom maybe no Read Uncommitted I Read op. do not set locks; can read not-committed updates q Read Committed I Read op. set short S locks; have to wait for X locks to be released I release lock immediately after execution of op. q Repeatable Reads I Read operations set standard lock S locks; standard 2 PL q Serializable I Read op. must set S locks that cover all objects that are read I predicate locks or coarse locks (e. g. , lock on entire relation) q 421 B: Database Systems - Concurrency Control

Isolation Levels in DB 2 q SET TRANSACTION must be the first statement in a transaction; I UR: uncommitted read I CS: cursor stability (read committed) I RS: read stability (repeatable read) I RR: repeatable read (serializable read) q Depending on the JDBC driver / or Cpreprocessor, not all isolation levels might be supported 421 B: Database Systems - Concurrency Control

Transactions and SQL q. A transaction ends with a COMMIT, ROLLBACK, or disconnection (intentional or unintentional) from the database. q A transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK, or connection to the database q Oracle issues an implicit COMMIT before and after any data definition language (DDL) statement. 421 B: Database Systems - Concurrency Control

Transactions in Java Transaction control is performed by the Connection object. q default it is in the auto-commit mode. q I each individual SQL statement is treated as a transaction by itself, and will be committed as soon as it's execution finished. q turn off/on auto-commit mode: I con. set. Auto. Commit(false) ; I con. set. Auto. Commit(true) ; q if auto-commit is off, explicit transaction termination (similar to embedded SQL): I con. commit() ; I con. rollback(); 421 B: Database Systems - Concurrency Control

Example con. set. Auto. Commit(false) ; try { con. set. Transaction. Isolation (Connection. TRANSACTION_READ_COMMITTED); stmt. execute. Update("INSERT INTO Sailors “ + ” VALUES (’Lilly', 18, 10)"); con. commit() ; }catch(SQLException ex) { System. err. println("SQLException: " + ex. get. Message()) ; con. rollback() ; } 421 B: Database Systems - Concurrency Control

Summary q q q q Concurrent execution should have same effect as serial execution Concurrency control schemes provide serializability There are several lock-based concurrency control schemes (Strict 2 PL, 2 PL). Many commercial systems use 2 PL SQL 2 provides different isolation levels that control the degree of concurrency Multiple granularity locking reduces the overhead involved in setting locks for nested collections of objects (e. g. , a file of pages); Other concurrency control mechanisms start to be used more and more frequently I Optimistic concurrency control for object-systems and in multi-tier architectures I Multi-version concurrency control where reads read old versions and do not interfere with writes 421 B: Database Systems - Concurrency Control

Transactions in C EXEC SQL WHENEVER SQLERROR DO sqlerror(); for (; ; ) { printf("Give sailor id number and rating : "); scanf("%d %d", &id, &rating); EXEC SQL SELECT … EXEC SQL UPDATE Sailors SET rating = : rating WHERE sid = : id EXEC SQL UPDATE … EXEC SQL COMMIT; } void sqlerror() { … EXEC SQL ROLLBACK; exit(1); } 421 B: Database Systems - Concurrency Control

Snapshots for Queries (Multiversion) q Idea: Let writers make a “new” copy while readers use an appropriate “old” copy: MAIN SEGMENT (Current versions of DB objects) q O O’ O’’ VERSION POOL (Older versions that may be useful for some active readers. Chained backwards) Each Xact is classified as Reader or Writer. I Writer may write some object; Reader never will. I Xact declares whether it is a Reader when it begins. I Readers are always allowed to proceed 421 B: Database Systems - Concurrency Control

Reader Xact WTS timeline q old T new Writer: time I Upon w(x), create new copy of x, update new copy I Upon commit: receive commit timestamp WTS (simple counter) I Label each copy created with WTS q Reader T 2 I Upon start of T 2: receive begin timestamp RTS = last WTS I Upon r(x), find copy with label WTS 1 such that l WTS 1 <= RTS l For each other copy of x with label WTS 2: s WTS 2 > RTS or WTS < WTS 1 I Provide reading transaction with the versions that were the last committed at the time the transaction started 421 B: Database Systems - Concurrency Control