More on Isolation

1. More on Isolation Department of Computer Science and Engineering, HKUST 1

2. Locking Single Data Items • So far, accesses and locks are made to single items • Read(x), Write(y) and correspondingly lock-R(x) and lock-W(y) • In relational databases, accesses are often made to a set of items that satisfies a predicate (SELECT, INSERT, UPDATE) • What should we lock? • A row? A table? • How should we lock? • Maintain a lock till the end of a transaction? Release a lock as soon as we finish read/write it? • What is a conflict? Department of Computer Science and Engineering, HKUST 2

3. Lock Granularity • A lock may lock an attribute value (fine granularity), a row or a table (coarse granularity) • Table lock (TL): one lock per table; lock entire table accessed by the statement • Row lock (RL): one lock per row; lock the row to be accessed • Tradeoff • Fine granularity allows high concurrency but more locks to maintain • Coarse granularity is the reverse • Fine granularity also causes unexpected semantic problems Department of Computer Science and Engineering, HKUST 3

4. Locks prevent Anomalies • We have already talked about some anomalies • Dirty Read: Read a value that has been written but uncommitted by another transaction • Dirty Write • Lost Update: Write to the same item by two transactions; second one overwrites first one • Now we discuss one more • Phantom • Non-Repeatable Read Department of Computer Science and Engineering, HKUST 4

5. Anomaly: Non-Repeatable Read T1 T2 SELECT SUM (balance) FROM Accounts WHERE name = ‘Mary’ UPDATE Accounts SET balance = 1.05 * balance WHERE name = ‘Mary’ SELECT SUM (balance) FROM Accounts WHERE name = ‘Mary’ does not introduce a phantom into predicate name=‘Mary’ • Phantom: execution of same SELECT twice yields different sets of rows • The second returns at least one row not returned by the first • Non-repeatable read: execution of same SELECT twice yields the same set of rows, but attribute values might be different Department of Computer Science and Engineering, HKUST 5

6. Conflicts in Relational Databases • Accounts keeps the balance for each customer • Depositor records the total balance for each customer • Audit should get the SAME result in the two SELECT statements • Interleaved execution of the two transactions is not serializable Audit: NewAccount: SELECT SUM (balance) FROM Accounts WHERE name = ‘Mary’; SELECT totbal FROM Depositors WHERE name = ‘Mary’ INSERT INTO Accounts VALUES (‘123’,‘Mary’,100); UPDATE Depositors SET totbal = totbal + 100 WHERE name = ‘Mary’ Department of Computer Science and Engineering, HKUST 6

7. Problem with Row Locking • First Audit does not see the new account, but the second Audit does; they return different results • Schedule is not serializable • Problem: Row locks held by the first Audit cannot block INSERT in New Account • A transaction cannot lock a data item that does not exist yet !!! • The inserted row is referred to as a phantom Department of Computer Science and Engineering, HKUST 7

8. update insert update Phantoms under Row Locking • Phantoms occur when row locking is used • T1 : SELECTs, UPDATEs, or DELETEs using a predicate P • T2 : createsa row (using INSERT or UPDATE) satisfying P T1: UPDATE Table T2: INSERT INTO Table SET Attr = …. VALUES ( … satisfies P…) WHERE P • T1 checks the results of update and would be surprised to see a row not updated • Need to lock the whole table, but concurrency is reduced Department of Computer Science and Engineering, HKUST 8

9. Predicate Locking (PL) • Table Locking prevents phantoms but Row Locking does not • Predicate locking also prevents phantoms • A predicate describes a set of rows, which could be in different tables; e.g. name = ‘Mary’ • A subset of the rows satisfying name = ‘Mary’(e.g., tuples in Accounts table) • A SQL statement has an associated predicate, acquire a (read or write) lock for it (i.e., rows specified by the predicate) • Two predicate locks conflict if one is a write and they contain a common row (not necessarily in the same table) Department of Computer Science and Engineering, HKUST 9

10. Mary Mary Mary Preventing Phantoms With Predicate Locks Audit: SELECT SUM (balance) FROM Accounts WHERE name = ‘Mary’ NewAccount: INSERT INTO Accounts VALUES (‘123’,‘Mary’,100) • Audit gets read lock on predicate name=‘Mary’ • NewAccount requests write lock on predicate (acctnum=‘123’  name=‘Mary’  bal=100) • Request denied since predicates overlap Audit holds read-lock on PL name=“Mary” NewAccount tries to write lock PL name=“Mary” and blocked Department of Computer Science and Engineering, HKUST 10

11. Conflicts and Predicate Locks (Example 1) SELECT SUM (balance) DELETE FROM Accounts FROM Accounts WHERE name = ‘Mary’ WHERE bal < 100 • Statements conflict since: • Predicates overlap and one is a write • There might be accounts with bal < 100 and name = ‘Mary’ • Locking is conservative: there might be no rows in Accounts satisfying both predicates • No phantom occur in this (DELETE) case Department of Computer Science and Engineering, HKUST 11

12. Conflicts And Predicate Locks (Example 2) SELECT SUM (balance) DELETE FROM Accounts FROM Accounts WHERE name = ‘Mary’ WHERE name = ‘John’ • Statements commute since: • Predicates are disjoint. • There can be no rows (in or not in Accounts) that satisfy both predicates • No phantom occurs in this (DELETE) case Department of Computer Science and Engineering, HKUST 12

13. Implementation of Predicate Locks • A DMBS who claims to support Predicate Locking may in fact implement a table lock • Predicate locks are typically implemented as an index lock • Assuming an index is available on a table • When a tuple is read or written, it must be located via an index • Then, lock the index node (e.g., the leaf node in B+tree) Department of Computer Science and Engineering, HKUST 13

14. SQL Isolation Levels • SQL standard does not specify how to implement an isolation level • Oracle does not allow Read uncommitted • Most DBMSs default to Read committed Department of Computer Science and Engineering, HKUST 14

15. Setting Isolation Level SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE } MS SQL Server Syntax SELECT Name FROM Employee (lock-type) NOLOCK | HOLDLOCK | UPDLOCK | TABLOCK | PAGLOCK | TABLOCKX | READCOMMITTED | READUNCOMMITTED | REPEATABLEREAD | SERIALIZABLE | READPAST | ROWLOCK Department of Computer Science and Engineering, HKUST 15

16. Transaction and Statement Isolation • DBMS might be executing several SQL statements (from different transactions) concurrently • While two transactions T1 and T2 might not be isolated, the execution of each statement within T1 must be isolated with respect to the execution of each statement within T2. Department of Computer Science and Engineering, HKUST 16

17. Locking Implementation of SQL Isolation Levels • Locking implementation is based on: • Entities locked: tables, rows, predicates, … • Lock modes: read & write • Lock duration: • Short: locks acquired in order to execute a statement are released when statement completes • Long: locksacquired in order to execute a statement are held until transaction completes • Medium: something in between Department of Computer Science and Engineering, HKUST 17

18. Locking Implementation of SQL Isolation Levels • Write locks are handled identically at all isolation levels: • Long-duration predicate write locks are associated with UPDATE, DELETE, and INSERT statements • This rules out dirty writes • In practice, predicate locks are implemented with table locks or by acquiring locks on an index as well as the data Department of Computer Science and Engineering, HKUST 18

19. Locking Implementation of SQL Isolation Levels • Read locks are handled differently at each level: • READ UNCOMMITTED: no read locks • Hence a transaction can read a write-locked item • Allows dirty reads, non-repeatable reads, and phantoms • READ COMMITTED: short-duration read locks on rows returned by SELECT • Prevents dirty reads, but non-repeatable reads and phantoms are possible Department of Computer Science and Engineering, HKUST 19

20. Locking Implementation of SQL Isolation Levels • REPEATABLE READ: long-duration read locks on rows returned by SELECT • Prevents dirty and non-repeatable reads, but phantoms are possible • SERIALIZABLE: long-duration read lock on predicate specified in WHERE clause • Prevents dirty reads, non-repeatable reads, and phantoms and … • Guarantees serializable schedules Department of Computer Science and Engineering, HKUST 20

21. Summary • Concurrent transactions are very difficult to support because correctness involves real-world meaning • “sum(balance)” has a precise meaning in real world • Tradeoff between level of concurrency or isolation level with performance is important; otherwise, just exclusive lock all items Department of Computer Science and Engineering, HKUST 21