transactions or concurrency control n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Transactions or Concurrency Control PowerPoint Presentation
Download Presentation
Transactions or Concurrency Control

Loading in 2 Seconds...

play fullscreen
1 / 36

Transactions or Concurrency Control - PowerPoint PPT Presentation


  • 1771 Views
  • Uploaded on

Transactions or Concurrency Control. Introduction. A program which operates on a DB performs 2 kinds of operations: Access to the Database (Read/Write) Memory operations. sailors. Reserves. Main Memory. DISK. Read + Memory operations. Read operation. Read operation. Memory operations.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Transactions or Concurrency Control' - niveditha


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
introduction
Introduction
  • A program which operates on a DB performs 2 kinds of operations:
    • Access to the Database (Read/Write)
    • Memory operations

sailors

Reserves

Main Memory

DISK

slide3

Read + Memory operations

Read operation

Read operation

Memory operations

introduction1
Introduction
  • When dealing with concurrency control, we are only interested in operations on the DB: Read or Write
  • So, we deal with “Abstractions” of programs
  • An “Abstraction” of a program is a series of operations the program performs on a DB
  • We also call this a Transaction (or sometimes, a Program)
example
Example

Transaction 1

Transaction 1

Read(C)

Read(A)

Write(A)

Read(B)

Write(B)

Write(C)

R1(C)

R1(A)

W1(A)

R1(B)

W1(B)

W1(C)

operations= Read(A),…

or

Items=A, B, C

definitions
Definitions
  • Schedule: The order of execution of operations of 2 or more transactions.

Schedule S1

Transaction1

Transaction2

R(A)

R(C)

W(A)

R(B)

W(C)

R(B)

W(B)

W(B)

Time

slide7
When a single Transaction is run, there is no Concurrency Control problem
  • When there are more, problems might occur
  • Example: 2 programs, each adding 100$ to an account A
example 2 programs each adding 100 to an account a
Example: 2 programs, each adding 100$ to an account A
  • If they are run one after the other:

Transaction 1

Transaction 2

R(A)

W(A)

R(A)

W(A)

Time

No problem!

example 2 programs each adding 100 to an account a1
Example: 2 programs, each adding 100$ to an account A
  • If they are run in parallel:

Transaction 1

Transaction 2

R(A)

W(A)

R(A)

W(A)

Time

Problem! Why?

definitions1
Definitions
  • Serial Schedule: A schedule in which the transactions are performed one after the other in a serial manner.

Read(A)

Write(A)

Read(B)

Write(B) Read(C)Write(C)Read(B)

Write(B)

schedules
Schedules
  • A schedule is “correct” if it gives the same result as a serial schedule for any calculation.
  • Examples:

Read(A)

Read(A)

Write(A)

Write(A)

Read(B)

Write(B)

Read(B)

Write(B)

Read(A)

Read(A)

Write(A)

Write(A)

Read(B)

Write(B)

Read(B)

Write(B)

Read(A)

Write(A)

Read(A)

Write(A)

Read(B)

Write(B)

Read(B)

Write(B)

schedules1
Schedules
  • Example for a “correct” schedule:

Read(A)

Write(A)

Read(A)

Write(A)

Read(B)

Write(B)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

And this will never cause an interleaving problem

Will always give the same result as

slide13
We would thus like to know when 2 schedules are equivalent
  • Equivalent: Will give the same result for any input
  • How do you check for equivalence?
  • Naïve approach: Check the output for all inputs
  • This is clearly impossible
  • So, we need a simple set of rules to tell us if 2 schedules are equivalent
slide14
Schedules are View Equivalent if:
    • They consist of the same transactions.
    • If Tk reads an initial value for A in S1, then Tk will also read an initial value for A in S2 (“initial”=A has not been written to yet).
    • If Tk reads a value of A written by Ti in S1, then Tk will also read a value of A written by Ti in S2.
    • If Ti writes a final value for A in S1, then Ti writes a final value for A in S2.

What are the violations of the following schedules to view-equivalence?

T1 T2 T3

T1 T2 T3

R1(A)

W1(A)

R1(C)

W1(C)

R2(C)

W2(C)

R2(B)

W2(B)

R3(C)

W3(C)

R1(A)

R2(C)

W2(C)

W1(A)

R1(C)

W1(C)

R3(C)

W3(C)

R2(B)

W2(B)

Schedule S1

Schedule S2

slide15

Are these schedules View-Equivalent?

Schedule S1

Schedule S2

T1 T2 T3

T1 T2 T3

R1(A)

R2(C)

W2(C)

W1(A)

R1(C)

W1(C)

R3(C)

W3(C)

R2(B)

W2(B)

R1(A)

R2(C)

W2(C)

W1(A)

R1(C)

W1(C)

R2(B)

W2(B)

W3(C)

R3(C)

slide16

Are these schedules View-Equivalent?

Schedule S1

Schedule S2

T1 T2 T3

T1 T2 T3

R2(C)

W2(C)

R1(A)

W1(A)

R1(C)

W1(C)

W3(C)

R3(C)

R2(B)

W2(B)

R1(A)

R2(C)

W2(C)

W1(A)

R1(C)

W1(C)

R2(B)

W2(B)

W3(C)

R3(C)

slide17

Are these schedules View-Equivalent?

Schedule S1

Schedule S2

T1 T2 T3

T1 T2 T3

R1(A)

R2(C)

W2(C)

W1(A)

R1(C)

W1(C)

R3(C)

W3(C)

R2(B)

W2(B)

R1(A)

R2(C)

W2(C)

W1(A)

R2(B)

W2(B)

R3(C)

R1(C)

W1(C)

W3(C)

slide18

Are these schedules View-Equivalent?

Schedule S1

Schedule S2

T1 T2 T3

T1 T2 T3

R1(A)

R2(C)

W2(C)

W1(A)

R1(C)

W1(C)

R3(C)

W3(C)

R2(B)

W2(B)

R1(A)

R2(C)

W2(C)

W1(A)

R1(C)

W1(C)

R2(B)

W2(B)

R3(C)

W3(C)

view equivalence
View-Equivalence
  • If 2 schedules are view-equivalent:
    • The same transactions will read the same values in both schedules
    • Therefore, they will also write the same values
    • This is true for any calculation
definitions2
Definitions
  • A schedule is View-Serializable if it is View-Equivalent to some Serial schedule.

S1

S2

Read(A)

Read(C)

Write(A)

Read(B)

Write(C)

Write(B)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Read(C)

Write(C)

Read(B)

Write(B)

Schedule S1 is view-equivalent to a serial schedule (S2), so it is View-Serializable

slide21
What is the Serial Schedule that S1 is equivalent to?

S2

S1

R(A)

W(A)

R(C)

W(C)

R)C(

W(B)

R(A)

R(B)

W(B)

W(B)

R(A)

W(A)

R(A)

R(B)

R(C)

W(B)

W(C)

R)C(

slide22
What is the Serial Schedule that S1 is equivalent to?

S1

R(A)

W(A)

R(A)

R(C)

R(C)

W(A)

W(C)

R)A(

There is no Serial Schedule that S1 is view equivalent to.

In other words, S1 is not View-Serializable

slide23
We already said that for any equivalent S1, S2: If Tk reads a value of A written by Ti in S1, then Tk will also read a value of A written by Ti in S2.
  • In simpler words: If in S1 Read(A) in T1 is “lower” than Write(A) in T2, then this has to hold in S2 too.
  • And in a picture:
  • What about Write(A) which is “lower” than Read(A)? And Write(A) which is “lower” than Write(A)?

Do these also have to hold in an equivalent schedule?

S1

W(B)

R(A)

W(A)

R(A)

R(B)

R(B)

Lower = later

slide24
Blind Write: A transaction performs a Blind Write of A if it writes A without reading it before.
  • Assuming there are no Blind Writes, and S2 is an equivalent serial schedule :
    • If Tk writes a value of A which was previously read by Ti in S1, then this will happen in S2 too.
    • If Tk writes a value of A which was previously written by Ti in S1, then this will happen in S2 too.

Read(A)

Write(A) Read(C)Write(B)

Blind Write

slide25
We want to show that if Write(B) in Ti is “lower” than Read(B) in Tk then this has to happen in any equivalent serial schedule.
  • Suppose this is the case:
  • So, suppose this is the case:

R(B)

R(B)

W(B)

R(B)

W(B)

R(B)

S1

S2

R(B)

W(B)

W(B)

R(B)

R(B)

W(B)

R(B)

W(B)

Blind write!

S2

S1

why is the no blind writes demand necessary
Why is the No Blind Writes demand Necessary?

R(B)

W(B)

R(B)

W(B)

R(B)

W(B)

W(B)

R(B)

Blind write!

S1

S2

R(B)

W(B)

R)B(

W(B)

R(B)

No Blind write

R(B)

R)B(

W(B)

R(B)

W(B)

S1

S2

Bottom line:if there are no blind writes, If Tk writes a value of A which was previously read by Ti in S1, then this will happen in any equivalent serial schedule

slide27
This can also be shown for two Write operations in the same way.

This leads us to the following definition:

  • There is a Conflict between 2 operations in different transactions, if at least one of them is a Write, and they are performed on the same item A.
  • According to what we showed,

if there are no blind writes,

the direction of the conflict (arrow)

has to be kept in any equivalent

serial schedule !

  • So is there a view-equivalent

serial schedule to S1?

S1

R(A)

W(A)

R(A)

R(B)

R(B)

W(B)

W(B)

R)C(

Find the conflicts…

slide28
We can now define equivalence between schedules according to their conflicts:
  • Schedules S1, S2 are Conflict Equivalent if they consist of the same transactions and the conflict arrows have the same directions.

S1

S2

R(A)

W(A)

R(A)

R(B)

R(B)

W(B)

W(B)

R)C(

R(A)

W(A)

R(A)

R(B)

R(B)

W(B)

W(B)

R)C(

Conflict -Equivalent:

slide29

Schedules are Conflict Equivalent if:

  • They consist of the same transactions.
  • The conflict arrows have the same directions.
  • Lemma: Conflict Equivalence => View Equivalence

(this is true even if there are Blind Writes!)

Proof: We assume S1 and S2 are Conflict Equivalent. We need to prove 1-4 from above.

Schedules are View Equivalent if:

  • They consist of the same transactions.
  • If Tk reads an initial value for A in S1, then Tk will also read an initial value for A in S2 (initial=A has not been written to).
  • If Tk reads a value of A written by Ti in S1, then Tk will also read a value of A written by Ti in S2.
  • If Ti writes a final value for A in S1, then Ti writes a final value for A in S2.
slide30
Schedule S1 is Conflict Serializable if it is Conflict-Equivalent to some serial schedule S2.
  • Conflict Serializable => View Serializable (directly from the Lemma).
  • The other direction is not necessarily true if there are Blind Writes:

T1T2T3

R(A)

W(A)

W(A)

W(A)

There is no serial schedule which is conflict-equivalent to s1

S1

T1T2T3

R(A)

W(A)

W(A)

W(A)

S2

But S2 is serial and is view- equivalent to S1

slide31

T1

T2

Node for each transaction

S1

T1

T2

The precedence graph

R(A)

W(A)

R(A)

R(B)

R(B)

W(B)

W(B)

R)C(

Edge from T1 to T2 if there is a conflict between T1 and T2 in which T1 occurs first

S1 is conflict-serializable iff its precedence graph doesn’t contain a circular path

which is conflict serializable
Which is conflict-Serializable?

W(B)

R(A)

W(A)

R(A)

R(B)

R(C)

W(B)

W(C)

R)C(

W(B)

R(A)

W(B)

R(A)

R(B)

R(C)

W(C)

W(C)

R)C(

R(C)

R(A)

W(A)

W(B)

R(A)

R(B)

W(B)

R)C(

locks
Locks
  • Used in order to allow only serializable schedules.
  • The principle: before performing a write/read on item A, a transaction asks for a lock on A.
  • Only after getting the lock from the lock-manager can the transaction perform the read/write.
  • 2 kinds of locks:
    • Shared lock: many transactions can hold a shared lock on the same item at the same time.
    • Exclusive lock: only one transaction can hold an exclusive lock on an item at any given time.
  • In order to Read, a Shared Lock is needed.
  • In order to Write, an Exclusive Lock is needed.
2 phase locking 2pl

T3

T2

T1

2-Phase Locking (2PL)
  • A protocol (set of rules) which uses locks to ensure only serializable schedules.
  • The only additional rule: after a transaction has freed a lock it cannot get any new lock.
  • This means every transaction will perform 2 phases: getting locks, and then releasing locks.
  • 2PL => conflict serializability

At least one end of each arrow is a ‘Write’

R/W(A)

R/W(A)

R/W(B)

R/W(B)

R/W(C)

R/W(C)

recovering from crashes
Recovering from crashes
  • Up until now we ignored the possibility of a crash of a transaction.
  • To handle such a case we remember Commit and Rollback.
  • Consider this schedule:

Problem: T1 read a value which T2 wrote, and committed. The schedule is called “Not Recoverable”

T1 T2

W(B)

R(A)

W(A)

R(A)

R(B)

R(C)

W(B)

W(C)

R)C(

T2 rolls back

Notice that this schedule is Conflict Serializable!

Crash!!

T1 finished so it commits

recovering from crashes1
Recovering from crashes
  • Solution: Commit only after all transactions which you have read from have committed (assuming your are a transaction).
  • Even more strict solution: Read an item only after all transactions which write this item have committed
  • This leads to a new protocol:
  • Strict 2PL: Same rules as 2PL with the addition that a transaction releases its locks only after it has committed.
  • Strict 2PL ensures recovering. Why?

Good luck!