Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
The concept of two-phase locking (2pl) in database systems and its importance in preserving transaction handshakes. 2pl is a protocol used to ensure serializability and consistency in multi-transaction systems. How 2pl works, its benefits, and how it preserves transaction handshakes. It also touches upon the concept of brain transport and its impact on transaction handshakes. Furthermore, it provides insights into implementing 2pl and the system model involved.
Typology: Slides
1 / 11
This page cannot be seen from the preview
Don't miss anything!
-^ 2PL does
not^ guarantee recoverability
-^ This non-recoverable execution is 2-phase lockedwl[x] w^1
[x] wu 1
[x] rl[x] r 12
[x] c… c 22
1
-^ hence, it is not strict and allows cascading aborts • However, holding write locks until
after^ commit or
abort guarantees strictness^ –^ and hence avoids cascading aborts and is recoverable^ –^ In the above example, T
must commit before its first 1
unlock-write (wu
): wl[x] w 11
[x] cwu 11
[x] rl[x] r 12
[x] c 22 Docsity.com
-^ 2PL can be hidden from the application •^ When a data manager gets a Read or Writeoperation from a transaction, it sets a read or writelock. •^ How does the data manager know it’s safe torelease locks (and be two-phase)? •^ Ordinarily, the data manager holds a transaction’slocks until it commits or aborts. A data manager^ –^ can release read locks after it receives commit^ –^ releases write locks only after processing commit,^ to ensure strictness
T1:^ Start.^
. .Display outputCommit
T2:^ StartGet input from display.^
User reads output… User enters input. .Commit
Braintransport Docsity.com
-^ For practical purposes, if user waits for T
to 1
commit before starting T
, then the data manager 2
can ignore brain transport. • This is called a transaction handshake (Tcommits before T^1
starts) 2
-^ Reason - Locking preserves the order imposed bytransaction handshakes^ –^ e.g., it serializes T
before T 1
. 2
2PL Preserves Transaction Handshakes
(cont’d)
-^ Stating this more formally … •^ Theorem:For any 2PL execution H,there is an equivalent serial execution H
,s
such that for all T
, T,ik if Tcommitted before Ti^
started in H,k
then Ti^
precedes T
in H.k s
-^ If a user reads committed displayed output of T
i
and uses that displayed output as input totransaction T
, then he/she should wait fork Tto commit before starting Ti^
.k
-^ The user can then rely on transaction handshakepreservation to ensure T
is serialized before Ti
.k Docsity.com
Transaction 1
Transaction N
Start,SQL OpsCommit, AbortDatabaseSystem
Query OptimizerQuery ExecutorAccess Method(record-oriented files)Page-oriented Files^ Database
How to Implement SQL
-^ Query Optimizer - translates SQL into an orderedexpression of relational DB operators (Select,Project, Join) •^ Query Executor - executes the ordered expressionby running a program for each operator, which inturn accesses records of files •^ Access methods - provides indexed record-at-a-time access to files (OpenScan, GetNext, …) •^ Page-oriented files - Read or Write (page address)
