Download DataBase Development and Implementation Lec12 - Transactions Management, Concurrency Control and Locking and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
DBDI / Lecture 12
Transactions Management,
Concurrency Control &
Locking
Dr. Ala Al-Zobaidie
The slides are based on the textbook Database Systems by Thomas Connolly & Carolyn Begg
30/05/2007 DBDI / CocnCntrl 2
Lecture - Objectives
- Database Integrity
- Function and importance of
transactions.
- Properties of transactions.
- Locking
- Serialization
- Deadlock
- Approaches for Concurrency
Management
30/05/2007 DBDI / CocnCntrl 3
Database Integrity
- Database Integrity:
- Reliability & Consistency
- Must be maintained:
- After HW or SW failures
- During multiple simultaneous access to data
- Requires:
- Concurrency Control mechanism
- allows simultaneous access in a controlled manner
- Recovery procedure
- the process of restoring the Database to a correct state
after a failure
- Depends on the concept of a Transaction
}
30/05/2007 DBDI / CocnCntrl 4
Properties of Transactions
- Transaction:
- a logical unit of work to perform on Database object
- e.g. creation, alteration, or deletion of a record.
- Transactions could be simple or complex
Atomicity
All or nothing; indivisible unit
Consistency
Database should stay in a consistent state.
Independence
independent execution of other transactions.
Durability
Permanent effects of update.
A C I D
- Transferring £100 from account 1234 to Account 4567.
- Together they comprise a single transaction.
- Potential problem:
UPDATE ACCOUNTS
SET BALANCE = BALANCE - 100
WHERE ACCOUNT = 1234;
UPDATE ACCOUNTS
SET BALANCE = BALANCE + 100
WHERE ACCOUNT = 4567;
Transactions Management
Scenario:
transferring money from one account to another in one bank requires the SQL commands:
- Crash may leave the Database in
inconsistent state (c.f. database integrity)
- in our e.g., it would be better if neither of the commands had been executed.
- Transaction integrity
- demands that the effects of a transaction should be either complete or not enacted at all.
- Commit/Rollback protocol
- exist to support transaction integrity
Transaction integrity
30/05/2007 DBDI / CocnCntrl 7
- Commit
- is when changes to a Database are made
permanent
- when a Database crashes, any changes that
have not been committed will be lost
UPDATE ACCOUNTS
SET BALANCE = BALANCE - 100
WHERE ACCOUNT = 1234;
UPDATE ACCOUNTS
SET BALANCE = BALANCE + 100
WHERE ACCOUNT = 4567;
COMMIT;
Commit/Rollback protocol
explicit commit command when both of these update commands have been
issued
30/05/2007 DBDI / CocnCntrl 8
- Rollback
- a mechanism to undo the effects of a transaction.
- when issued all of the Database changes since last commit are undone.
- the Rollback in 4 undoes the effect of the
Update in 2
- because the Update has not been committed 1. Select name from customers where refno = 1; {returns ‘P Abdul’} 2. Update customers set name = ‘J Jones’ where refno =1; 3. Select name from customers where refno = 1; {returns ‘J Jones’} 4. ROLLBACK; 5. Select name from customers where refno = 1; {returns ‘P Abdul’}
Commit/Rollback Protocol /
30/05/2007 DBDI / CocnCntrl 9
- Suppose we issue a Commit command
- The Commit command in 3 makes the change permanent
- The above can be modified to use Save-points or Checkpoints
- **When a Rollback is issued, it may make reference to a checkpoint
- Select name from customers where refno = 1; {returns ‘P Abdul’}
- Update customers set name = ‘J Jones’ where refno =1;
- COMMIT ;
- Select name from customers where refno = 1; {returns ‘J Jones’}
- ROLLBACK ;
- Select name from customers where refno = 1; {returns ‘J Jones’}**
Commit/Rollback Protocol /
30/05/2007 DBDI / CocnCntrl 10
Checkpoints
- The point of synchronisation between the Database and transaction log file.
- All buffers are force-written to secondary storage **1. Select name from customers where refno = 1; {returns ‘P Abdul’}
- Update customers set name = ‘J Jones’ where refno = 1;
- COMMIT;
- Select name from customers where refno = 1; {returns ‘J Jones’}
- Update customers set name = ‘P Smith’ where refno = 1;
- SAVEPOINT_1;
- Update customers set name = ‘A Sharif’ where refno = 1;
- ROLLBACK TO SAVEPOINT_1;
- Select name from customers where refno = 1; {returns ‘P Smith’}**
Valid Concurrent Transaction / Example 1
108 W(X)
X:=X+M 108
R(X) 105
45 W(Y)
Y:=Y-N 45
R(Y) 50
105 W(X)
X:=X+N 105
R(X) 100
X Y X Y X
Database T1 T Example:
N= 5; M= 3
Is it Serial
Schedule?
Time
Valid Concurrent Transaction / Example 2
45 W(Y)
Y:=Y-N 45
R(Y) 50
108 W(X)
X:=X+N 108
R(X) 103
103 W(X)
X:=X+M 103
R(X) 100
X Y X Y X
Database T1 T Example:
N= 5; M= 3
Is it Serial
Schedule?
Example 1 & 2
are Equivalent
Schedules
Time
30/05/2007 DBDI / CocnCntrl 19
Inconsistent Analysis
Balance = 0
TA {Select Sum (Balance) From Account;}
TB {Update Accounts Set Balance = Balance - 100 Where Accno = 3; Update Accounts Set Balance = Balance + 100 Where Accno = 1;}
Retrieves account 3 Balance = 100
Retrieves account 1 Balance = 100
Retrieves Account 1 Balance = 100 Total = 100
Retrieves Account 2 Balance = 100 Total = 200
- Updates account 3
Balance = 0 Total = 200
- Retrieves Account 3
Balance = 100 Total = 300
Retrieves Account 4
Updates account 1^ Balance = 200
30/05/2007 DBDI / CocnCntrl 20
Temporary Update / Case 1
108 W(X)
X:=X+M 103
R(X) 100
100 undo
abort
105 W(X)
X:=X+N 105
R(X) 100
X Y X Y X
Database T1 T Example:
N= 5; M= 3
This scenario
is ok.
T1's effects
have been
eliminated.
Time
30/05/2007 DBDI / CocnCntrl 21
Temporary Update / Case 2
108 W(X)
100 undo
X:=X+M 108
R(X) 105
abort
105 W(X)
X:=X+N 105
R(X) 100
X Y X Y X
Database T1 T Example:
N= 5; M= 3
some of T1's
effects have
been retained!
Time
30/05/2007 DBDI / CocnCntrl 22
Temporary Update / Case 3
100 undo
108 W(X)
X:=X+M 108
R(X) 105
abort
105 W(X)
X:=X+N 105
R(X) 100
X Y X Y X
Database T1 T Example:
N= 5; M= 3
T2's effects
have been
lost, even after
commit!
What is this
problem?
Time
DBMS Transaction Subsystem
Locking
- How to avoid all previous problems?
- Lock the object to prevent access by other
transactions
- A transaction releases the object when it
finishes with it
- Other transactions need to queue until the
object is released
- The lock could be shared or exclusive
30/05/2007 DBDI / CocnCntrl 25
Shared & Exclusive Locks
• A Shared lock S is placed on an object
that is being accessed for read only
purposes
- many S locks may be placed
- an X lock must wait
• An exclusive lock X , when an object is
being altered
- No other lock may be placed
- All transactions must wait
30/05/2007 DBDI / CocnCntrl 26
The Locking Protocol
• Relate this to SQL:
• Many ‘read-only’ operations (e.g. Select)
• One ‘update’ operation (e.g. Delete)
• The Lost Update Problem:
- TA will place an X lock on Account 1234 before it starts update
• The uncommitted dependency:
- TA will lock TB out from Account 1234 until it has completed the rollback
• The inconsistent analysis:
- TA will place an S lock on all of the account records.
30/05/2007 DBDI / CocnCntrl 27
Serialization
Release S on Obj
Place S on Obj
Place S on Obj
Place S on Obj
Place X on Obj
Release S on Obj
Release X on Obj1 Place S on Obj
Place X on Obj
Release S on Obj
Release X on Obj
TA: S lock on Obj1; S on Obj2; X on Obj
TB: S lock on Obj1; X on Obj1; S on Obj
• TB has updated Obj1 before TA: Error
Place X on Obj
Denied, Wait for X on Obj
30/05/2007 DBDI / CocnCntrl 28
Serialisation
• the effects should be the same as if they
operated in a purely serial manner
• Serializable transactions:
– when the conflicting requests are presented in
the same order as if the transactions were
enacted consecutively
• Hence, TA should place X lock from start
Two-Phase Locking
Place X on Obj
Place X on Obj
Place S on Obj
Place X on Obj
Release all locks^ Wait
Place S on Obj
TA: S lock on Obj1; S on Obj2; X on Obj
TB: S lock on Obj1; X on Obj1; S on Obj
• Acquiring locks phase & releasing locks phase
• Two-Phase locking
Release all locks
Request X not S Requests denied, Wait
Wait
Problems with Locking
• Appropriate locking can guarantee
correctness, However, it also introduces
potential undesirable effects:
– Deadlock
- No transactions can proceed; each waiting on lock held by another.
– Starvation
- One transaction is permanently "frozen out" of access to data.
– reduced performance
- Locking introduces delays while waiting for locks to be released.
