Transaction Schedules in DBMS

Schedules in DBMS, Schedules example, Define schedule, Explain schedules with examples, Definitions of schedules

Schedule

A schedule is a list of operations (read, write, commit, abort, checkpoint, calculation – we mostly consider the read and write operations) that are performed by various concurrently (simultaneous) executing set of transactions. The chronological order of the individual transactions’ instructions must be preserved in a schedule.

When two or more transactions are executing simultaneously, then the steps involved in the transactions are usually interleaved. That means, first few instructions executed from T1 and then from T2, then back to T1 etc. until both T1 and T2 are finished (if only T1 and T2 are executing at that time, for example).

The instructions are interleaved in a schedule because, if one transaction is processing some I/O with disk, the other transactions may use the CPU. They do not need to wait until one transaction completes its execution. This concurrent access improves both the response time and the number of transaction that can be completed in a given time.

Example:

Schedule 1

Transaction T1 Transaction T2 read(A); A := A – 5; write(A); read(B); B := B – 5; write(B); read(A); temp := A * 0.1; A := A – temp; write(A); read(B); B := B + temp; write(B);

In schedule 1, first three instructions of T1 are executed first then the first four instructions of T2 are executed. Again, the next three instructions of T1 are executed then the next three instructions of T2 executed. This happens in coordination with time.

Schedule 2

Transaction T1 Transaction T2 Transaction T3 read(A); A := A – 5; write(A); read(B); B := B + 5; write(B); read(A); temp := A * 0.1; A := A – temp; write(A); read(B); B := B + temp; write(B); read(C); temp1 := C * 0.5; C := C + temp1; write(C); read(D); temp2 := D * 0.5; D := D + temp1; write(D);

In schedule 2, three instructions T1, T2 and T3 are executed in an interleaved fashion. Here, the execution order is T3àT1àT2àT3àT1àT2.

Other definitions:

• When transactions are executing concurrently in an interleaved fashion, then the order of execution of operations from all the various transactions is known as a schedule (or history) – Fundamentals of Database Systems by Elmasri and Navathe.

• A schedule is a list of actions (reading, writing, aborting, or committing) from a set of transactions, and the order in which two actions of a transaction T appear in a schedule must be the same as the order in which they appear in T – Database Management Systems by Raghu Ramakrishnan and Gehrke.

• A particular sequencing (usually interleaved) of the actions (reading and writing) of a set of transations is called a schedule (or history). The order of actions of every transaction in a schedule must always be the same as they appear in the transaction – Database Management Systems by G K Gupta.

*******************

Go to Transaction Management in DBMS home page

Database Transaction States in DBMS

Database transaction states in DBMS, different states a transaction can go through in DBMS, Active, Partially committed, Committed, Failed, and Aborted states, Various transaction states, explain various transaction states in dbms with example

Transaction States

A transaction is a single logical unit of work that must be completed entirely (all the instructions from begin_transaction to commit) or must be aborted if not able to complete all the instructions successfully. During its execution a transaction goes through many different states. The following state transition diagram depicts the various states a single transaction has to go through.

 

State Transition Diagram of states of transaction execution

 

During its execution a transaction must be in any one of the following states;

• Active state – Initial state.

Any transaction will be in this state during its execution. Once the transaction starts executing from the first instruction begin_transaction, the transaction will be considered in active state. During this state it performs operations READ and WRITE on some data items.

From active state, a transaction can go into one of two states, a partially committed state or a failed state.

• Partially committed state – After the execution of final statement.

This is the state of a transaction that successfully executing its last instruction. That means, if an active transaction reaches and executes the COMMIT statement, then the transaction is said to be in partially committed state.

From partially committed state, a transaction can go into one of two states, a committed state or a failed state.

• Committed – After successful completion of transaction.

At partially committed state the database recovery system will perform certain actions to ensure that a failure at this stage should not cause lose of any updates made by the executing transaction. If the current transaction passed this check, then the transaction reaches committed state.

From committed state, a transaction can go into terminated state.

• Failed – If any failure occurs.

While a transaction is in the active state or in the partially committed state, the issues like transaction failure, user aborting the transaction, concurrency control issues, or any other failure, would happen. If any of these issues are raised, then the execution of the transaction can no longer proceed. At this stage a transaction will go into a failed state.

From failed state, a transaction can go into only aborted state.

• Aborted – After rolled back to the old consistent state.

After the failed state, all the changes made by the transaction has to be rolled back and the database has to be restored to its state prior to the start of the transaction. If these actions are completed by the DBMS then the transaction considered to be in aborted state.

From aborted state, a transaction can go into terminated state.

Example:

Let us assume a transaction T1 that transfers money from account A to account B. Before transaction let A = 1000 and B = 1000. The transaction can be represented as follows;

Transaction T1

BEGIN TRANSACTION READ(A); A := A - 500; WRITE(A); READ(B); B := B + 500; WRITE(B);           COMMIT; END TRANSACTION;

• ACTIVE - When the transaction T1 starts, i.e., enters BEGIN TRANSACTION, the transaction in ACTIVE state. From BEGIN TRANSACTION and COMMIT, the transaction is in ACTIVE state only.

• In ACTIVE state, A = 1000 and B = 1000.

• PARTIALLY COMMITTED - If T1 reaches the instruction COMMIT, the transaction goes into PARTIALLY COMMITTED state.

• In PARTIALLY COMMITTED state, A = 500 and B = 1500;

• FAILED – It happens if one of the following occurs;
•  If any failures happen to the transaction in ACTIVE state (refer type of failures) then the transaction goes into FAILED state.

• If the transaction failed before WRITE(A), then A = 1000 and B = 1000. 
• If the transaction failed after WRITE(A), then A = 500 and B = 1000. 
• If the transaction failed before COMMIT and after WRITE(B), then A = 500 and B = 1500.

• If any failures happen to the transaction in PARTIALLY COMMITTED state (refer type of failures) then the transaction goes into FAILED state.

• If the transaction goes into FAILED state from PARTIALLY COMMITTED state, then A = 500 and B = 1500.

• ABORTED - The transaction in ABORTED state has to undo the changes made so far. That is the old consistent values of data items A and B are restored.

• In ABORTED state, A = 1000 and B = 1000. [A and B are rolled back to Old consistent state]

• COMMITTED - If the transaction executes COMMIT successfully, that is, if it successfully writes the new value of A and B into log file or stable storage, then the transaction is said to be in COMMITTED state.

• In COMMITTED state, A = 500 and B = 1500. [New consistent state]

********************

 Go back to Transaction Management in DBMS home page