Concurrency control using transactions

1. Concurrency controlusing transactions Transactions

2. Transaction, the concept • Logical unit of work on the database • Examples • Transfer money from one bank account to another • Reserve a seat on a flight • Transaction boundaries are defined by the database user / application programmer • That’s us! Transactions

3. Interleaved processingversus parallel processing Transactions

4. Transaction operations • Reading data • Read_item(X) • Reads data X from the database • Find the address of the disk block containing X • Copy the disk block from hard disk to buffer in main memory • Copy data from the buffer to program variable X • Writing data • Write_item(X) • Writes data X to the database • Find the address of the disk block containing X • Copy the disk block from hard disk to buffer in main memory • Copy item X from program variable to buffer • Store the updated buffer on hard disk • SELECT = Read_item(X) • INSERT = Write_item(X) • UPDATE = {Read_item(X)} + Write_item(X) Transactions

5. Example transactions • Transferring N kroner from account X to account Y • Updating account X, adding M kroner Transactions

6. Concurrency problems: Lost update + dirty read Transactions

7. Concurrency problems:Incorrect analysis Transactions

8. Concurrency problems:Unrepeatable reads • A transaction reads the same data item twice, with different results! • Example: Ticket reservation • The seat is free • After at little while, your decide to buy the ticket • But now the seat is not longer free • Someone else bought it while you are ”thinking”. Transactions

9. Transaction states • Transactions can end in two ways • COMMIT_TRANSACTION • Successful end of transaction • ROLLBACK / ABORT • Unsuccessful end of transaction Transactions

10. ACID properties of a transaction • Atomicity • A transaction is an atomic unit of processing: It should either be performed in its entirety OR not performed at all. • Rollback means rollback the transaction as if it never happened. • Consistency • A transaction takes the database from one consistent state to another • Isolation • Transactions should be executed in isolation from other transactions • Durability • The results of a committed transaction can never be undone Transactions

11. Schedules of transactions • Schedule, a definition • A schedule of a set of transactions is an ordering of the operations of the transactions • Examples, fig. 20.3 • Sa: r1(X); r2(X); w1(X);r1(Y); w2(X); w1(Y) • T1 reads X, T2 reads X, T1 writes X, etc. • Sb: r1(X); w1(X); r2(X); w2(X); r1(Y); abort T1 • Serial schedule • No concurrency permitted • T1 finishes before T2 starts, or vice versa • Not good: Too much waiting time • (Conflict) serializable schedule • Schedule where the end result is (conflict) equal to the result of some serial schedule • This is OK. But how do we achieve it? Transactions

12. Serial and non-serial schedules Transactions

13. Testing for (conflict) serializability of a schedule • A simple(?) way to test if a schedule is serializable • Make a precedence graph • Nodes: Transactions • Edges • Tj does Read_item(X) AFTER Ti does Write_item(X) • Ti → Tj • Tj does Write_item(X) AFTER Ti does Read_item(X) • Ti → Tj • Tj does Write_item(X) AFTER Ti does Write_item(X) • Ti → Tj • Schedule is serializable if the precedence graph has no cycles Transactions

14. Example: Precedence graphs Transactions

15. Transaction support in Microsoft SQL Server • BEGIN TRANSACTION • COMMIT TRANSACTION • ROLLBACK TRANSACTION • Documentation • http://msdn.microsoft.com/en-us/library/ms174377.aspx Transactions