3/18/2012

Transactions

Contents
Transaction Concept Transaction State

1. Introduction To transaction Processing 1.1 Single User VS Multi User Systems

One criteria to classify Database is according to number of user that concurrently connect to the system.
Single User: only one user use the system in each time Multi User: many users use the system in the same time

3/18/2012

Example of transaction
Let Ti be a transaction that transfer money from account A (5000) to account B. The transaction can be defined as
T i: read (A) A := A 5000 write (A); read (B) B := B + 5000 write (B) (withdraw from A) (update A) (deposit B) (update B)

Why recovery is needed?

Transaction submitted for execution DBMS is responsible for making sure that either
All operations in transaction are completed successfully and the changes is recorded permanently in the database. The DBMS must not permit some operations of a transaction T to be applied to the DB while others of T are not. (will cause inconsistency)

Failures Type
Generally classified as Transaction Failure System Failure Media Failure

3/18/2012

A computer failure (System crash) media failures A transaction or system error: logical program
error

Reasons for a transaction fails in the middle of execution

Load error or exception conditions detected by the transaction : no data for the transaction Concurrency control enforcement: by concurrency
control method

Disk failure Physical problems and catastrophes: ex. Power failure, fire, overwrite disk

Transaction or system error

Some operation in transaction may cause it to fail, such as integer overflow or divide by zero. May occur because of erroneous parameter values Logical programming User may interrupt during execution

Local error or exception conditions detected by transaction

During transaction execution, conditions may occur that necessitate cancellation of the transaction Ex. Data for the transaction may not found Exception should be programmed (not be consider failure)

3/18/2012

Concurrency control enforcement

The concurrency control method may decide to abort the transaction,
Because of violent serializability Because several transaction are in state of deadlock

Transaction states and additional Operation

For recovery purpose, the system needs to keep track of when the transaction
starts, terminates, and commits or aborts.

What information that the recovery manager keep track?

The recovery manager keep track of the followings

Transaction states and additional Operation

Begin_transaction: mark the beginning of transaction execute Read or write: specified operations on the database item that executes as part of transaction End_transaction: specifies that operations have ended and marks the end of execution (Necessary to check)

The change can be committed Or whether the transaction has to aborted

Commit_Transaction: successful end (will not undo) Rollback: unsuccessful end (undone)

3/18/2012

State of transaction
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 backed and the database has been restored to its state prior to the start of transaction. Committed, after successful completion

State diagram of a transition

Read Write Begin Transaction Commit

Partially Committed

committed

Abort Active Aborted

(Terminate)

Failed

A transaction must be in one of these states.

The transaction has committed only if it has entered the committed state. The transaction has aborted only if it has entered the aborted state. The transaction is said to have terminated if has either committed or aborted.

3/18/2012

The System Log

The system maintain log by keep track of all transactions that effect the database. Log is kept on Disk. Effected only by disk or catastrophic failure Keep Log records

Log records
T is transaction ID (Start_transaction, T) start transaction (Write_item, T, X, old_value, new_value) transaction write/update item x (Read_item, T, X) transaction read item X (Commit, T) complete operation of T (Abort, T) terminate transaction T

System Log (cont.)

Log file keep track
start transaction complete transaction

System fail occur Restart system, system will recovery

Redo transaction that already commit Undo no commit transaction

3/18/2012

Commit point of a transaction

When is the transaction T reach its commit point? Answer is when all its operations that access the database have been executed successfully and the effect of all the transaction operations on the database have been recorded in the log. The transaction is said to be committed

(Cont.)
At committed point
Write [commit] in log file

Failure occur
Search in log file looking for all transactions T, that have write [Start_Transaction ,T] If no commit, Rollback transaction If commit found, Redo transaction

Desirable properties of transaction : ACID properties

To ensure integrity of data, we require that the database system maintain the following properties of the transactions:
Atomicity. Consistency preservation. Isolation. Durability or permanency.

3/18/2012

ACID
Atomicity. Either all operations of the transaction are reflected properly in the database, or none are. Consistency. Execution of a transaction in isolations (that is, with no other transaction executing concurrently)

Isolation. Even though multiple transactions may execute concurrently, the system guarantees that, for every pair of transactions Ti and Tj, it appears to Ti that
either Tj finished execution before Ti started, or Tj started execution after Ti finished. Thus, each transaction is unaware of other transactions executing concurrently in the system.

Durability. After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.
(The changes must not be lost because of any failure)

Atomicity
Atomicity. Either all operations of the transaction are reflected properly in the database, or none are. State before the execution of transaction Ti
The value of A = 50,000 The value of B = 100

Failure occur (ex. Hardware failure)

Failure happen after the WRITE(A) operation (at this moment A = 50000 5000 = 45000) And the value of B = 100 (inconsistency state) In consistency state A = 45000 and B = (5100)

3/18/2012

(cont.)
Idea behind ensuring atomicity is following:
The database system keeps track of the old values of any data on which a transaction performs a write If the transaction does not complete, the DBMS restores the old values to make it appear as though the transaction have never execute.

Consistency
Consistency. The consistency requirement here is that the sum of A and B be unchanged by the execution of the transaction. Without consistency requirement, money could be created or destroyed by the transaction. It can be verified,
If the database is consistency before an execution of the transaction, the database remains consistent after the execution of the transaction.

Durability or permanency
Durability or permanency. After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.
These changes must not be lost because of any failure ensures that, transaction has been committed, that transactions updates do not get lost, even if there is a system failure

3/18/2012

Isolation
Isolation. Even though multiple transactions may execute concurrently, the system guarantees that,

for every pair of transactions Ti and Tj, it appears to Ti that either Tj finished execution before Ti started, or Tj started execution after Ti finished. Thus, each transaction is unaware of other transactions executing concurrently in the system.

( Execution of transaction should not be interfered with by any other transactions executing concurrently )

Concurrent Executions
Transaction processing permit
Multiple transactions to run concurrently. Multiple transactions to update data concurrently

Cause
Complications with consistency of data

Reason for allowing concurrency

Improved throughput of transactions and system resource utilization Reduced waiting time of transactions

10

3/18/2012

Possible Problems
Lost update problem Temporary update problem Incorrect summary problem

Example transaction
Transfer money from account A to B
Read_item(A) A := A 50 Write_item(A) Read_item(B) B := B + 50 Write_item(B)

Transfer 10% of A to Account B

Read_item(A) temp := 0.1*A A:= A-temp Write_item(A) Read_item(B) B := B + temp Write_item(B)

A = 1000, Lost update problem B =2000

T1 Read_item(A) A = 1000 A := A 50 A = 950

T2

A = 950 Read_item(A) temp = 95 temp := 0.1*A A:= A-temp A=950-95

Write_item(A) A = 950 Read_item(B) B = 2000 Write_item(A) Read_item(B)

B = 2050 B := B + 50 Write_item(B) B = 2050

= 855

A = 855 B = 2000

B := B + temp B = 2095 Write_item(B) B = 2095

11

3/18/2012

Temporary update problem

R = 3000

T1 Read_item(R) R = 1000

T2 Write_item(R) RollBack
R = 3000 R = 1000

Inconsistency problem
A = 40 , B = 50, C = 30

T1 A = 40 Read_item(A) SUM = Sum+A Sum = 40 Read_item(B) B = 50 SUM = A + B

SUM = 40+50 = 90

T2
A+B+C = 40+50+30 = 120

After A+B+C = 50+50+20 = 120

A = 40+10 =50

Read_item(C) C = 30 C = C - 10 C = 30-10 =20 Write_item(C) C = 20 Read_item(A) A = 40 A = A + 10 Write_item(A) A = 50 COMMIT

Read_item(C) C = 20 SUM = SUM + C Sum = 90 + 20 = 110

Contents
Transaction control in oracle Data Concurrency and Consistency in a Multiuser Environment Locking

12

3/18/2012

Database Transaction
A database transaction consists of one of the following: DML statements which constitute one consistent change to the data One DDL statement One DCL statement

Oracle Transaction Types

Type Data manipulation language (DML) Description
Consists of any number of DML statements that the Oracle server treats as a single entity or a logical unit of work Consists of only one DDL statement

Data definition language (DDL) Data control language (DCL)

Consists of only one DCL statement

Transaction boundaries
A transaction one of the following begins with the events: first executable A COMMIT or ROLLBACK SQL statement. statement is issued
A DDL or DCL statement executes (automatic commit) The user exits iSQL*Plus The system crashes A transaction ends with

13

3/18/2012

Advantages of COMMIT and ROLLBACK

With COMMIT and ROLLBACK statements, you can: Ensure data consistency Preview data changes before making changes permanent Group logically related operations

Controlling transaction

COMMIT transaction
Before COMMIT generated rollback segment records in buffers in the SGA generated redo log entries in the redo log buffer of the SGA. The changes have been made to the database buffers of the SGA. After COMMIT The internal transaction table for the associated rollback segment records updated with SCN LGWR writes SGA redo log entries to the online redo log file Oracle releases locks Oracle marks the transaction complete.

14

3/18/2012

ROLLBACK transaction
ROLLBACK Oracle undoes all transaction changes using the undo tablespace or rollback segments Oracle releases all the transactions locks of data The transaction ends ROLLBACK to SAVEPOINT Oracle rolls back only the statements run after the savepoint. Oracle preserves the specified savepoint, but all savepoints that were established after the specified one are lost Oracle releases all table and row locks acquired since that savepoint

State of the Data Before COMMIT or ROLLBACK The previous state of the data can be recovered. The current user can review the results of the DML operations by using the SELECT statement. Other users can not view the results of the DML statements by the current user. The affected rows are locked Other users cannot change the data within the affected rows.

State of the Data after COMMIT Data changes are made permanent in the database. The previous state of the data is permanently lost. All users can view the results. Locks on the affected rows are released; those rows are available for other users to manipulate. All savepoints are erased.

15

3/18/2012

Distributed database
Distributed transaction is a transaction that includes one or more statements that update data on two or more distinct nodes of a distributed database A two-phase commit mechanism guarantees the data consistent in all nodes.

Autonomous transactions Autonomous transactions are independent transactions that can be called from within another transaction An autonomous transaction lets you leave the context of the calling transaction You can call autonomous transactions from within a PL/SQL block by using the pragma AUTONOMOUS_TRANSACTION.

Data Concurrency and Consistency Data concurrency means that many users can access data at the same time. Data consistency means that each user sees a consistent view of the data, including visible changes made by the users own transactions and transactions of other users.

16

3/18/2012

The isolation models prevents

Dirty reads Nonrepeatable (fuzzy) reads

Phantom reads

Isolation levels (SQL92) controls

Isolation Level Read uncommitted Read committed Repeatable read Serializable Dirty Read Y Nonrepeatable Read Y Phantom Read Y

N N N

Y N N

Y Y N

Oracle isolation levels

Read committed Each query executed by a transaction sees only data that was committed before the query began (Oracle default isolation level) Serializable transactions see only those changes that were committed at the time the transaction began, plus its own changes The transaction sees only those changes that were committed at the time the transaction began and do not allow any DML statement

Serializable

Read-only

17

3/18/2012

Multiversion Concurrency Control Statement-level read consistency The data returned by a single query comes from a single point in time the time that the query began Transaction-level read consistency When a transaction executes in serializable mode, all data accesses reflect the state of the database as of the time the transaction began

Transactions and Read Consistency

Snapshot too old

When commit or rollback has been executed, the pre-images can be overwritten even if they are needed to provide a read-consistent view to another query. "Snapshot too old" simply means that preimages which the query needs to maintain a read-consistent view have been overwritten.

18

3/18/2012

Common recommendations
Common recommendations to reduce the possibility of "snapshot too old" are: Keep transactions as fast as possible Increase the size/number of rollback segments Do not specify an OPTIMAL size for your rollback segments. Increase the size of UNDO_RETENTION parameter
(amount of committed undo information to retain in the database)

Avoid executing long-running queries when transactions which update the table are also executing.

Set the Isolation Level

You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction: SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL READ ONLY;

Serializable Transaction Failure

19

3/18/2012

Modes of Locking Exclusive lock The mode prevents the associates resource from being shared Share lock The mode allows the associated resource to be shared, depending on the operations involved

Deadlock

Types of Locks
Lock DML locks (data locks) Description DML locks protect data

For example, table locks lock entire tables, rowlocks lock selected rows. DDL locks (dictionary locks) DDL locks protect the structure of schema objects Internal locks and latches Internal locks and latches protect internal database structures such as datafiles

20

3/18/2012

Table Locks
RS: row share RX: row exclusive S: share SRX: share row exclusive X: exclusive

Data Lock Escalation

A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock conversion is required or performed. Oracle automatically converts a table lock of lower restrictiveness to one of higher restrictiveness as appropriate

MS SQL Isolation Levels

Microsoft SQL Server 2005
SELECT ... WITH (UPDLOCK) READ COMMITTED with snapshots SNAPSHOT SNAPSHOT READ UNCOMMITTED READ COMMITTED with locking REPEATABLE READ SERIALIZABLE

Oracle
SELECT... FOR UPDATE READ COMMITTED SERIALIZABLE READ ONLY No Equivalent No Equivalent No Equivalent No Equivalent

21

3/18/2012

DB2 Isolation Levels

Isolation levels
Repeatable Read (RR) Read Stability (RS) Cursor Stability (CS) Uncommitted Read (UR)

Levels of locking
Tablespace Table Row

22