UMBC CMSC 461 Fall '98  CSEE | 461 | 461 F'98 | lectures | news | help 

Lecture 19

Transaction Concept

A transaction is a unit of program execution that accesses and possibly updates various data items.  This is usually from a user-written program within a block, delimited by a set of statements such as, begin transaction and end transaction.  Transactions must maintain database integrity during this updates, so we require the following properties (called the ACID properties): To illustrate a set of two transactions that means the test:

Transaction State

In the absence of failures, all transactions complete successfully.  One that does has a failure does not complete successfully and is said to have aborted.  Any changes that an aborted transaction has made must be removed from the database or rolled back.  Successful transactions must have the changes made permanent or committed.

A simple transaction model has the transaction in one of the five following states:

Concurrent Executions

Transaction-processing systems usually allow multiple transactions to run concurrently, and extra work must be done to allow this.  The reasons for allowing concurrency. The DBMS must use concurrency control to regulate interaction among concurrent transaction to prevent them from destroying the consistency of the database.

An example of an concurrent execution that would produce an incorrect state:
T1 T2
A := A - 50;
temp := A * 0.1; 
A := A - temp; 
write (A); 
B := B + 50; 
B := b + temp; 
The first transaction transfers $50 from the account of person A to the account of person B.  The second one transfers ten percent from the account of person A to the account of person B.  In this example, the ten percent transfer from A is lost and the deposit of $50 to B is lost.  You can prove it to yourself.  The sum of A + B should be the same before and after both transactions.


The two parts of the transactions that we will focus on controlling is the read and write operations.  When managing conflict serializability there are only four cases to consider where there are two transactions processing the same data item: Simply put, any time a transaction  is going to update  a particular item, after the read occurs, the DBMS must prevent another traction from accessing that item until  write operation is completed.


If a transaction Ti fails for any reason, we need to undo the effect of this transaction to ensure the atomicity property of the transaction.  In a system that allows concurrent execution, it is necessary also to ensure that any transaction Tj that is dependent on  Ti (that is, Tj has read data written by Ti ) is also aborted.  This is referred to as cascaded rollback, which undesirable since it leads to the undoing of a significant amount of work.


Schedules that are conflict serializable and cascadeless satisfy the requirements to leave the database in a consistent state.

One scheme for concurrency control is for a transaction to acquire a lock on the entire database before it starts, and releases the lock after it has committed.  While one transactions hold a lock, no other transaction is allowed to acquire the lock and must therefore wait for the lock to be released.  As a result of the locking policy, only one transaction can execute at a time.  Therefore only serial schedules are generated, but this leads to poor performance by forcing transactions to wait until they can acquire a lock.  An improvement on this schema is to limit the lock to a particular tuple or a single attribute of a particular tuple.

Transaction Definition in SQL

The SQL standard specifies the current transaction begins implicitly.  Transactions are ended by one of the following: The keyword work is optional.

The standard also specifies that the system must ensure both serializability and freedom from cascading rollback.  The levels of consistency are:

CSEE | 461 | 461 F'98 | lectures | news | help