Transaction Processing

Transaction:

Action, or series of actions, carried out by user or application, which accesses or changes contents of database. It Transforms database from one consistent state to another, although consistency may be violated during transaction

Logical unit of database processing that includes one or more access operations (read -retrieval, write - insert or update, delete).

Transaction is an executing program forming a logical unit of database access operations that involves one or more database operations (read -retrieval, write - insert or update, delete).

A transaction may be stand-alone set of operations specified in a high level language like SQL submitted interactively, or may be embedded within a program.

A transaction must see a consistent database.

During transaction execution the database may be inconsistent.

When the transaction is committed, the database must be consistent.

Transaction boundaries:

Begin and End transaction.

An application program may contain several transactions separated by the Begin and End transaction boundaries.

Two Basic operations are read and write

1)    read_item(X): Reads a database item named X into a program variable. To simplify our notation, we assume that the program variable is also named X.

2)    write_item(X): Writes the value of program variable X into the database item named X.

Why Do We Need Transactions?

It’s  all  about  fast  query  response  time  and  correctness

DBMS is a multi-user systems

^§        Many different requests

^§        Some against same data items

^v        Figure out how to interleave requests to shorten response time while guaranteeing correct result

How does DBMS know which actions belong together?

Group database operations that must be performed together into transactions

Either execute all operations or none

READ AND WRITE OPERATIONS:

Basic unit of data transfer from the disk to the computer main memory is one block. In general, a data item (what is read or written) will be the field of some record in the database, although it may be a larger unit such as a record or even a whole block.

read_item(X) command includes the following steps:

1.     Find the address of the disk block that contains item X.

2.     Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer).

3.     Copy item X from the buffer to the program variable named X.

write_item(X) command includes the following steps:

1.     Find the address of the disk block that contains item X.

2.     Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer).

3.     Copy item X from the program variable named X into its correct location in the buffer.

4.     Store the updated block from the buffer back to disk (either immediately or at some later point in time

Example Transactions:

Issues to deal with:

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

o Concurrent execution of multiple transactions

A transaction is an atomic unit of work that is either completed in its entirety or not done at all. For recovery purposes, the system needs to keep track of when the transaction starts, terminates, and commits or aborts.

Transaction States

A transaction can be in one of several states:

Active - Reading and Writing data items ,if something wrong happens during reading and writing aborts to Failed.

Partially Committed - All reading and writing operations are done aborts to Failed when rollback occurs or committed when commit occurs.

Committed - Transaction successfully completed and all write operations made permanent in the Database

Failed - Transaction halted and all operations rolled back

Terminated - terminates either commits or failed

ACID Properties

To preserve the integrity of data, the database system must ensure:

Atomicity. Either all operations of the transaction are properly reflected in the database or none are.

Consistency. Execution of a transaction in isolation preserves the consistency of the database.

Isolation. Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transactions. That is, 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.

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

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)

Atomicity requirement :

1.     if the transaction fails after step 3 and inconsistent database state

2.     The system should ensure that updates of a partially executed transaction are not reflected in the database

Durability requirement :

once the user has been notified that the transaction has completed (i.e., the transfer of the $50 has taken place), the updates to the database by the transaction must persist even if there are software or hardware failures.

Consistency requirement :

^ the sum of A and B is unchanged by the execution of the transaction

In general, consistency requirements include

Explicitly specified integrity constraints such as primary keys and foreign keys

Implicit integrity constraints

A transaction must see a consistent database.

During transaction execution the database may be temporarily inconsistent.

When the transaction completes successfully the database must be consistent

^ Erroneous transaction logic can lead to inconsistency

Isolation requirement :

if between steps 3 and 6, another transaction T2 is allowed to access the partially updated database, it will see an inconsistent database (the sum A+B will be less than it should be). Isolation can be ensured trivially by running transactions serially, that is, one after the other. However, executing multiple transactions concurrently has significant benefits.

Transaction states:

Active state

Partially committed state

Committed state

Failed state

Terminated State

^ Recovery manager keeps track of the following operations:

o  begin_transaction: This marks the beginning of transaction execution.

o       read or write: These specify read or write operations on the database items that are executed as part of a transaction.

o       end_transaction: This specifies that read and write transaction operations have ended and marks the end limit of transaction execution.

p       At this point it may be necessary to check whether the changes introduced by the transaction can be permanently applied to the database or whether the transaction has to be aborted because it violates concurrency control or for some other reason

q       commit_transaction: This signals a successful end of the transaction so that any changes (updates) executed by the transaction can be safely committed to the database and will not be undone.

o       rollback (or abort): This signals that the transaction has ended unsuccessfully, so that any changes or effects that the transaction may have applied to the database must be undone.

ⁿ        Recovery techniques use the following operators:

o       undo: Similar to rollback except that it applies to a single operation rather than to a whole transaction.

o       redo: This specifies that certain transaction operations must be redone to ensure that all the operations of a committed transaction have been applied successfully to the database.

Two Main Techniques

Deferred Update

No physical updates to db until after a transaction commits.

During the commit, log records are made then changes are made permanent on disk. What if a Transaction fails?

No UNDO required

REDO may be necessary if the changes have not yet been made permanent before the

failure

Immediate Update

Physical updates to db may happen before a transaction commits.

All changes are written to the permanent log (on disk) before changes are made to the DB. What if a Transaction fails?

After changes are made but before commit –need to UNDO the changes

REDO may be necessary if the changes have not yet been made permanent before the

failure

Recovery based on Deferred Update

Deferred update

Changes are made in memory and after T commits, the changes are made permanent on disk.

Changes  are  recorded  in  buffers  and  in  log  file

At the commit point, the log is force-written to disk and updates are made in database.

No need to ever UNDO operations because changes are never made permanent

REDO is needed if the transaction fails after the commit but before the changes are made on disk.

Hence the name is NO UNDO/REDO algorithm

2 lists are maintained:

Commit list: committed transactions since last checkpoint

Active list: active transactions

REDO all write operations from commit list in order that they were written to the log

Active transactions are cancelled & must be resubmitted

Recovery based on Immediate Update

Immediate update:

Updates to disk can happen at any time

But, updates must still first be recorded in the system logs (on disk) before changes are made to database.

Need to provide facilities to UNDO operations which have affected the db

2 flavors of this algorithm UNDO/NO_REDO recovery algorithm

if the recovery technique ensures that all updates are made to the database on disk before T commits, we do not need to REDO any committed transactions

UNDO/REDO recovery algorithm

Transaction is allowed to commit before all its changes are written to the database o (Note that the log files would be complete at the commit point)