Transaction
Download
1 / 44

Transaction - PowerPoint PPT Presentation


  • 143 Views
  • Uploaded on

Transaction. Outline. Transaction and OLTP Desired properties of transactions Schedule Concurrent transactions and their properties. Transaction. A multi-billion dollar business OLTP http://www.tpc.org/default.asp. Transaction Definition.

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 ' Transaction' - heath


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
Transaction

Transaction

Yan Huang - CSCI5330 Database Implementation –Transaction


Outline
Outline

  • Transaction and OLTP

  • Desired properties of transactions

  • Schedule

  • Concurrent transactions and their properties

Yan Huang - CSCI5330 Database Implementation –Transaction


Transaction1
Transaction

  • A multi-billion dollar business

  • OLTP

  • http://www.tpc.org/default.asp

Yan Huang - CSCI5330 Database Implementation –Transaction


Transaction definition
Transaction Definition

  • A unit of program execution that accesses and possibly updates various data items

  • Transactions?

    • Book an airline ticket from DFW to Paris

    • Buy “The Dilbert Principle” from amazon.com

    • Sell 1000 shares of LU from your ameritrade account

    • Withdraw $100 from a ATM machine

    • Issue a SQL statement to sqlplus of Oracle 9i

    • Look at your grade of homework 3

    • Check out your groceries at Walmart

Yan Huang - CSCI5330 Database Implementation –Transaction


Transaction concept
Transaction Concept

  • A transactionis a unit of program execution that accesses and possibly updates various data items.

  • Two main issues to deal with:

    • Failures of various kinds, such as hardware failures and system crashes

    • Concurrent execution of multiple transactions

a1, a2, a3, a4, …, an, commit

consistent

Database may be inconsistent

consistent

a1, a2, a3, a4, …, an, commit

b1, b2, b3, b4, …, bm, commit

c1, c2, c3, c4, …, cl, commit

Yan Huang - CSCI5330 Database Implementation –Transaction


Challenges to maintain transactions
Challenges to Maintain Transactions

  • Hardware failures

    • Cashed stuck in ATM machine

    • Power failure…

  • Software failures

    • Programming errors

    • System crash…

  • User interference

    • Termination of transactions

  • Concurrent users

    • Multiple users accessing the same item

Yan Huang - CSCI5330 Database Implementation –Transaction


Designed properties of database systems
Designed Properties of Database Systems

  • Atomicity

  • Consistency

  • Isolation

  • Durability

Yan Huang - CSCI5330 Database Implementation –Transaction


Atomicity
Atomicity

  • Transaction needs to be executed as a unit

  • Example

    • You should not cause the quantity of “The Dilbert Principle” of amazon.com decrease if you place your order and the order does not get through due to server errors

  • Who are responsible for atomicity?

    • Transaction management system and

    • Recovery system

Yan Huang - CSCI5330 Database Implementation –Transaction


Example of fund transfer
Example of Fund Transfer

  • Transaction to transfer $50 from account A to account B:

    • read(A)

    • A := A – 50

    • write(A)

    • read(B)

    • B := B + 50

    • write(B)

    • commit

Yan Huang - CSCI5330 Database Implementation –Transaction


Atomicity1
Atomicity

  • Either all operations of the transaction are properly reflected

  • Or none are

(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit

(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50

Yan Huang - CSCI5330 Database Implementation –Transaction


Consistency
Consistency

  • Database implicit/explicit constraints need to be maintained

  • Example:

    • Transferring money from one account to another in the same bank should not change your total amount of money

  • Who are responsible for consistency?

    • Transaction management system and

    • Programmer

Yan Huang - CSCI5330 Database Implementation –Transaction


Consistency1
Consistency

  • A+B = TOT where TOT is a constant value

  • Consistency: DB satisfies all integrity and constraints

    • Examples:

      • - x is key of relation R

      • - x  y holds in R

      • - Domain(x) = {Red, Blue, Green}

      • - a is valid index for attribute x of R

      • no employee should make more than twice the average salary

      • A+B = TOT

(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit

A+B= TOT

A+B may not equal to TOT

A+B= TOT

Yan Huang - CSCI5330 Database Implementation –Transaction


Isolation
Isolation

  • Transaction A should not see partial results of transaction B

  • Analogy:

    • When I update my website here and there, you should not see and think a tentative version as my final version

  • Who are responsible for isolation?

    • Transaction management system

Yan Huang - CSCI5330 Database Implementation –Transaction


Isolation1
Isolation

A+B ≠ TOT?!

  • Intermediate transaction results must be hidden from other concurrently executed transactions.

T2

(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit

A+B= TOT

A+B may not equal to TOT

A+B= TOT

Yan Huang - CSCI5330 Database Implementation –Transaction


Durability
Durability

  • Any transaction committed needs to be in database for ever

  • Example:

    • After you get the receipt of the water melon you buy from Alberson, the transaction is final and permanently reflected in the database system

      • If you want to cancel it, that is another transaction

  • Who are responsible for durability?

    • Transaction management system and

    • Recovery system

Yan Huang - CSCI5330 Database Implementation –Transaction


Durability1
Durability

  • After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.

(1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit

After this point, A and B are permanently updated

Yan Huang - CSCI5330 Database Implementation –Transaction


Transaction state cont
Transaction State (Cont.)

a1, a2, a3, a4, …, an, commit

Yan Huang - CSCI5330 Database Implementation –Transaction


An ideal world
An Ideal World

  • No hardware failures

  • No software failures

  • No programming errors

  • Do we still need transaction management?

Yan Huang - CSCI5330 Database Implementation –Transaction


Why concurrent transactions
Why Concurrent Transactions?

  • Parallelism

  • Improved response time

Yan Huang - CSCI5330 Database Implementation –Transaction


Storage hierarchy
Storage Hierarchy

  • Read(x) read x from memory, if it is not in memory yet, read from disk first

  • Write(x) writes x to memory and possibly to disk

  • read(A)

  • A := A – 50

  • write(A)

  • read(B)

  • B := B + 50

  • write(B)

  • commit

x

x

Memory Disk

Yan Huang - CSCI5330 Database Implementation –Transaction


Schedules
Schedules

Schedule 1

Read(A)

A:=A-50

Read(A)

Temp:=A*0.1

A:=A-temp

Write(A)

Read(B)

Write(A)

Read(B)

B:=B+50

Write(B)

B:=B+temp

Write(B)

T1

Read(A)

A:=A-50

Write(A)

Read(B)

B:=B+50

Write(B)

T2

Read(A)

Temp:=A*0.1

A:=A-temp

Write(A)

Read(B)

B:=B+temp

Write(B)

T1 transfer $50 from A to B

T2 transfer 10% of the balance from A to B

Yan Huang - CSCI5330 Database Implementation –Transaction


Schedules1
Schedules

  • Schedules – sequences that indicate the chronological order in which instructions of concurrent transactions are executed

    • a schedule for a set of transactions must consist of all instructions of those transactions

    • must preserve the order in which the instructions appear in each individual transaction.

Yan Huang - CSCI5330 Database Implementation –Transaction


Serial schedule
Serial Schedule

  • T1 is followed by T2.

Schedule 2

Read(A)

A:=A-50

Write(A)

Read(B)

B:=B+50

Write(B)

Read(A)

Temp:=A*0.1

A:=A-temp

Write(A)

Read(B)

B:=B+temp

Write(B)

A = 100, B = 100 originally

A = ? and B = ?

Yan Huang - CSCI5330 Database Implementation –Transaction


Example schedule cont
Example Schedule (Cont.)

  • Schedule 3 is equivalent to Schedule 1.

Schedule 3

Read(A)

A:=A-50

Write(A)

Read(A)

Temp:=A*0.1

A:=A-temp

Write(A)

Read(B)

B:=B+50

Write(B)

Read(B)

B:=B+temp

Write(B)

A = 100, B = 100 originally

In both Schedule 2 and 3, the sum A + B is preserved.

A = ? and B = ?

Yan Huang - CSCI5330 Database Implementation –Transaction


Example schedules cont
Example Schedules (Cont.)

Schedule 4

Read(A)

A:=A-50

Read(A)

Temp:=A*0.1

A:=A-temp

Write(A)

Read(B)

Write(A)

Read(B)

B:=B+50

Write(B)

B:=B+temp

Write(B)

A = 100, B = 100 originally

Schedule 4 does not preserve the sum A + B

A = ? and B = ?

Yan Huang - CSCI5330 Database Implementation –Transaction


Where is the mystery
Where is the mystery?

  • How to preserve database consistency?

Serializability!

Yan Huang - CSCI5330 Database Implementation –Transaction


Serializability
Serializability

  • A (possibly concurrent) schedule is serializable if it is equivalent to a serial schedule.

Yan Huang - CSCI5330 Database Implementation –Transaction


Conflict serializability
Conflict Serializability

  • Transactions T1 and T2

  • Two operations on the same item Q,

  • Intuitively, a conflict between T1 and T2 forces a (logical) temporal order between T1 and T2 .

  • Two consecutive non-conflict operations in a schedule can been interchanged

Conflict?

T1

T2

Yan Huang - CSCI5330 Database Implementation –Transaction


Conflict serializability cont
Conflict Serializability (Cont.)

  • If a schedule S can be transformed into a schedule S´ by a series of swaps of non-conflicting instructions, we say that S and S´ are conflict equivalent.

Yan Huang - CSCI5330 Database Implementation –Transaction


Note

  • Only read and write operations will cause conflict

  • Other operations (A:=A+10) are on local copy variables and do not interface with database

Yan Huang - CSCI5330 Database Implementation –Transaction


Simplified schedules
Simplified Schedules

Schedule 3

Read(A)

A:=A-50

Write(A)

Read(A)

Temp:=A*0.1

A:=A-temp

Write(A)

Read(B)

B:=B+50

Write(B)

Read(B)

B:=B+temp

Write(B)

Schedule 3

Read(A)

Write(A)

Read(A)

Write(A)

Read(B)

Write(B)

Read(B)

Write(B)

Schedule 2

Read(A)

A:=A-50

Write(A)

Read(B)

B:=B+50

Write(B)

Read(A)

Temp:=A*0.1

A:=A-temp

Write(A)

Read(B)

B:=B+temp

Write(B)

Schedule 2

Read(A)

Write(A)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Yan Huang - CSCI5330 Database Implementation –Transaction


Schedule 3 and schedule 2 are conflict equivalent
Schedule 3 and Schedule 2 are conflict equivalent

Schedule 3

Read(A)

Write(A)

Read(A)

Write(A)

Read(B)

Write(B)

Read(B)

Write(B)

Schedule 2

Read(A)

Write(A)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Yan Huang - CSCI5330 Database Implementation –Transaction


Schedule 3 and schedule 2 are conflict equivalent1
Schedule 3 and Schedule 2 are conflict equivalent

Schedule 3

Read(A)

Write(A)

Read(A)

Read(B)

Write(A)

Write(B)

Read(B)

Write(B)

Schedule 2

Read(A)

Write(A)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Yan Huang - CSCI5330 Database Implementation –Transaction


Schedule 3 and schedule 2 are conflict equivalent2
Schedule 3 and Schedule 2 are conflict equivalent

Schedule 3

Read(A)

Write(A)

Read(A)

Read(B)

Write(B)

Write(A)

Read(B)

Write(B)

Schedule 2

Read(A)

Write(A)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Yan Huang - CSCI5330 Database Implementation –Transaction


Schedule 3 and schedule 2 are conflict equivalent3
Schedule 3 and Schedule 2 are conflict equivalent

Schedule 3

Read(A)

Write(A)

Read(B)

Read(A)

Write(B)

Write(A)

Read(B)

Write(B)

Schedule 2

Read(A)

Write(A)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Yan Huang - CSCI5330 Database Implementation –Transaction


Schedule 3 and schedule 2 are conflict equivalent4
Schedule 3 and Schedule 2 are conflict equivalent

Schedule 3

Read(A)

Write(A)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Schedule 2

Read(A)

Write(A)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Yan Huang - CSCI5330 Database Implementation –Transaction


Conflict serializability cont1
Conflict Serializability (Cont.)

  • We say that a schedule S is conflict serializable if it is conflict equivalent to a serial schedule

  • Schedule 3 is conflict serializable

Yan Huang - CSCI5330 Database Implementation –Transaction


Conflict serializability cont2
Conflict Serializability (Cont.)

  • Example of a schedule that is not conflict serializable:

    T3T4read(Q)write(Q)write(Q)We are unable to swap instructions in the above schedule to obtain either the serial schedule < T3, T4 >, or the serial schedule < T4, T3 >.

Yan Huang - CSCI5330 Database Implementation –Transaction


Testing for serializability
Testing for Serializability

  • Precedence graph— a direct graph where the vertices are the transactions (names).

Yan Huang - CSCI5330 Database Implementation –Transaction


Example schedule schedule a
Example Schedule (Schedule A)

T1 T2 T3 T4 T5 read(X)read(Y)read(Z) read(V) read(W) read(W) read(Y) write(Y) write(Z)read(U) read(Y) write(Y) read(Z) write(Z)

read(U)write(U)

Yan Huang - CSCI5330 Database Implementation –Transaction


Precedence graph for schedule a
Precedence Graph for Schedule A

T1

T2

T5

T4

T3

Yan Huang - CSCI5330 Database Implementation –Transaction


Recoverability
Recoverability

  • Only commit after the transaction your read from commits

Yan Huang - CSCI5330 Database Implementation –Transaction


Cascadeless schedule
Cascadeless Schedule

  • Only read committed write

Yan Huang - CSCI5330 Database Implementation –Transaction


Check schedules
Check Schedules

Schedule 4

Read(A)

Read(A)

Write(A)

Read(B)

Write(A)

Read(B)

Write(B)

Write(B)

Schedule 1

Read(A)

Read(A)

Write(A)

Read(B)

Write(A)

Read(B)

Write(B)

Write(B)

Schedule 2

Read(A)

Write(A)

Read(B)

Write(B)

Read(A)

Write(A)

Read(B)

Write(B)

Schedule 3

Read(A)

Write(A)

Read(A)

Write(A)

Read(B)

Write(B)

Read(B)

Write(B)

Conflict serializable?

Recoverable?

Cascadeless?

Yan Huang - CSCI5330 Database Implementation –Transaction


ad