Before we look into Isolation types, We should understand,
There are different application or users can access and modify data stored in a DB2 database at the same time, So the DB2 Database Manager must be able to allow users to make necessary changes while ensuring that data integrity is never compromised.
The sharing of DB2 resources by multiple users or application programs at the same time is known as concurrency. One of the ways DB2 enforces concurrency is through the use of isolation levels, which determine how data accessed and/or modified by one transaction is "isolated from" other transactions.
Isolation levels are enforced by locks, and the type of lock that is used limits or prevents access to the data by concurrent application processes.
The database manager supports three general categories of locks:
Share (S) Under an S lock, concurrent application processes are limited to read-only operations on the data.
Update (U) Under a U lock, concurrent application processes are limited to read-only operations on the data, if these processes have not declared that they might update a row. The database manager assumes that the process currently looking at a row might update it.
Exclusive (X) Under an X lock, concurrent application processes are prevented from accessing the data in any way. This does not apply to application processes with an isolation level of uncommitted read (UR), which can read but not modify the data.
Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. Thus, all isolation levels ensure that any row that is changed by an application process during a unit of work is not changed by any other application process until the unit of work is complete.
DB2 have four locking isolation levels:
|
This holds page and row locks until a COMMIT point is reached. No other program can modify the data. If the data is accessed twice during the unit of work, the same exact data will be returned.
This holds page and row locks until a COMMIT point is reached. But other programs can INSERT new data. If data is accessed twice during the unit of work, new rows may be returned, but old rows will not have changed.
Default isolation level. This acquires and releases page or row locks as pages or rows are read and processed. This provides the greatest level of concurrency. But there is a chance of different data being returned by the same cursor if it is processed twice during the same unit of work.
This is also known as dirty read processing. UR avoids locking altogether. Data can be read that may never actually exist in the database. We can use this for working with the table that is rarely updated.
Regardless of the ISOLATION level chosen, all page locks are released when a COMMIT is encountered.
If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!