Two-Phase Locking and Transaction Handshakes, Slides of Fundamentals of E-Commerce

Download Two-Phase Locking and Transaction Handshakes and more Slides Fundamentals of E-Commerce in PDF only on Docsity!

2PL and Recoverability

-^ 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

Automating Locking

-^ 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

–^ data manager didn’t know about send/receive andthought the execution was SR. • Also watch out for brain transport

• 3.5 Preserving Transaction Handshakes • Read and Write are the only operations thesystem will control to attain serializability. • So, if transactions communicate via messages,then implement SendMsg as Write, andReceiveMsg as Read. • Else, you could have the following:w[x] r
  • [x] send
  • [M] receive
  • [M]

Transactions Can Communicate via Brain

Transport

T1:^ Start.^

. .Display outputCommit

T2:^ StartGet input from display.^

User reads output… User enters input. .Commit

Braintransport Docsity.com

Brain Transport (cont’d)

-^ 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 •^ 2PL serializes transactions (abbr. txns) consistentwith all transaction handshakes. I.e. there’s anequivalent serial execution that preserves thetransaction order of transaction handshakes •^ This isn’t true for arbitrary SR executions. E.g.^ –^ r[x] w^1

[x] cr 22 3 [y] cw^3

[y] c^1

-^ Tcommits before T^2

starts, but the only equivalent 3 serial execution is T

TT 3 1 2

-^ rl[x] r^1

[x] wl[y] ru 11

[x] wl[x] w 12

[x] wu[x] c 22

2

but now we’re stuck, since we can’t set rl

[y]) r[y]. 33

So the history cannot occur using 2PL.

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

Brain Transport

^ One Last Time

-^ 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

3.6 Implementing Two-Phase Locking •^ Even if you never implement a DB system, it’svaluable to understand locking implementation,because it can have a big effect on performance. •^ A data manager implements locking by^ –^ implementing a lock manager^ –^ setting a lock for each Read and Write^ –^ handling deadlocks

System Model

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)