Page 170 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 9: Transaction Management

9.14.2 Weak Levels of Consistency in SQL Notes

SQL allows non-serialisable executions:
1. Serialisable is the default.
2. Repeatable read allows only committed records to be read, and repeating a read should
return the same value (so read locks should be retained). (However, the phantom
phenomenon need not be prevented) that is, T may see some records inserted by T , but
1 2
may not see others inserted by T .
2
3. Read committed same as degree two consistency, but most systems implement it as cursor-
stability.
4. Read uncommitted allows even uncommitted data to be read. This is the level, which has
almost no restriction on concurrency but will result in all sorts of concurrency related
problems.

Task Use of wound-wait scheme.

Lab Exercise Create a table and apply implicit transactions on that.

9.15 Summary

 As transaction is the smallest unit of a work.

 It plays an important role in a Database Management System.
 In this unit we cover Properties of a transaction, Basic operations on transaction, State of
transaction etc. and cover Concepts of Concurrency Control and various problems which
occur on Concurrency such as Lost Updata problem, Dirty read problem etc. and cover
concepts about serializability along with a technique to find whether given transaction
are serial or not.

9.16 Keywords

A transaction: This is a unit of program execution that accesses and possibly updates various
data items.
Aborted state: This transaction state occurs after the transaction has been rolled back and the
database has been restored to its state prior to the start of the transaction.

Active state: This is the initial state of a transaction, the transaction stays in this state while it is
starting execution.
Committed state: This transaction state occurs after the successful completion of the transaction.

Complete schedule: A schedule that contains either an abort or a commit statement for each
transaction whose actions are listed in schedule, then it is called a complete schedule. A complete
transaction must also contain all the actions of every transaction that appears in it.

Partially committed state: This transaction state occurs after the final (last) statement of the
transaction has been executed.

LOVELY PROFESSIONAL UNIVERSITY 163