Transaction Management
- Concurrence Control
Vũ Tuyết Trinh
Learning objectives
•Upon completion of this lesson, students will be able to:
1. Understand main concepts of transaction
2. Be able to select a suitable transaction management strategy
1
Outline
1. Transaction
2. ACID properties
3. Transaction Management Interface
4. Concurrence control
5. Isolation levels
Example
500USD
Account A Account B
read(A)
If A > 500 then
B:=B+500 Crash What happen
???
A:=A-500
2
1. Transaction
• A sequence of read and write operations on data items that
logically functions as one unit of work
• Ensuring data integrity and correctness
500USD
Account A Account B
read(A)
If A > 500 then
B:=B+500
A:=A-500
5
2. ACID Properties
2.1. Atomicity
2.2. Consistency
2.3. Isolation
2.4. Durability
3
2.1. Atomicity
• Guarantee that either all of the tasks of a transaction are
performed or none of them are.
T: Read(A,t1);
If t1 > 500 {
Read(B,t2);
t2:=t2+500;
crash
Write(B,t2);
t1:=t1-500;
Write(A,t1);
}
2.2. Consistency
• Ensures that the DB remains in a consistent state before the
start of the transaction and after the transaction is over.
T: Read(A,t1); A+B = C
If t1 > 500 {
Read(B,t2);
t2:=t2+500;
Write(B,t2);
t1:=t1-500;
Write(A,t1);
} A+B = C
4
2.3. Isolation
• Ability of the application to make operations in a transaction
appear isolated from all other operations.
A= 5000, B= 3000
T: Read(A,t1);
If t1 > 500 {
Read(B,t2);
t2:=t2+500;
Write(B,t2); T’: A+B
t1:=t1-500; (= 5000+3500)
Write(A,t1); (A+B = 4500+3500)
}
9
2.4. Durability
• Guarantee that once the user has been notified of success, the
transaction will persist, and not be undone.
A= 5000, B= 3000
T: Read(A,t1);
If t1 > 500 {
Read(B,t2);
t2:=t2+500;
Write(B,t2);
t1:=t1-500;
Write(A,t1);
} crash
A= 4500, B=3500
10
10
5
3. Transaction Management Interface
11
11
3.1. Transaction States
• Active
• Failed
• Aborted
• Commited
• Partially commited
12
12
6
3.1. Transaction States
• Active
• Begin Trans
• Commit ()
• Abort()
• Savepoint Save()
• Rollback (savepoint)
(savepoint = 0 ==> Abort)
13
13
4. Concurrent control
4.1. Objective
4.2. Scheduling
4.3. Lock
14
14
7
4.1. Objective
• Ensures that database transactions are performed concurrently without the
concurrency violating the data integrity.
• Guarantees that no effect of committed transactions is lost, and no effect
of aborted (rolled back) transactions remains in the related database.
• Example
T0: read(A); T1: read(A);
A := A -50; temp := A *0.1;
write(A); A := A -temp;
read(B); write(A);
B := B + 50; read(B);
write(B); B := B + temp;
write(B);
15
15
4.2. Scheduling
(1) (2) (3)
16
16
8
4.2. Scheduling
• A schedule of a set of transactions is a linear ordering of their
actions
• e.g. for the simultaneous deposits example:
R1(X) R2(X) W1(X) W2(X)
• A serial schedule is one in which all the steps of each transaction
occur consecutively
• A serializable schedule is one which is equivalent to some serial
schedule
17
17
4.3. Lock
• Lock: a synchronization mechanism for enforcing limits on access
to DB in concurrent way.
• one way of enforcing concurrency control policies
• Lock types
• Shared lock (LS) readable but can not write
• Exclusive lock (LX): read and write LS LX
• UN(D): unlock LS true false
• Compatibility
LX false false
18
18
9
4.3. Lock
• Example
T0: LX(A); T1: LX(A);
read(A); read(A);
A := A -50; temp := A *0.1;
write(A); A := A -temp;
LX(B); write(A)
read(B); LX(B);
B := B + 50; read(B);
write(B); B:=B+temp;
UN(A); write(B);
UN(B); UN(A);
UN(B);
19
19
4.3. Lock
• Example
T0: LX(B); 1 4 T1: LX(A);
read(B); 2 5 read(A);
B := B +50; 3 6 temp := A *0.1;
write(B); 7 8 A := A -temp;
LX(A); 9 9’ write(A)
read(A); 10’ LX(B);
A := A - 50; read(B);
write(A); B:=B+temp;
UN(A); write(B);
UN(B); UN(A);
UN(B);
20
20
10
5. Isolation levels
Set isolation level <level>
• Read Uncommitted (No lost update)
• Exclusive locks for write operations are held for the duration of the
transactions
• No locks for read
• Read Committed (No inconsistent retrieval)
• Shared locks are released as soon as the read operation terminates.
• Repeatable Read (no unrepeatable reads)
• Strict two phase locking
• Serializable (no phantoms)
• Table locking or index locking to avoid phantoms
21
21
Summary
• Transaction
• Sequence of actions
• ACID
• Properties of a transaction
• Concurrence control
• Mechanism allows multiple transactions accessing the same resource
in the same time
• Isolation level
• Defining the level DBMS must ensure data integrity and correctness in
processing concurrent accesses
22
22
11
