Isolation level
The DB2 setting that decides how much a program's reads are protected from other programs' changes, and so how much locking it does.
Also written WITH UR, uncommitted read, cursor stability, repeatable read, read stability, ISOLATION(CS)
When several programs use the same data, DB2 uses locks to keep each program's view consistent. The isolation level decides how strict that is. Stricter levels give more consistent results but hold more locks, which can make other programs wait.
There are four levels. Cursor stability, CS, is the usual default: a row is locked only while the program is positioned on it, and the program only sees committed data. Read stability, RS, keeps rows that matched the query locked until commit. Repeatable read, RR, the strictest, makes sure that reading the same data again in the same unit of work gives the same answer. Uncommitted read, UR, takes almost no locks and can see changes that other programs have not committed yet, which may later be rolled back.
The level is set when a package is bound, with the ISOLATION option, and a single statement can override it, for example with WITH UR at the end of a query.
UR is popular for reports and enquiries because it is fast and does not block anyone. It is unsafe for anything that updates data based on what it read, because what it read may never have really happened.
Where can I learn this hands-on?
Right here, at ZedBank. You take a junior job at a simulated bank and learn each part of the work by doing it, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- DeadlockWhen two programs each wait for data the other has locked, so neither can continue; DB2 cancels one of them.
- BINDPreparing a program's SQL for execution, working out how each statement will access the data and storing the result.
- CursorA mechanism for stepping through a set of rows one at a time, when a query returns more than one result.
- Commit and rollbackMaking a set of changes permanent together, or undoing them together, so a transaction never half-happens.
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.
- SQLThe language used to query and change relational data. The same SQL used everywhere else, running against mainframe tables.