12-26-03, 14:39 #1Registered User
- Join Date
- Dec 2003
Unanswered: Clarification on Isolation Levels
I am new to DB2 and want to select a record and prevent it from being read or updated by any other process. I want an exclusive lock. Is this possible in DB2? In other databases I can do a "select for update" but DB2 is not as clear to me on how this can be done.
Here is what I got fromt he forum but I still do not understand if any of these solve my problem (I do not want anything else to read the record being worked on):
DB2 supports the following isolation levels:
Repeatable read (RR) locks all the rows an application references within a unit of work. Using repeatable read, a SELECT statement issued by an application twice within the same unit of work in which the cursor was opened, gives the same result each time. With repeatable read, lost updates, access to uncommitted data, and phantom rows are not possible.
Read stability (RS) locks only those rows that an application retrieves within a unit of work. It ensures that any qualifying row read during a unit of work is not changed by other application processes until the unit of work completes, and that any row changed by another application process is not read until the change is committed by that process. That is, "nonrepeatable read" behavior is not possible.
Cursor stability (CS) locks any row accessed by a transaction of an application while the cursor is positioned on the row. This lock remains in effect until the next row is fetched or the transaction is terminated. However, if any data on a row is changed, the lock must be held until the change is committed to the database.
Uncommitted read (UR) allows an application to access uncommitted changes of other transactions. The application also does not lock other applications out of the row it is reading, unless the other application attempts to drop or alter the table. Uncommitted read works differently for read-only and updatable cursors.
12-26-03, 14:59 #2Registered User
- Join Date
- May 2003
DB2 does not have quite as much flexibility in locking as some other databases since it wants to ensure the integrity of the database in the DBMS, and not leave it up to the application where a programmer error may cause problems.
Isolation level mainly deals with the duration of locks, and not the type of lock held or when the exclusive lock is first initiated.
If you use SELECT FOR UPDATE, DB2 will prevent other UPDATEs, or other SELECT FOR UPDATEs, but will not prevent others from reading the data (regular SELECTs for read only). If you want to hold an exclusive lock in DB2, you will need to update the row and keep the locks held by not committing the updates until you want to release the lock. You could create a dummy column to update for this purpose.M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390