Transactions and Concurrency

1. Transactions and Concurrency Edel Sherratt

2. Overview • What is a Transaction • Transaction Integrity • Transactions and SQL • Concurrency Control

3. What is a transaction • A useful piece of work • For example: • record customer order • record a lodgement to a bank account • record a withdrawal • prepare a statement of account • Often embodied as a single execution of an application program

4. A transaction has steps For example, a business to business customer order • Enter order data • Read customer record (or create a new record) • If (balance + order amount > credit then • reject order else • increase balance by order amount • store updated customer record • store the accepted order record

5. Transaction integrity • Preserve • atomicity • permanence • independence • To preserve transation integrity, the DBMS must recognize transaction boundaries – the logical beginning and end of each transaction

6. ACID properties Atomicity • A transaction is an indivisible unit of work; it is either performed in its entirety, or is not performed at all. Consistency • Database goes from one consistent state (representation of reality) to another Isolation • Partial effect of one transaction invisible to other transactions; overall effect is as if transactions were executed sequentially Durability • Effects of a successful transaction are permanently recorded in the database

7. Transaction boundaries in SQL • The first SQL statement marks the beginning of a transaction • COMMIT marks the end of a successful transaction • ROLLBACK undoes the effects of a transaction • COMMIT and ROLLBACK mark transaction boundaries • The point at which a transaction is committed or rolled back is called a commit point • The next SQL statement after a COMMIT or ROLLBACK starts the next transaction

8. General structure – embedded SQL … other code, part of the application program … SQL – the next statements are SQL UPDATE borrowers SET name = ‘Jones’ WHERE card_no =‘C12’ … other code … if (some condition) then SQL COMMIT else SQL ROLLBACK

9. Concurrency Control • Multiple concurrent transactions • Avoid undesirable interactions between transactions • Preserve ACID properties • No incomplete transactions, no interference between transactions • Effect of executing a collection of transactions concurrently is the same as if the transactions were run one by one in some order - serializability • Responsibility of DBMS

10. Example: concurrent updates of A. Smith’s savings account; starting balance is £100 • lodge £25 • retrieve customer record for A. Smith • add £25 to the balance • Store customer record • withdraw £50 • retrieve customer record for A. Smith • subtract £50 from the balance • store customer record What if the steps of these transactions were interleaved so that their execution order were ADBCEF?

11. Locking • Data is locked when a transaction begins, and released when the transaction is committed or rolled back • Lock level: database, table, tuple, attribute • Lock mode: shared (read access only), exclusive • Problems with locking • deadlock • starvation • Dealing with deadlock • prevention • detection and resolution

12. Timestamping • The system generates timestamps, usually a system clock time • Each transaction is stamped with its start time • Each data item is stamped with the times of the transactions that last read and last wrote the data item • Effect of running all the transactions concurrently is as if each transaction executed instantaneously at the time indicated by its timestamp • No transaction is allowed to read a value that was written in its future • No transaction writes a value to a data item if the old value of the data item is read in the transaction’s future

13. Concurrency control by timestamping • Writing: a transaction with timestamp t tries to write an item with read time tr and write time tw • perform the write if t ≥ trand t ≥ tw;if t ≥ tw set the write time t • do nothing if tr ≤ t < twthe transaction need not roll back since no other transaction has attempted to read the item between t and tw • roll back if t < tr • Reading: the transaction tries to read the item • perform the read if t ≥ tw;if t > trthe set the read time to t • roll back if t < tw

14. Potential problem • excessive rollback • could cause more delay than a locking system • compare with starvation

15. Summary • What is a Transaction • Transaction Integrity • Transactions and SQL • Concurrency Control