Download Understanding Isolation & Serializability in Database Systems: Transaction Concepts and more Study notes Computer science in PDF only on Docsity!
9. Transaction Processing Concepts
Goals: Understand the basic properties of a transaction and learn the concepts underlying transaction processing as well as the concurrent executions of transactions.
A transaction is a unit of a program execution that accesses and possibly modifies various data objects (tuples, relations).
DBMS has to maintain the following properties of transactions:
- Atomicity: A transaction is an atomic unit of processing, and it either has to be performed in its entirety or not at all.
- Consistency: A successful execution of a transaction must take a consistent database state to a (new) consistent database state. (; integrity constraints)
- Isolation: A transaction must not make its modifications visible to other transactions until it is committed, i.e., each transaction is unaware of other transactions executing concurrently in the system. (; concurrency control)
- Durability: Once a transaction has committed its changes, these changes must never get lost due to subsequent (system) failures. (; recovery)
Model used for representing database modifications of a transaction:
- read(A,x): assign value of database object A to variable x;
- write(x,A): write value of variable x to database object A
Example of a Transaction T
read(A,x) x := x - 200 write(x,A) Transaction Schedule reflects read(B,y) chronological order of operations y := y + 100 write(y,B)
Main focus here: Maintaining isolation in the presence of multiple, concurrent user transactions
Goal: “Synchronization” of transactions; allowing concurrency (instead of insisting on a strict serial transaction execution, i.e., process complete T 1 , then T 2 , then T 3 etc.) ; increase the throughput of the system, ; minimize response time for each transaction
Problems that can occur for certain transaction schedules without appropriate concurrency control mechanisms:
Inconsistent Analysis (Incorrect Summary Problem)
Time Transaction T 1 Transaction T 2 1 read(A,y1) 2 read(A,x1) 3 x1 := x1 - 100 4 write(x1, A) 5 read(C,x2) 6 x2 := x2+x 7 write(x2,C) 8 commit 9 read(B,y2) 10 read(C,y3) 11 sum := y1 + y2 + y 12 commit
In this schedule, the total computed by T 1 is wrong (off by 100). =⇒ T 1 must lock/unlock several db objects
Serializability
DBMS must control concurrent execution of transactions to ensure read consistency, i.e., to avoid dirty reads etc.
; A (possibly concurrent) schedule S is serializable if it is equivalent to a serial schedule S′, i.e., S has the same result database state as S′.
How to ensure serializability of concurrent transactions?
Conflicts between operations of two transactions:
Ti Tj read(A,x) read(A,y)
Ti Tj read(A,x) write(y,A) (order does not matter) (order matters)
Ti Tj write(x,A) read(A,y)
Ti Tj write(x,A) write(y,A) (order matters) (order matters)
A schedule S is serializable with regard to the above conflicts iff S can be transformed into a serial schedule S’ by a series of swaps of non-conflicting operations.
Lock-Compatibility Matrix:
requested existing lock lock slock xlock slock OK No xlock No No
E.g., xlock(A) has to wait until all slock(A) have been released.
Using locks in a transaction (lock requirements, LR):
- before each read(X) there is either a xlock(X) or a slock(X) and no unlock(X) in between
- before each write(X) there is a xlock(X) and no unlock(X) in between
- a slock(X) can be tightened using a xlock(X)
- after a xlock(X) or a slock(X) sometime an unlock(X) must occur
But: “Simply setting locks/unlocks is not sufficient”
replace each read(X) → slock(X); read(X); unlock(X), and
write(X) → xlock(X); write(X); unlock(X)
Two-Phase Locking Protocol (TPLP)
A transaction T satisfies the TPLP iff
- after the first unlock(X) no locks xlock(X) or slock(X) occur
- That is, first T obtains locks, but may not release any lock (growing phase) and then T may release locks, but may not obtain new locks (shrinking phase)
Strict Two-Phase Locking Protocol:
All unlocks at the end of the transaction T =⇒ no dirty reads are possible, i.e., no other transaction can write the (modified) data objects in case of a rollback of T.
Concurrency Control in PostgreSQL
In PostgreSQL (or Oracle) the user can specify the following locks on relations and tuples using the command
lock table in mode;
mode =ˆ tuple level relation level row share =ˆ slock intended slock row exclusive =ˆ xlock intended xlock share =ˆ — slock share row exclusive =ˆ — sixlock exclusive =ˆ — xlock
- serializable: serializable TAs see only those changes that were committed at the time the TA began, plus own changes.
PostgreSQL generates an error when such a transaction tries to update or delete data modified by a transaction that commits after the serializable transaction began.
T 1 T 2
set transaction isolation level serializable set transaction... update R set A = new where B = 1 commit update R set A = new where B = 1 → ERROR
Dirty-reads and non-repeatable reads are not possible. Furthermore, this mode guarantees serializability (but does not provide much parallelism).
