ACID Transactions: Understanding the Four Properties of Database Transactions, Summaries of Database Programming

Download ACID Transactions: Understanding the Four Properties of Database Transactions and more Summaries Database Programming in PDF only on Docsity!

Chapter 4

Transaction Overview

Controlling Concurrent

Behavior

Chapter Outline

1. Transaction Concept.

2. Four important properties of

transactions.

3. Transaction State

4. Concurrent Executions and schedules

5. Recoverability

6. Lock-Based Concurrency Control

7. Recovering From a Crash

 DBMS handles an execution of a user

program, or transaction, as a series of

reads and writes of database objects:

+To read a database object, it is first

brought into main memory (specifically,

some frame in the buffer pool) from disk,

and then its value is copied into a program

variable.

+To write a database object, an in-memory

copy of the object is first modified and

then written to disk.

1. Transaction concept

(cont’d)

Why Transactions?

 Database systems are normally

being accessed by many users or

processes at the same time.

 Both queries and modifications.

 Unlike operating systems, which

support interaction of processes, a

DMBS needs to keep processes

from troublesome interactions.

1. Transaction concept (cont’d)

2. Four important properties of transactions (ACID Transactions)  (^) ACID transactions are:  (^) Atomic : Whole transaction or none is done. Users should not have to worry about the effect of incomplete transactions.  (^) Consistent : Database constraints preserved. Ensuring this property of a transaction is the responsibility of the user.  (^) Isolated : It appears to the user as if only one process executes at a time.  (^) Durable : a transaction has been successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk.

Example of Fund Transfer  (^) Transaction to transfer $50 from account A to account B :

1. read ( A )
2. A := A – 50
3. write ( A )
4. read ( B )
5. B := B + 50
6. write ( B)  (^) Consistency requirement – the sum of A and B is unchanged by the execution of the transaction.  (^) Atomicity requirement — if the transaction fails after step 3 and before step 6, the system should ensure that its updates are not reflected in the database, else an inconsistency will result.

2. Four important properties of transactions

(cont’d)

3. Transaction State

 (^) Active, the initial state; the transaction stays in this state while it is executing  (^) Partially committed, after the final statement has been executed.  (^) Failed, after the discovery that normal execution can no longer proceed.  (^) Aborted, after the transaction has been rolled back and the database restored to its state prior to the start of the transaction. Two options after it has been aborted:  (^) restart the transaction – only if no internal logical error  (^) kill the transaction  (^) Committed, after successful completion.

State transition diagram

illustrating the states for

transaction execution

 (^) 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.

4. Concurrent Executions & Schedules (cont’d)

Scheduling Transactions

 Serial schedule: Schedule that does not interleave

the actions of different transactions.

 Equivalent schedules : For any database state, the

effect (on the set of objects in the database) of

executing the first schedule is identical to the effect

of executing the second schedule.

 Serializable schedule : A schedule that is equivalent

to some serial execution of the transactions.

(Note: If each transaction preserves consistency, every

serializable schedule preserves consistency. )

4. Concurrent Executions & Schedules (cont’d)

Example Schedule

(Cont’d)

 (^) Let T 1 and^ T 2 be the transactions defined previously_._^ The following schedule (Schedule 2 in the text) is not a serial schedule, but it is equivalent to Schedule 1. In both Schedule 1 and 2, the sum A + B is preserved. T1 T Read(a) A := A – 50 Write(A) Read(B) B:= B+ Write(B) Commit Read(A) A:= A – A*0. Write(A) Read(B) B := B +B 0. Write(B) Commit T1 T Read(a) A := A – 50 Write(A) Read(B) B:= B+ Write(B) Commit Read(A) A:= A – A0. Write(A) Read(B) B := B +B *0. Write(B) Commit

S.1 S.

Example Schedules

(cont’d)

 (^) The following concurrent schedule (Schedule 3 in the text) does not preserve the value of the the sum A + B. T1 T Read(a) A := A – 50 Write(A) Read(B) B:= B+ Write(B) Commit Read(A) A:= A – A*0. Write(A) Read(B) B := B +B 0. Write(B) Commit T1 T Read(a) A := A – 50 Write(A) Read(B) B:= B+ Write(B) Commit Read(A) A:= A – A0. Write(A) Read(B) B := B +B *0. Write(B) Commit

Conflict Serializability  (^) Instructions l i and^ lj of transactions^ Ti and^ Tj respectively, conflict if and only if there exists some item Q accessed by both li and lj , and at least one of these instructions wrote Q.

1. li = read ( Q), lj = read ( Q ). li and lj don’t conflict.
2. li = read ( Q), lj = write ( Q ). They conflict- RW conflict
3. li = write ( Q), lj = read ( Q ). They conflict- WR conflict
4. li = write ( Q), lj = write ( Q ). They conflict- WW conflict  (^) Intuitively, a conflict between l i and^ lj forces a (logical) temporal order between them. If li and lj are consecutive in a schedule and they do not conflict, their results would remain the same even if they had been interchanged in the schedule.

Conflict Serializability(cont’d)

a serial schedule a WR-conflict schedule

T1 T Read(a) A := A – 50 Write(A) Read(B) B:= B+ Write(B) Commit Read(A) A:= A – A*0. Write(A) Read(B) B := B – B 0. Write(B) Commit T1 T Read(a) A := A – 50 Write(A) Read(B) B:= B+ Write(B) Commit Read(A) A:= A – A0. Write(A) Read(B) B := B – B *0. Write(B) Commit