Phantom read: 

Within the same transaction User A runs a SELECT statement multiple times 

and gets addition records because user B

 added

 records in User A's result 

set.

One of the more advanced features of a data management system is to define 

modification rules

 to control the use of data and guarantee the integrity of the 

data to prevent above undesirable effects.

7.3.2  Concurrency control and transaction isolation

From a bird's eye view, two methods for concurrency control can be 

differentiated:

The optimistic concurrency approach:

A strategy to increase concurrency in which rows are not locked. 

Transactions are divided into 

read,

validate,

 and

 write

 phases. Instead, 

during the validation phase before they are updated or deleted, a cursor 

checks to see if they have been changed since they were last read. If so, the 

update or delete fails.

The pessimistic concurrency or locking approach:

A strategy for implementing serializability in which rows are locked so that 

other transactions cannot change them. Transaction requests locks to update 

resources. Other transactions have to wait or time out. The resource is 

released on transaction completion or commit and rollback.

Both methods have their pros and cons, but by far the most popular method is 

the latter approach. Both MySQL and DB2 follow this approach to various 

degrees of sophistication and implementation differences.

7.3.3  DB2 isolation levels

In general DB2 UDBs default setting do nt require changes in the application to 

work around a different locking behavior. 

DB2 provides transaction isolation levels to segregate data and prevent the 

undesirable effects discussed in 7.3.1,  What is the purpose of locking?  on 

page 226. Table 7 10 gives an overview over DB2 isolation levels.

Table 7 10   DB2 isolation level

DB2 UDB isolation level

Description

Uncommitted Read

Access to uncommitted data from 

other transaction

No record locks unless updates occur

 Chapter 7. Application porting 

227