"The isolation level is the degree to which your program is isolated from the concurrent actions of other programs" ("Informix Guide to SQL: Tutorial", IBM, 2007). Each isolation level offers different levels of concurrency and different degrees of data consistency.
In the ANSI SQL-92 standard, three potential problems (phenomena) with data concurrency are mentioned. They are:
Dirty read: reads the data that never existed
Here is a possible Dirty Read scenario with T1 and T2 as two different transactions:
T1: Starts a transaction
T1: Modifies some data
T2: Reads the modified data
T1: Rolls back the transaction
Non-repeatable read: reads modified data or cannot find the
Possible scenario with T1 and T2 as two different transactions:
T1: Reads a data item
T2: Modifies or deletes that same data item
T1: Reads the data item again
Phantom read: getting a different result from the first
Possible scenario with T1 and T2 as two different transactions:
T1: Searches a set of results based of a set of criteria
T2: Inserts a set of data that meets the search criteria of T1
T1: Searches the results again based on the same set of criteria
IDS provides four isolation levels that offer mechanisms for dealing with these data concurrency issues:
- Dirty read isolation (read uncommitted)
- Committed read isolation (read committed)
- Cursor stability isolation
- Repeatable read isolation (repeatable read and serializable)
Dirty read isolation requires no locks held on the row it is reading, and it does not check whether there is any lock on the required rows. A session with this isolation level could get dirty data that was updated by another session, but not yet committed.
If a database is non-logging, this is the only isolation level allowed.
All three phenomena described above can occur to this isolation level.
Committed read isolation lets a session read only those rows that are committed. It does not place a lock on the row that it is reading, but does check if any lock is placed on them. As such, a dirty read will not happen. This is how it is prevented:
Listing 15. Prevent a dirty read
T1: CREATE TABLE t1 (c1 int) LOCK MODE ROW; T1: INSERT INTO t1 VALUES(1); T1: INSERT INTO t1 VALUES(2); T1: INSERT INTO t1 VALUES(3); T1: BEGIN WORK T1: UPDATE t1 SET c1=20 WHERE c1=2 (this places an exclusive lock on the row) T2: SET ISOLATION TO Committed Read T2: SELECT * FROM t1 WHERE c1=2
T2 will be denied the read on that row because the database server detects that an exclusive lock is already placed on it, implying that it is an uncommitted row.
Because there is no lock placed on a row that is being read, a non-repeatable read phenomenon can happen. For example:
Listing 16. Non-repeatable read phenomenon
T1: BEGIN WORK T1: SET ISOLATION TO Committed Read T1: SELECT * FROM t1 WHERE c1=2 T2: BEGIN WORK T2: UPDATE t1 SET c1=20 WHERE c1=2 (places an Exclusive Lock on the row and updates it) T2: COMMIT; this releases the lock T1: SELECT * FROM t1 WHERE c1=2 (read the same row but this time it has different data)
Also, because the table is not locked, nothing is preventing a phantom read from occurring because another session can easily insert another row.
IDS supports a type of committed read, called last committed read, which reads the most recently committed rows. Thus, even if a row is updated and not yet committed, the database server returns the most recently committed row. Last committed read isolation prevents the reader from getting the lock error in the scenario above, but still allows the two phenomena similar to the plain committed read isolation.
Cursor stability isolation places a shared lock on the row that is being read and releases it when the next row is locked.
This isolation level can prevent the dirty read and non-repeatable read phenomena. This is how a dirty read is prevented:
Listing 17. Prevent a dirty read
T1: BEGIN WORK T1: UPDATE t1 SET c1=20 WHERE c1=2 (an exclusive lock is placed this row) T2: SET ISOLATION TO Cursor Stability T2: SELECT * FROM t1 WHERE c1=2
The server denied T2 from reading the row because it could not place a shared lock since an exclusive lock is already in place (see Table 1).
Listing 18 illustrates how cursor stability prevents the non-repeatable read phenomenon:
Listing 18. Cursor stability prevents the non-repeatable read phenomenon
T1: SET ISOLATION TO Cursor Stability T1: declare a cursor for SELECT * FROM t1 WHERE c1=2 T1: open cursor and fetch the row T2: UPDATE t1 SET c1=20 WHERE c1=2 (Fail to put an exclusive lock on the row because a shared lock is already on it; see Table 1.) T1: SELECT * FROM t1 WHERE c1=2
Note that to place a lock on the record read, the session will need to use a cursor to fetch the record. Otherwise, no locks will be placed, just like in the committed read isolation level.
The phantom read phenomenon can happen to a session using this isolation level as well, just like with committed read isolation.
The repeatable read isolation implemented in IDS is equivalent to the ANSI SQL 92 repeatable read and serializable.
In addition to preventing dirty read and non-repeatable read phenomena, this isolation can also prevent the phantom read phenomenon.
If the database server needs to do a sequential read on a table for a session with repeatable read isolation, it will place a shared lock on the table. However, if an index scan is used, only the index keys related to the affected rows will be locked with shared locks.
Consider the following example. A table is created and values are inserted as shown below:
Listing 19. Table creation and inserted values
CREATE TABLE t1 (C1 INT) LOCK MODE ROW; INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2); INSERT INTO t1 VALUES(3); INSERT INTO t1 VALUES(4); INSERT INTO t1 VALUES(5); INSERT INTO t1 VALUES(6); INSERT INTO t1 VALUES(7); INSERT INTO t1 VALUES(8); INSERT INTO t1 VALUES(9); INSERT INTO t1 VALUES(10);
Then transaction T1 executes the following against the table:
Listing 20. Transaction T1 execution
T1: BEGIN WORK; T1: SET ISOLATION TO REPEATABLE READ; T1: SELECT * FROM t1 WHERE c1=5;
onstat -k output shows that a shared
lock is placed on the table t1, although the only requested row is
Locks address wtlist owner lklist type tblsnum rowid key#/bsiz 10a3134d8 0 111535cd8 0 HDR+S 100002 204 0 10a313558 0 111536d78 0 HDR+S 100002 202 0 10a313758 0 111536d78 10a313558 HDR+S 100243 0 0 10a44bcd8 0 111535488 0 S 100002 204 0 10a44bf58 0 111536528 0 S 100002 204 0
The decimal value of 0x100243 is 1049155.
Running the following SQL :
select tabname from systables where partnum=1049155
The following result is returned:
At this point, a second transaction T2 continues with the following SQL statement:
T2: INSERT INTO t1 VALUES(20);
T2 will get an error 271 (could not insert row) and 113 (table is locked).
If the following statement had been run on T2 instead:
T2: UPDATE t1 SET c1=20 WHERE c1=10
An error would be returned saying that the row to update could not be fetched. As you can see, placing a shared lock on the table has a large impact on concurrency.
If you modify the example with an index:
CREATE INDEX idx1 ON t1(c1);
Starting the exercise all over again:
Listing 22. Start execution again
T1: BEGIN WORK; T1: SET ISOLATION TO REPEATABLE READ T1: SELECT * FROM t1 WHERE c1=5;
onstat -k output will look like this:
Locks address wtlist owner lklist type tblsnum rowid key#/bsiz 10a3134d8 0 111535cd8 0 HDR+S 100002 204 0 10a313558 0 111536d78 0 HDR+S 100002 202 0 10a313658 0 111536d78 10a313d58 HDR+SR 100245 106 K- 1 10a313758 0 111536d78 10a313558 HDR+IS 1001ca 0 0 10a313b58 0 111536d78 10a313758 HDR+IS 100243 0 0 10a313d58 0 111536d78 10a313b58 HDR+SR 100245 105 K- 1 10a44bcd8 0 111535488 0 S 100002 204 0 10a44bf58 0 111536528 0 S 100002 204 0
This time, instead of placing a shared lock on the table t1, the database server placed an intent shared lock on the table. The shared lock is placed on the related index key, instead (look at the "K -1 " value at the last column of Listing 23).
If T2 tries to do an update, as in the example above:
T2: BEGIN WORK; T2: UPDATE t2 SET c1=5 WHERE c1=1
This time the update will be successful.
From this example, you can see how an index can improve concurrency.
However, if T2 tries to do any of the following SQL statements, the session will get a "key value locked" error:
INSERT INTO t1 VALUES(5);
UPDATE t1 SET c1=20 WHERE c1=5;
DELETE t1 SET c1=20 WHERE c1=5;
Because there is no way to insert or change the rows where c1 is equal to 5, the phantom read phenomenon is avoided while concurrency is increased.
The default isolation for an ANSI-compliant database is repeatable read. The default isolation level for a non-ANSI-compliant database without logging is dirty read and for a non-ANSI-compliant database with logging is committed read.
To change the isolation level, use the following SQL statement:
SET ISOLATION TO <isolation level>
Where <isolation level> can be one of the following:
- DIRTY READ
- COMMITTED READ
- CURSOR STABILITY
- REPEATABLE READ
The SQL statement run after the
SET ISOLATION statement is run will have
the new isolation level.
The isolation level can be monitored with the
onstat -g sql or
-g ses commands.
For example, a session with a session ID of 26 (third column of the
onstat -u output) shows this information:
onstat -g ses 26
$ onstat -g ses 26 IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 00:36:15 -- 522240 Kbytes session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 26 informix - 128 16699 acme 1 196608 114440 off tid name rstcb flags curstk status 40 sqlexec 111972cf0 Y-BP--- 7839 cond wait netnorm - Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 26 V 112bdc040 192512 81360 259 62 26*O0 V 112dcf040 4096 808 1 1 name free used name free used overhead 0 6576 scb 0 144 opentable 0 4360 filetable 0 1480 ru 0 600 log 0 16536 temprec 0 21664 keys 0 856 gentcb 0 1632 ostcb 0 2872 sqscb 0 38648 sql 0 72 rdahead 0 1120 hashfiletab 0 552 osenv 0 2680 sqtcb 0 3808 fragman 0 352 udr 0 10488 sqscb info scb sqscb optofc pdqpriority sqlstats optcompind directives 1125dc318 112c61028 0 0 0 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 26 - db1 CR Not Wait 0 0 9.24 Off Last parsed SQL statement : update t2 set c1=5
onstat -g sql 26
$ onstat -g sql 26 IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 00:36:40 -- 522240 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 26 - db1 CR Not Wait 0 0 9.24 Off Last parsed SQL statement : update t2 set c1=5
"CR" indicates committed read. The notation used for dirty read, cursor stability, and repeatable read are DR, CS, and RR respectively. If you use the last committed type of committed read isolation, then the notation is LC.