MODULE – 4

CHAPTER-11

TRANSACTION
PROCESSING
Transaction processing:
The concept of transaction provides a mechanism for describing logicalunits of
database processing.
Transaction processing systems are systems with large databases andhundreds
of concurrent users executing database transactions.
Examples of such systems include airline reservations, banking, credit cardprocessing,
online retail purchasing, stock markets, supermarket checkouts, and many other
applications.
These systems require high availability and fast response time forhundreds

of concurrent users.

Introduction to Transaction Processing:

DBMS is classified according to the number of users who can use the systemconcurrently that
is,

1. Single user

2. Multiuser.

Single - User:

o At most one user at a time can use the system.

o Single-user DBMSs are mostly restricted to personal computer systems.

Multi-User:

o Many users can use the system and access the database concurrently.
o Database systems used in banks, insurance agencies, stock exchanges, supermarkets,
airline agencies and many other applications are multiusersystems.
o Multiple users can access databases and use computer system simultaneously
because of the concept of multiprogramming (to executemultiple programs or
processes at the same time).
o In a multiuser DBMS, the stored data items are the primary resources that may be
accessed concurrently by users or programs, which are constantly retrieving
information from and modifying the database.
Interleaved processing versus parallel processing of concurrent
transactions:
o Multiprogramming operating systems execute some commands from one process,
then suspend that process and execute some commands from thenext process, and so
on. A process is resumed at the point where it was suspended whenever it gets its turn
to use the CPU again. Hence, concurrent execution of processes is actually interleaved.
Interleaving prevents a long process from delaying other processes.

o If the computer system has multiple hardware processors (CPUs), parallelprocessing

of multiple processes is possible.

For example,

o Two processes, A and B, executing concurrently in an interleaved fashion.Interleaving

keeps the CPU busy when a process requires an input or output (I/O) operation,
such as reading a block from disk. The CPU is switched to execute another process
rather than remaining idle during I/O time.
o Parallel processing of multiple processes is illustrated by processes C andD using
multiple CPUs.
Transactions, Database Items, Read and Write Operations, and
DBMS Buffers:-
Transactions:
A transaction is an executing program that forms a logical unit of databaseprocessing.
A transaction includes one or more database access operations, they are,insertion,
deletion, modification (update), or retrieval operations.
The database operations that form a transaction can either be embeddedwithin an
application program or they can be specified interactively via a high-level query
language such as SQL.
One way of specifying the transaction boundaries is by specifying explicit begin
transaction and end transaction statements.
If the database operations in a transaction do not update the database butonly retrieve
data, the transaction is called a read-only transaction; otherwise it is known as a read-

write transaction.

Database items:

A database is basically represented as a collection of named data items.The size of

a data item is called its granularity.
A data item can be a database record, but it can also be a larger unit such as a
whole disk block, or even a smaller unit such as an individualfield (attribute) value
of some record in the database.
Each data item has a unique name, used to uniquely identify each dataitem.
For example, if the data item granularity is one disk block, then the diskblock address
can be used as the data item name. If the item granularityis a single record, then the
record ID can be the item name.
Read and write operations:

read_item(X)-Reads a database item named X into a program variable. Tosimplify our

notation, we assume that the program variable is also namedX.
write_item(X)-Writes the value of program variable X into the databaseitem
named X.

Executing a 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 isnot already
in some main memory buffer). The size of the buffer is the same as the disk block
size.

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

Executing a 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 isnot already
in some main memory buffer).

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

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

A transaction includes read_item and write_item operations to access andupdate the

database.
Buffers:

Sometimes the buffer is not immediately stored to disk, in case additionalchanges are
to be made to the buffer.
The decision about when to store a modified disk block whose contentsare in a main
memory buffer is handled by the recovery manager of theDBMS in cooperation with
the underlying operating system.
The DBMS will maintain in the database cache a number of data buffers inmain
memory.
Each buffer typically holds the contents of one database disk block, which contains
some of the database items being processed. When these buffersare all occupied, and
additional database disk blocks must be copied into memory, some buffer replacement
policy is used that is LRU(least recentlyused).If the chosen buffer has been modified, it
must be written back to disk before it is reused.

Why Concurrency Control Is Needed:

• Several problems can occur when concurrent transactions execute in an uncontrolled
manner.
• We illustrate some of these problems by referring to a much simplified airline reservations
database in which a record is stored for each airline flight. Each record includes the
number of reserved seats on that flight as a named (uniquely identifiable) data item,
among other information. Figure 20.2(a) shows a transaction T1 that transfers N
reservations from one flight whose number of reserved seats is stored in the database
item named X to another flight whose number of reserved seats is stored in the database
item named Y. Figure 20.2(b) shows a simpler transaction T2 that just reserves M seats on
the first flight (X) referenced in transaction T1.2
• We do not show additional portions of the transactions, such as checking whether a flight
has enough seats available before reserving additional seats. When a database access
program is written, it has the flight number, date, and number of seats to be booked as
parameters; hence, the same program can be used to execute many different
transactions, each with a different flight number, date, and number of seats to be booked.

• For concurrency control purposes, a transaction is a particular execution of a program on

a specific date, flight, and number of seats.
In Figures 20.2(a) and (b), the
transactions T1 and T2 are specific
executions of the programs that refer to
the specific flights whose numbers of
seats are stored in data items X and Y in
the database.

Several problems can occur when concurrent transactions execute in anuncontrolled

manner. We illustrate some of these problems they are,

1. The Lost Update Problem:

This problem occurs when two transactions that access the same
database items have their operations interleaved in a way that makesthe value of
some database items incorrect. Suppose that transactions T1 and T2 are submitted
at the same time, and their operations are interleaved as shown in Figure(a); then
the final value of item X is incorrect because T2 reads the value of X before T1
changes it in the database, and hence the updated value resulting from T1 is lost.

For example,

If X = 80 at the start (originally there were 80 reservations on the flight), N = 5 (T1 transfers 5 seat reservations from the
flight corresponding to X to the flight corresponding to Y), and M = 4 (T2 reserves 4 seats on X), the final result should be X
= 79. However, in the interleaving of operations shown in Figure (a), it is X = 84 because the update in T1 that removed the
five seats from X was lost
2. The Temporary Update (or Dirty Read) Problem:
This problem occurs when one transaction updates a database item
and then the transaction fails for some reason. Meanwhile, the updated item
is accessed (read) by another transaction before it ischanged back (or rolled
back) to its original value.

Figure (b) shows an example where T1 updates item X and then fails before completion, so the system must roll back X to
its original value. Before it can do so, however, transaction T2 reads the temporary value of X, which will not be recorded
permanently in the database because of the failure of T1. The value of item X that is read by T2 is called dirty data because
it has been created by a transaction that has not completed and committed yet; hence, this problem is also known as the
dirty read problem.

3. The Incorrect Summary Problem:

If one transaction is calculating an aggregate summary function on a
number of database items while other transactions are updating someof these
items, the aggregate function may calculate some values before they are updated
and others after they are updated.
For example,
suppose that a transaction T3 is calculating the total number of reservations on all the flights;
meanwhile, transaction T1 is executing. If the interleaving of operations shown in Figure 20.3(c) occurs,
the result of T3 will be off by an amount N because T3 reads the value of X after N seats have been
subtracted from it but reads the value of Y before those N seats have been added to it.

4.The Unrepeatable Read Problem:

Another problem that may occur is called unrepeatable read, where a
transaction T reads the same item twice and the item is
changed by another transaction T′ between the two reads. Hence, T receives
different values for its two reads of the same item. This may occur, for example, if
during an airline reservation transaction, a customer inquires about seat
availability on several flights. When the customer decides on a particular flight, the
transaction then reads thenumber of seats on that flight a second time before
completing the reservation, and it may end up reading a different value for the
item.
Why Recovery Is Needed:-

Whenever a transaction is submitted to a DBMS for execution, the systemis

responsible for making sure that either all the operations in the transaction are
completed successfully, then the transaction is said to be committed.
And their effect is recorded permanently in the database, or that thetransaction does
not have any effect on the database or any other transactions, and then the transaction
is aborted.
If a transaction fails after executing some of its operations but before executing all of
them, the operations already executed must be undoneand have no lasting effect.

Types of Failures:-
Failures are generally classified as transaction, system, and media failures. Thereare several
possible reasons for a transaction to fail in the middle of execution:

1. A computer failure (system crash)-

A hardware, software, or network error occurs in the computer system during transaction

execution. Hardware crashes are usually media failures for example,main memory failure.

2. A transaction or system error-

Some operation in the transaction may cause it to fail, such as integer overflowor division by
zero, erroneous parameter values or because of a logical programming error. Additionally, the
user may interrupt the transaction duringits execution.

3. Local errors or exception conditions detected by the transaction-

During transaction execution, certain conditions may occur that necessitate cancellation of
the transaction. For example, data for the transaction may not befound. An exception
condition, such as insufficient account balance in a bankingdatabase, may cause a
transaction, such as a fund withdrawal, to be canceled.
This exception could be programmed in the transaction itself, and in such a casewould not be
considered as a transaction failure.
.
4. Concurrency control enforcement-
The concurrency control method may abort a transaction because it violates
serializability, or it may abort one or more transactions to resolve a state of deadlock
among several transactions. Transactions aborted because of serializability violations or
deadlocks are typically restarted automatically at alater time.

5.Disk failure-
Some disk blocks may lose their data because of a read or write malfunction orbecause of a
disk read/write head crash. This may happen during a read or a write operation of the
transaction.
6.Physical problems and catastrophes-
This refers to an endless list of problems that includes power or air- conditioning failure, fire,
theft, sabotage, overwriting disks or tapes by mistake,and mounting of a wrong tape by the
operator.
Whenever a failure of type 1 through 4 occurs, the system must keep sufficient
information to quickly recover from the failure. Disk failure or other catastrophicfailures
of type 5 or 6 do not happen frequently; if they do occur, recovery is a major task.
TRANSACTION AND STATES AND ADDITIONAL
OPERATION

A transaction is an atomic unit of work that should either be completed in its

entirety or not done at all. For recovery purposes, the system needs to keep track of
when each transaction starts, terminates, and commits, or aborts.
the recovery manager of the DBMS needs to keep track of the
following operations:
■ BEGIN TRANSACTION : This marks the beginning of transaction execution.
■ READ or WRITE: These specify read or write operations on the database items
■ END_TRANSACTION: This specifies that READ and WRITE transaction operations
have ended and marks the end of transaction execution. However, at this point it
may be necessary to check whether the changes introduced by the transaction can
be permanently applied to the database (committed) or
whether the transaction has to be aborted because it violates serializability or for
some other reason.
■ 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.
■ 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.

The following figure shows a state transition diagram that illustrates how a
transaction moves through its execution states. A transaction goes into an active
state immediately after it starts execution, where it can execute its READ and WRITE
operations. When the transaction ends, it moves to the partially committed state.
At this point, some types of concurrency control protocols may do
additional checks to see if the transaction can be committed or not.
If these checks are successful, the transaction is said to have reached its
commit point and enters the committed state. When a transaction is
committed, it has concluded its execution successfully and all its changes
must be recorded permanently in the database, even if a system failure
occurs.
However, a transaction can go to the failed state, if one of the checks fails
or if the transaction is aborted during its active state. The terminated
state corresponds to the transaction leaving the system.

SyStem log:
to recover from failures that affect transactions, the system maintains a log
.the memory area that holds data to be written to the log file on the disk
called the log buffers. When the log buffer is filled, or when certain other
conditions occur, the log buffer is appended to the end of the log file on disk.
T refers to a unique transaction-id that is generated automatically by the
system for each transaction and that is used to identify each transaction:
1. [start _transaction, T]: Indicates that transaction T has started
execution.
2. [write _item, T, X, old _value, new _value]:Indicates that transaction T
has changed the value of database item X from old _value to new _value.
3. [read _item, T, X]: Indicates that transaction T has read the value of
database item X.
4. [commit, T]: Indicates that transaction T has completed successfully,
and affirms that its effect can be committed (recorded permanently) to the
database.
5. [abort, T]: Indicates that transaction T has been aborted.
it is possible to undo the effect of these WRITE operations of a transaction T
by tracing backward through the log and resetting all items changed by a
WRITE operation of T to their old values. Redo of an operation may also be
necessary if a transaction has its updates recorded in the log but a failure
occurs before the system can be sure that all these new values have been
written to the actual database on disk from the main memory buffers.

Commit Point of a Transaction:

A transaction T reaches its commit point 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 then writes a commit record [commit, T] into the log. If a
system failure occurs. we can search back in the log for all transactions T
that have written a [start _transaction, T] record into the log but have not
written their [commit, T] record yet; these transactions may have to be
rolled back to undo their effect on the database during the recovery process.
the log file must be kept on disk. it is common to keep one or more blocks of
the log file in main memory buffers, called the log buffer. At the time of a
system crash, only the log entries that have been written back to disk are
considered in the recovery process if the contents of main memory are lost.
Hence, before a transaction reaches its commit point, any portion of the
log that has not been written to the disk yet must now be written to the
disk. This process is called force-writing the log buffer to disk before
committing a transaction.
DBMS-Specific Buffer Replacement Policies
The DBMS cache will hold the disk pages that contain information currently
being processed in main memory buffers. If all the buffers in the DBMS
cache are occupied and new disk pages are required to be loaded into
main memory from disk, a page replacement policy is needed to select
the particular buffers to be replaced.
Some of the place replacement policies are:
Domain Separation (DS) Method:
In a DBMS, various types of disk pages exist: index pages, data file pages,
log file pages, and so on. In this method, the DBMS cache is divided into
separate domains (sets of buffers). Each domain handles one type of disk
pages, and page replacements within each domain are handled via the
basic LRU (least recently used) page replacement.
Hot Set Method:
This page replacement algorithm is useful in queries that have to scan a
set of pages repeatedly. If the inner loop file is loaded completely into main
memory buffers without replacement (the hot set), the join will be
performed efficiently because each page in the outer loop file will have to
scan all the records in the inner loop file to find join matches. The hot set
method determines for each database processing algorithm the set of
disk pages that will be accessed repeatedly, and it does not replace
them until their processing is completed.
The DBMIN Method.
This page replacement policy uses a model known as QLSM (query locality
set model), which predetermines the pattern of page references for each
algorithm for a particular type of database operation.
The DBMIN page replacement policy will calculate a locality set using
QLSM for each file instance involved in the query . DBMIN then allocates
the appropriate number of buffers to each file instance involved in the query
based on the locality set for that file instances.
The concept of locality set is analogous to the concept of working set, which
is used in page replacement policies for processes by the operating system
but there are multiple locality sets, one for each file instance in the query
CHARACTERIZING SCHEDULE BASED ON
RECOVERABILITY
When transactions are executing concurrently in an interleaved fashion, then the
order of execution of operation from all the various transaction is known as a
schedule.
SCHEDULES (HISTORIES) OF TRANSACTIONS

A schedule S of n transaction T1,T2….,Tn is an ordering of the operation of the

transactions. Operations from different transactions can be interleaved in the
schedule S. However, for each transaction Ti that participates in the schedule S, the
operation of Ti in S must appear in the same order in which they occur in Ti. The
order of operations in S is considered to be a total ordering, meaning that for any two
operations in the schedule, one must occur before the other.

For the purpose of recovery and concurrency control, we are mainly interested in the
read_item and write_item operations of the transactions, as well as the commit and
abort operations. A shorthand notation for describing a schedule uses the symbols b,
r, w, e, c, and a for the operations begin_transaction, read_item, write_item,
end_transaction, commit, and abort, respectively, and appends as a subscript the
transaction id (transaction number) to each operation in the schedule.
In this notation, the database item X that is read or written follows the r and w
operations in parentheses. In some schedules, we will only show the read and write
operations, whereas in other schedules we will show additional operations, such as
commit or abort.
Sa:R1(X);R2(X);W1(X);R1(Y);W2(X);W1(Y);
Sb:R1(X);W1(X);R2(X);W2(X);R1(Y)A1;
CONFLICTING OPERATION IN A SCHEDULE

Two operations in a schedule are said to conflict if they satisfy all three of the
following conditions:
1. They belong to different transaction;
2. They access the same item XZ; and
3. At least one of the operations is a write item(X);
For example, in schedule Sa, the operations r1(X) and w2(X) conflict, as do the
operations r2(X) and w1(X), and the operations w1(X) and w2(X). However, the
operations r1(X) and r2(X) do not conflict, since they are both read operations;
The operations w2(X) and w1(Y) do not conflict because they operate on distinct
data items X and Y; and the operations r1(X) and w1(X) do not conflict because
they belong to the same transaction. Intuitively, two operations are conflicting if
changing their order can result in a different outcome.
Here are some common types of conflicting operations.
1.Read-write conflict:
Transaction A reads a values, and then transaction B modifies
the same value before transaction A completes.
Example:
If we change the order of the two operations r1(X); w2(X) to w2(X); r1(X), then the
value of X that is read by transaction T1 changes, because in the second ordering the
value of X is read by r1(X) after it is changed by w2(X), whereas in the first ordering
the value is read before it is changed. This is called a read-write conflict
A: Read X
B: Write X (before A commits)
2.Write-write conflict:

Both transaction A and transaction B modify the data item.

It is illustrated by the case where we change the order of two operations such as
w1(X); w2(X) to w2(X); w1(X). For a write-write conflict, the last value of X will differ
because in one case it is written by T2 and in the other case by T1. Notice that two
read operations are not conflicting because changing their order makes no difference
in outcome
Example:
A: Write Y
B: Write Y
3.Read-write and write-read conflicts:

These involve a combination of read and write operation on the same data item.
Example:
A:Read Z
B:Write Z

A schedule S of n transactions t1,t2,…,Tn is said to be a complete schedule if the

following condition hold:
1. The operations in S are exactly those operation T1,T2,…,Tn, including a commit
or abort operation as the last operation for each transaction in the schedule.
2. For any pair of operations from the same transaction T1,their relative order of
appearance in S is the same as their order of appearance in Ti.
3. For any two conflicting operation , one of the two must occur before the other
in the schedule.
The preceding condition (3) allows for two nonconflicting operations to occur in the
schedule without defining which occurs first, thus leading to the definition of a
schedule as a partial order of the operations in the n transactions.
It is difficult to encounter complete schedule in a transaction processing system
because new transactions are continually being submitted to the system. Hence it is
useful to define the concept of the committed projection C(S) of a schedule S, which
includes only the operations in S that belong to committed transactions that is
transactions Ti whose commit operation Ci is in S.
Characterizing Schedules Based on Recoverability

We would like to ensure that once a transaction T is committed, it should never be

necessary to roll back T. This ensure that the durability property of transactions is not
violated. The schedules that theoretically need this criterion are called recoverable
schedules.
A schedule where a committed transaction may have to be rolled back during
recovery is called nonrecoverable and hence should not be permitted by the DBMS.
The condition for a recoverable schedule is as follows: A schedule S is recoverable T
in S commits until all transactions T’ that have written some item X that T reads have
committed.
Sa’:R1(X);R2(X);W1(X);R1(Y);W2(X);C2;W1(Y);C1;
Sa’ is recoverable , even though it suffers from the lost update problem;
Sc:R1(X);W1(X);R2(X);R1(Y);W2(X);C2;A1;
Sd:R1(X);W1(X);R2(X);R1(Y);W2(X);W1(X);C1;C2;
Se:R1(X);W1(X);R2(X);R1(Y);W2(X);W1(Y);A1;A2;
Sc is not recoverable because T2 ends reads item X written by T1 and then T2
commits before T12 commits.
Sd is recoverable because T2 reads item X written by T1 and then T2 commits after T1
commits and no other conflict operations exits in the schedule.
Se is similar to Sd but T1 is aborted (instead of commit)
• In this situation T2 should also abort, because the value of X it read is no longer
valid (this phenomenon known as cascading rollback or cascading abort).
Because cascading rollback can be quite time consuming, we perform cascade
less schedules.
• Because cascading rollback can be quite time consuming, we perform cascade
less schedules.
• A schedule is said to be cascade less or to avoid cascading rollback if every
transaction in the schedule reads only item that were written by committed
transactions.
• A more restrictive type of schedule is called strict schedule in which transaction
is called strict schedule in which transaction can neither read nor write an item
X until the last transactions that wrote X has committed (or aborted).
• It is important to note that any strict schedule is also cascadeless, and any
cascade less schedule is also recoverable. Suppose we have i transactions T1,
T2, … , Ti, and their number of operations are n1, n2, … , ni, respectively. If we
make a set of all possible schedules of these transactions, we can divide the
schedules into two disjoint subsets: recoverable and nonrecoverable. The
cascadeless schedules will be a subset of the recoverable schedules, and the
strict schedules will be a subset of the cascadeless schedules. Thus, all strict
schedules are cascadeless, and all cascadeless schedules are recoverable
[Module 4 Chapter 2]

TRANSACTION SUPPORT IN SQL

The definition of an SQL transaction is:

“An SQL transaction is a logical unit of work and is always atomic.”

A single SQL statement is always considered to be atomic i.e., either the execution is completed
without an error (or) it fails and leaves the database unchanged.

With SQL, there is no Begin_Transaction statement. Transaction initiation is done when particular
SQL statements are encountered. But, every transaction must have an explicit end statement, which
is either a COMMIT or a ROLLBACK. Every transaction has certain characteristics to it, which are set
by the SET TRANSACTION statement in SQL.

The characteristics are:

1.The access mode

2.the diagnostic area size

3. the isolation level

The access mode

• The access mode can be specified as READ ONLY or READ WRITE. The default is READ WRITE,
unless the isolation level of READ UNCOMMITTED is specified. In such cases READ ONLY is
assumed.
• READ WRITE allows select, update, insert, delete, and create commands to be executed.
READ ONLY is for data retrieval.

The diagnostic area size

The diagnostic area size n, is an integer value which indicates the number of conditions that can be
held simultaneously in the diagnostic area. These conditions return errors or exceptions(feedback)
to the user or program about the recently executed SQL statements.

The isolation level

The isolation level is specified using the statement ISOLATION LEVEL, where the value can be either
READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, or SERIALIZABLE. The default
isolation level is SERIALIZABLE, but some systems use READ COMMITTED as their default.

If a transaction executes at a lower isolation level than SERIALIZABLE, then one or more of the
following three violations may occur:

• Dirty read
• Nonrepeatable read
• Phantoms

Transaction support in SQL 1

[Module 4 Chapter 2]

1. Dirty read
A transaction T1 may read the update of a transaction T2, which has not yet committed. If T2
fails and is aborted, then T1 would have read a value that does not exist and is incorrect.

2. Nonrepeatable read
A transaction T1 may read a given value from a table. If another transaction T2 updates the
value and T1 reads it again, T1 will see a different value.

3. Phantoms
A transaction T1 may read a set of rows from a table, based on some condition specified in the
SQL WHERE-clause. Now suppose that a transaction T2 inserts a new row r that also satisfies the
WHERE-clause condition used in T1. The record r is called a phantom record because it was not
there when T1 starts but is there when T1 ends. T1 may or may not see the phantom row. If the
equivalent serial order is T1 followed by T2, then the record r should not be seen; but if it is T2
followed by T1, then the phantom record should be in the result given to T1. If the system
cannot ensure the correct behaviour, then it does not deal with the phantom record problem.

Table: Possible Violations Based on Isolation Levels as Defined in SQL

Type of Violation
Isolation Level Dirty Read Non repeatable read Phantom
READ UNCOMMITTED YES YES YES
READ COMMITTED NO YES YES
REPEATABLE READ NO NO YES
SERIALIZABLE NO NO NO

The above table summarizes the possible violations for the different isolation levels.

• An entry of Yes indicates that a violation is possible and an entry of No indicates that it is not
possible.
• READ UNCOMMITTED is the most forgiving, and SERIALIZABLE is the most restrictive in that
it avoids all three of the problems mentioned above.

A SAMPLE SQL TRANSACTION:

EXEC SQL WHENEVER SQL ERROR GOTO UNDO;

EXEC SQL SET TRANSACTION
READ WRITE
DIAGNOSTIC SIZE 5
ISOLATION LEVEL SERIALIZABLE;
EXEC SQL INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno, Salary)
VALUES ('Robert', 'Smith', '991004321', 2, 35000);
EXEC SQL UPDATE EMPLOYEE
SET Salary = Salary * 1.1 WHERE Dno = 2;
EXEC SQL COMMIT;
GOTO THE_END;
UNDO: EXEC SQL ROLLBACK;
THE_END: ... ;

Transaction support in SQL 2

[Module 4 Chapter 2]

The previous transaction consists of the following steps:

• First inserting a new row in the EMPLOYEE table and then updating the salary of all
employees who work in department.
• If an error occurs on any of the SQL statements, the entire transaction is rolled back.
• This implies that any updated salary (by this transaction) would be restored to its
previous value and that the new row would be removed.

SQL provides a number of transaction-oriented features. The DBA or database

programmers can take advantage of these options to try improving transaction
performance by relaxing serializability if that is acceptable for their applications.

Snapshot Isolation
• Snapshot isolation is used in some commercial DBMSs, and some concurrency control
protocols exist that are based on this concept.
• The basic definition of snapshot isolation is that a transaction sees the data items that it
reads based on the committed values of the items in the database snapshot (or
database state) when the transaction starts.
• Snap shot isolation will ensure that the phantom record problem does not occur, or in
some cases the database statement will only see the records that were inserted in the
database at the time the transaction starts.
• Any insertions, deletions, or updates that occur after the transaction starts will not be
seen by the transaction.

Questions:
1. Describe the four levels of isolation in SQL. Also discuss the concept of snapshot isolation
and its effect on the phantom record problem.
2. Define the violations caused by each of the following: dirty read, nonrepeat able read, and
phantoms

Transaction support in SQL 3