cs4432: database systems ii
DESCRIPTION
CS4432: Database Systems II. Concurrency Control Enforcing Serializability: Locking. Enforcing Serializable Schedules. DBMSs use a “ Scheduler ” that schedules the actions of transactions Transactions send their requests (R or W) to Scheduler The scheduler prevents the formation of cycles - PowerPoint PPT PresentationTRANSCRIPT
CS4432: Database Systems II
Concurrency ControlEnforcing Serializability: Locking
1
Enforcing Serializable Schedules
• DBMSs use a “Scheduler” that schedules the actions of transactions
• Transactions send their requests (R or W) to Scheduler • The scheduler prevents the formation of cycles
– It grants permission to R or W only if no cycle will be formed
2
Locking Protocol
• “Scheduler” uses a locking protocol to enforce serializability
• Two New actions– Lock (exclusive): li(A) Transaction Ti locks item A
– Unlock: Ui(A) Transaction Ti unlocks (releases) item A
3
locktable
4
Rule #1: Well-Formed Transactions
Ti: … li(A) … pi(A) … ui(A) ...
Any action (R/W) must be after the lock (l) and before the unlock (u)
Rule 1 is at the level of each transaction independent of the others
Rule 1 is at the level of each transaction independent of the others
5
Rule #2 Legal Scheduler
S = …….. li(A) ………... ui(A) ……...
no lj(A)
No transaction Tj can lock item A that is already locked by another transaction Ti(Transaction Tj must wait until Ti releases its lock)
Rule 2 is at the level of the complete schedule (Set of interleaving transactions)
Rule 2 is at the level of the complete schedule (Set of interleaving transactions)
6
Schedule F: Let’s Add Some Locking!
Does the locking mechanism working? Does it guarantee serializable schedule??
Does the locking mechanism working? Does it guarantee serializable schedule??
Still Something is Missing…
7
Still by applying the locks….results is not consistent !!!
8
Rule #3 Two Phase Locking (2PL)
Ti = ……. li(A) ………... ui(A) ……...
No unlocks No locks
A transaction never start unlocks until it takes all its locks. In other words: Take all locks before the first unlock
Rule 3 is for each transaction…Rule 3 is for each transaction…
9
2 Phase Locking Protocol
# of locks held by a transaction
Growing Phase Shrinking Phase
Collect locksRelease locks
10
Schedule F : Does it follow 2PL ?
Both transactions violate 2PL
11
Example 2PL: Schedule G
l2(B)
Scheduler will deny this request(T2 will wait…)
12
Example 2PL: Schedule G
l2(B)
Scheduler will deny this request(T2 will wait…)
At this point the scheduler can resume T2
T2 Continue processing
Scheduler Forced Schedule G to be Serializable
13
Theorem
• Any legal schedule S following the 2PL Is a Conflict Serializable schedule
14
Most DBMSs use two-phase locking (2PL) to enforce concurrency
Most DBMSs use two-phase locking (2PL) to enforce concurrency
There is a room for improvement
Improvement 1: Handling Deadlocks
• 2PL protocol does not prevent deadlocks– Transactions waiting for each other indefinitely
15
l1(B)
T1 has to wait for T2
l2(A)
T2 has to wait for T1
Deadlock situation
Handling Deadlocks
16
• Detection– Wait-for graph
• Prevention– Resource ordering– Timeout– Wait-die– Wound-wait
17
Deadlock Detection• Build Wait-For graph
– Ti Tj: means Ti is waiting for Tj
• Use lock table structures• Built incrementally by the scheduler• When cycle found, rollback victim
Cycle (Deadlock)
Select one among T1, T2, and T3 to kill (rollback) A good choice is “the most recent one”
Deadlock Prevention: Timeout
18
• If transaction waits more than L sec., roll it back!
• Pros: Simple scheme• Cons: Hard to select L
Check the other techniques in Textbook for your knowledge
Check the other techniques in Textbook for your knowledge
Improvement 2: Avoiding Cascade Rollback
• 2PL protocol does not prevent dirty reads
19
commit
Rollback
Now T2 is based on deleted transaction
To avoid this situation, we use Strict Two-Phase Locking (S2PL)
To avoid this situation, we use Strict Two-Phase Locking (S2PL)
Strict 2PL (S2PL)
• A transaction releases its lock only at commit or rollback time
20
# of locks held by a transaction
Growing Phase Shrinking Phase
Collect locksRelease locks
Commit or rollback event
S2PL: Example
21
U1(A)
Unlock A has to wait until the end (even if T1 is done
processing A)
Summary of Concurrency Control
• How to manage multiple transactions at the same time and still ensure consistency
• Schedules (order of actions)– Serial, Conflict Serializable, Legal
• Precedence Graphs for Conflict Serializable
• Locking Protocol – 2PL , S2PL, Handling of deadlocks
22