Concurrency and Transaction Processing

1. Concurrency and Transaction Processing

2. Concurrency models 1. Pessimistic • avoids conflicts by acquiring locks on data that is being read, so no other processes can modify that data • acquires locks on data being modified, so no other processes can access that data for either reading or modifying • readers block writers and writers block readers

3. Concurrency models 2. Optimistic • use row versioning to allow data readers to see the state of the data before the modification occurs • process that modifies the data is unaffected by processes reading the data because the reader is accessing a saved version of the data rows • readers do not block writers and writers do not block readers

4. Transaction Processing • Guarantees the consistency and recoverability of databases. • Ensures that all transactions are performed as a single unit of workeven in the presence of a hardware or general system failure. • Such transactions are referred to as having the ACID properties

5. ACID Properties - 1 1.Atomicity Each transaction is treated as all or nothing - it either commits or aborts If a transaction commits, all its effects remain. If it aborts, all its effects are undone.

6. ACID Properties - 2 2. Consistency A transaction won't allow the system to arrive at an incorrect logical state. The data must always be logically correct.

7. ACID Properties - 3 3. Isolation Separates concurrent transactions from the updates of other incomplete transactions Accomplished automatically using locking or row versions

8. ACID Properties - 4 4. Durability Ensures that the effects of the transaction persist even if a system failure occurs Accomplished usingwrite-ahead logging and automatic rollback and roll-forward of transactions during the recovery phase

9. Dependency/Consistency problems 1. Lost updates Two processes read the same data and both manipulate the data, changing its value, and then both try to update the original data to the new value

10. Dependency/Consistency problems 2. Dirty reads Process reads uncommitted data Process has changed data but not yet committed the change, another process reading the data will read it in an inconsistent state

11. Dependency/Consistency problems 3. Non-repeatable reads (inconsistent analysis) A process might get different values when reading the same resource in two separate reads within the same transaction Can happen when another process changes the data in between the reads that the first process is doing

12. Dependency/Consistency problems 4. Phantom reads When membership in a set changes (a query with a predicatesuch as WHERE col1 < 100 is involved) Two SELECT operations using the same predicate in the same transaction return a different number of rows

13. Isolation Levels 1. Uncommitted Read (ANSI/ISO SQL-92) All the problems described previously except lost updates are possibleimplemented by allowing your read operations to not take any locks

14. Isolation Levels 2. Read Committed (ANSI/ISO SQL-92) The default isolation levelCan be either optimistic or pessimistic de-pending on the READ_COMMITTED_SNAPSHOTdatabase setting (ON/OFF)

15. Isolation Levels 3. Repeatable Read (ANSI/ISO SQL-92) Ensuring that if a transaction revisits data that is already read, the data will not have changed. (Phantoms are possible!) All the shared locks in a transaction must be held until the completion

16. Attention • Exclusive locks must always be held until the endof a transaction, no matter what the isolation level or concurrency model • Thus a transaction can be rolled back if necessary. • Otherwise might be impossible to undo the work because other concurrent transactions might have changed the data

17. Isolation Levels 5. Snapshot (non ANSI/ISO SQL-92) Allows processes to read older versions of committed data if the current version is locked. Not in the standard but useful! Google for:A Critique of ANSI SQL Isolation Levels Interesting too:The Third Manifesto, by C.J. Date and H. Darwen(or why SQL sucks)

18. Isolation Levels 5. Serializable Ensuring that if a query is reissued, rows will not have been added in the interim (phantoms will not appear). All the shared locks in a transaction must be held until completion of the transaction Not only lock data that has been read, but also lock data that does not exist!!!

19. HOW READ UNCOMMITTED ALLOWS DIRTY READ

20. READ COMMITTED without READ_COMMITTED_SNAPSHOT(HOW READ COMMITTED DOES NOT ALLOW DIRTY READS)

21. RESULT • NO DIRTY READS • GOOD! • WRITERS BLOCK READERS • COULD BE BAD! LOWER CONCURRENCY! • ANOTHER SOLUTION • USE READ_COMMITTED_SNAPSHOT

22. How to enable READ_COMMITTED_SNAPSHOT EXECUTE: ALTER DATABASE<DATABASE_NAME> SETREAD_COMMITTED_SNAPSHOT ON Enables the so called: MULTI-VERSION CONCURRENCY CONTROL (row versions are stored in the tempdb database)

23. READ COMMITTED with READ_COMMITTED_SNAPSHOT

24. RESULT • NO DIRTY READS • GOOD! • WRITERS DON’T BLOCK READERS • GOOD! • TRADEOFF • ROW VERSIONS SHOULD BE MANAGED • NON-REPEATABLE READ HAPPENS • COULD BE A PROBLEM

25. SAME with REPEATABLE READ

26. RESULT • NO NON-REPEATABLE READS • GOOD! • READERS BLOCK WRITERS • COULD BE A PROBLEM BECAUSE OF THE LOWER CONCURRENCY CAN USE THE SNAPSHOT ISOLATION LEVEL TO HAVE BOTH REPEATABLE READS AND READERS THAT DON’T BLOCK WRITERS

27. HOW TO USE SNAPSHOT ISOLATION LEVEL EXECUTE: ALTER DATABASE<DATABASE_NAME> SETALLOW_SNAPSHOT_ISOLATION ON

28. NOW THE SAME with SNAPSHOT

29. Update conflict in SNAPSHOT ISOLATION LEVEL

30. PHANTOMS EXAMPLE

31. SNAPSHOT vs. SERIALIZABLE