Concurrency Control

Concurrency Control

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

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

Example (Contd.) • Consider two interleavings (schedules): T1: A=A+100, B=B-100 T1: A=A+100, B=B-100 T2: A=1.06*A, B=1.06*B T2: A=1.06*A, B=1.06*B • A bad one: • The 100$ that are transferred are included twice in the interest rate calculation • good

Concurrency Control • Example • Txn 1: w(x), w(y) • Txn 2: w(x) (conflicts with Txn 1) • Txn 3: w(y) (conflicts with Txn 1 but not with Txn 3) Txn 1 consistent database inconsistent database Txn 2 Txn 1 Txn 2 Txn 3 CONCURRENCY CONTROL Txn 1 Txn 2 Txn 3 consistent database consistent database consistent database

Schedules • Transaction: • A sequence of read and write operations on objects of the DB (denoted as r(x)/w(x)) • 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. • Schedule: sequence of actions (read,write,commit,abort) from a set of transactions (which obeys the sequence of operations within a transaction) • 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. • Serial schedule: Schedule where transactions are executed one after the other.

Examples • Non serial Schedule • Serial Schedule T1 T2 T1 T2 R1(A) W1(A) R1(B) W1(B) c1 R1(A) W1(A) R1(B) W1(B) c1 R2(A) W2(A) R2(B) W2(B) c2 R2(A) W2(A) R2(B) W2(B) c2

Reading Uncommitted Data (WR Conflict) • If T2 reads from T1 before T1 commits, it might read inconsistent data (Inconsistent or Dirty Reads) A=A+100 A=1.06*A B=1.06*B B=B-100 T1: R(A) W(A) R(B) W(B) T1: R(A) W(A) R(B) W(B) A=A+100 A=1.06*A B=B-100 B=1.06*B T2: R(A) W(A) R(B) W(B) T2: R(A) W(A) R(B) W(B) • The user perspective: • Example 1: T2 executes after T1 • Example 2: T2 executes after T1 because it reads the A value T1 has written; T2 executes before T1 because it writes the B value that T1 reads

Unrepeatable Reading (RW Conflict) • If T1 reads twice the same data item, but T2 changes the value between the first and second read, then we have unrepeatable read situation. T1: R(A) R(A) T2: W(A) • The user perspective: • T1 executes before T2 because it reads A before T2 writes it • T1 executes after T2 because it reads A after T2 writes it

Overwriting Uncommitted Data (WW conflict) • Can lead to lost update • T1: A=A+5, T2: A=A+10 T1: R(A) W(A) T2: R(A) W(A) A=10: A=? A=? A=20 A=15 +10 +5 • The user perspective: • It is as if T2’s update has never taken place; it is not reflected in the database • If it were reflected the final value of A should be 25 and not 15

Committed and Aborted Transactions • If a transaction aborts, all its actions are undone. • It is if they were never carried out • Dirty Read: T1: R(A) W(A) abort T2: R(A) commit • The user perspective: • T2 reads a value for A that actually will never exist!

Conflicting Operations • Conflicting operations: Two operations conflict if • They access the same object • Both operations are writes, or one is write and one is read • Note the difference between a serial schedule and our problematic examples: • Serial schedule: If two transactions T1 and T2 have two sets of conflicting operations, then either both operations of T1 are executed before both of T2’s operations or vice versa. • Our examples: one operation of T1 was ordered before the conflicting operation of T2, the other operation was ordered afterwards

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

Examples S1: T1 T2 S2: T1 T2 S3: T1 T2 r1(x) w1(x) w1(y) c1 r1(x) w1(x) w1(y) c1 r1(x) w1(x) W1(y) c1 r2(z) r2(y) w2(x) c2 r2(z) r2(y) w2(x) c2 r2(z) r2(y) w2(x) c2 S1: r1(x) w1(x) r2(z) r2(y) w2(x) c2 w1(y) c1 S2: r1(x) r2(z) r2(y) w1(x) w1(y) c1 w2(x) c2 S3: r1(x) r2(z) w1(x) w1(y) c1 r2(y) w2(x) c2

Serializability and Dependency Graphs • Dependency graph / Serialization graph / precedence graph / Serializability graph for a schedule: • Let S be a schedule (T, O, <) • 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. T1: R(A) W(A) R(B) W(B) T1: R(A) W(A) R(B) W(B) T2: R(A) W(A) R(B) W(B) T2: R(A) W(A) R(B) W(B) T2 T1 T2 T1

Dependency Graphs • Theorem: Schedule is conflict serializable if and only if its dependency graph is acyclic • 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 T1 T4 T2 T1 -> T2 -> T4 -> T3 T1 -> T4 -> T2 -> T3 T3

Schedule classes • 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) T2: R(A) commit T1: R(A) W(A) commit T1: R(A) W(A) abort T1: R(A) W(A) abort T2: R(A) commit T2: R(A) abort Recoverable schedule with commit Non-recoverable schedule Recoverable schedule with cascading abort

Schedule classes II • Avoiding cascading aborts: A transaction reads only values written by committed transactions. T1: R(A) W(A) abort T2: R(A) commit T1: R(A) W(A) commit T2: R(A) commit T1: R(A) W(A) abort T2: R(A) abort T2 does not read the value written by T1: Avoids cascading abort Recoverable schedule with cascading abort T2 can safely read the value written by T1; T1 has committed

Schedule classes III • Strict: A transaction only reads or overwrites value written by committed transactions • Usually, whenever a transactions updates an object, it logs its before image • At abort, the transaction restores the before image of the object A=10 A=20 A=30 A=10 T2: R(A) commit T1: W(A) abort T2: W(A) commit T1: W(A) abort T2: W(A) commit When T1 restores the before image of A=10, T2’s update is lost

Schedule Classes III All schedules serializable recoverable Avoiding cascading aborts strict

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

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 • Locks: Two types, because two read operations do not conflict • Basics of locking: • Each transaction Ti must obtain a S (shared) lockon object before reading, and an X (exclusive) lockon object before writing. • If an X lock is granted on object O, no other lock (X or S) might be granted on O at the same time. • If an S lock is granted on object O, no X lock might be granted on O at the same time. • Conflicting locks are expressed by the compatibility matrix: S X  -- S X -- --

Two Phase Locking • Each transaction Ti must request a S (shared) lockon object before reading, and an X (exclusive) lockon object before writing. • 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 (2PL: growing phase / shrinking phase) • Using strict two-phase locking (strict 2PL) a transactions releases all its lock at the end of its execution 2PL allows only serializable schedules 2PL allows only serializable strict schedules

Example: strict 2PL Lock Table: A: T1-S,T3-S A: T1-S,T3-S, B: T1-X A: T1-S,T3-S, T2-X B: T1-X, T3-S A: T3-S, T2-X, B: T3-S A: T2-X T3: S(A) R(A) S(B) R(B) Commit U(A),U(B) T1: S(A) R(A) X(B) W(B) Abort U(A) U(B) T2: X(A) W(A) Commit U(A) Note: strict 2PL avoids cascading aborts, simple 2PL is not even recoverable!

Lock Management • Locks are managed using a lock table • The lock table has a lock table entry for each object that is currently locked • 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 • if a T has an S lock and requests an X lock, the S lock is upgraded to an X lock • Locking and unlocking have to be atomic operations • Set latch/semaphore when accessing lock table • Transaction table: • For each transaction T contains pointer to a list of locks held by T

Implementing strict 2PL • Lock request • 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 • 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 • Otherwise: add the lock to the request lock queue • In the first two cases, the transaction can continue immediately. In the last case the transaction is blocked until the lock is granted • Lock release (at end of transaction) • Remove the lock from the granted lock queue • 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.

Why does 2PL work? • When is a schedule not serializable? • If there are operations of transactions T1 and T2 such that T1 should be ordered before T2 AND after T2 in the schedule T1 -> T2 -> T1 R1/W2 + R2/W1 R1/W2 + W2/W1 R1/W2 + W2/R1 W1/W2 + R2/W1 W1/W2 + W2/W1 … W1/R2 + R2/W1 … In all cases, T1 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 • Deadlock: Cycle of transactions waiting for locks to be released by each other. • Waits-for graph: • 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 T2: S(B) R(B) X(A) T1: S(A) R(A) X(B) T1 T2

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

Deadlock Detection (Continued) Example: T1 T2 T3 T4 S(A) R(A) X(B) W(B) S(B) S(C) R(C) X(C) X(B) X(A) T1 T2 T4 T3 T1 T2 T3 T3

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

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

Solution: Hierarchical Locking • 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 • If T has an IS lock, it may set IS and S locks on successors • If T has an IX lock, it may set any type of lock on successors • If T has an S lock, it may read the object and all successors • 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.

Solution: Hierarchical Locking • Compatibility Matrix: IX S X IS SIX IS --     -- -- -- IX    --  -- -- S -- -- -- -- SIX  -- -- -- -- -- X • Partial Order: S SIX X IS IX

Hierarchical Lock Protocol • Each transaction starts from the root of the hierarchy. • To get S or IS lock on a node, must hold IS or IX on parent node. • 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. • Must release locks in bottom-up order. Protocol is correct in that it is equivalent to directly setting locks at the leaf levels of the hierarchy.

Examples • SELECT * from Employee • Request a S lock on Employee • UPDATE Employee SET salary = salary + 1000 WHERE eid = 1008 • 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 • SELECT name FROM Employee WHERE depid = 5; • Assume scan through table: request S lock on Employee OR • Assume index for depid: request IS lock on Employee, request successively S locks for employee tuples of dep 5 • If too many locks, perform lock escalation (replace tuple locks by table lock) • UPDATE Employee SET salary = salary + 1000 WHERE depid = 5 • Without index: request SIX on Employee, request successively X locks on employee tuples of department 5 OR • With index: request IX on Employee, request repeatedly X locks

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

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

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

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

Isolation levels in SQL2 • Many systems implement hierarchical strict 2PL locking • Very restrictive, low concurrency, problem for long queries • More and more exception, e.g. Oracle/PostgreSQL (uses mix between locking and multiversion concurrency control) • In order to allow for more concurrency, SQL2 defines various levels of isolation • 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 • Definitions are no more appropriate if systems do not use locking but other forms of concurrency control • For instance, Oracle’s “serializable” level does not provide serializable schedules as defined in the literature

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

Isolation Levels in DB2 • SET TRANSACTION must be the first statement in a transaction; • UR: uncommitted read • CS: cursor stability (read committed) • RS: read stability (repeatable read) • RR: repeatable read (serializable read) • Depending on the JDBC driver / or C-preprocessor, not all isolation levels might be supported

Transactions and SQL • A transaction ends with a COMMIT, ROLLBACK, or disconnection (intentional or unintentional) from the database. • A transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK, or connection to the database • Oracle issues an implicit COMMIT before and after any data definition language (DDL) statement.

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

Example con.setAutoCommit(false) ; try { con.setTransactionIsolation (Connection.TRANSACTION_READ_COMMITTED); stmt.executeUpdate("INSERT INTO Sailors “ + ” VALUES (’Lilly', 18, 10)"); stmt.executeUpdate("INSERT INTO Sailors “ + ” VALUES (’Lilly', 18, 10)"); con.commit() ; }catch(SQLException ex) { System.err.println("SQLException: " + ex.getMessage()) ; con.rollback() ; }

Summary • Concurrent execution should have same effect as serial execution • Concurrency control schemes provide serializability • There are several lock-based concurrency control schemes (Strict 2PL, 2PL). • Many commercial systems use 2PL • SQL2 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 • Optimistic concurrency control for object-systems and in multi-tier architectures • Multi-version concurrency control where reads read old versions and do not interfere with writes

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); }

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

Reader Xact WTS timeline old new T • Writer: • Upon w(x), create new copy of x, update new copy • Upon commit: receive commit timestamp WTS (simple counter) • Label each copy created with WTS • Reader T2 • Upon start of T2: receive begin timestamp RTS = last WTS • Upon r(x), find copy with label WTS1 such that • WTS1 <= RTS • For each other copy of x with label WTS2: • WTS2 > RTS or WTS < WTS1 • Provide reading transaction with the versions that were the last committed at the time the transaction started time

Concurrency Control