Before you start
About this series
This complimentary series of nine tutorials has been developed to help you prepare for the IBM Informix Dynamic Server 11.50 Fundamentals certification exam (555). This certification exam will test your knowledge of entry-level administration of IDS 11.50, including basic SQL (Structured Query Language), how to install IDS 11.50, how to create databases and database objects, security, transaction isolation, backup and recovery procedures, and data replication technologies and purposes. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam.
About this tutorial
This tutorial describes the locking mechanism in IBM Informix Dynamic Server 11.50. It discusses isolation levels and their effects; and it also introduces you to the monitoring of isolation levels and locks.
At the end of the tutorial, you should be able to:
- Understand the lock management in IDS
- Identify isolation levels and their effects
This tutorial is written for users with basic knowledge of Structured Query Language (SQL).
To understand and test the concepts discussed in this tutorial, you need a running instance of IDS 11.50.
What is a lock?
A lock is a software mechanism that is used to control access to data in the database. In a multi-user environment where data is read and updated simultaneously, locks are used to ensure that the atomicity, isolation, consistency, and durability (ACID) of each transaction are not compromised, and to maintain the integrity of data.
Granularities of lock
Informix provides locking at various granularities. They are:
- Database lock: A lock on the entire database
- Table lock: A lock on an entire table
- Page lock: A lock on an entire page of data
- Row lock: A lock on a single data row
- Byte lock: A lock on a row containing VARCHARs
- Key lock: A lock on a single key value in an index
The coarser the granularity, the more database objects will be locked by a single lock. For example, for a table that can contain four rows on a disk page, a single lock placed on a page will result in all four rows being locked. On the contrary, if a row lock is used, then only one row will be locked. As such, coarser granularity will result in lesser concurrency and thus affect performance, especially when applications tend to access the same set of rows. However, coarser granularity would also mean less number of locks needed to lock the same number of rows in some cases. For example, only one lock is required to lock the entire table.
Lock mode of a table
When a table is created, its default lock granularity is page lock.
This default can be overridden by using the
LOCK MODE clause when creating the table.
CREATE TABLE t1(c1 int) LOCK MODE ROW;
The lock mode in this context indicates the granularity to be used
when a lock is required to be placed when accessing the table. If this
MODE clause is
omitted from the
CREATE SQL statement, the
default lock mode is used.
The lock mode of a table can be changed by using the
ALTER TABLE statement if the table is
already created. Using the example of table t1 mentioned above, you
can use the following
ALTER statement to
change the lock mode from row lock to page lock:
dbaccess db1 - ALTER TABLE t1 LOCK MODE(PAGE);
The default lock mode of the table created can be changed by using the
It can take the value of either
PAGE. For example, if the
DEF_TABLE_LOCKMODE is set to
ROW, the subsequent default lock mode for
the tables created after the database server is restarted will be
Similarly, the environment variable
IFX_DEF_TABLE_LOCKMODE can be used to
achieve the same effect. Note: If the environment variable is
set in the window from which
oninit is run
to bring up the server, then the default lock mode specified by the
environment variable will be effective for all the sessions. However,
if it is set in the environment of only a specific client session,
then it will only affect that specific session.
Listings 1 - 4 illustrate the different ways to examine the lock mode of a table (table name is t1; database is db1):
Listing 1. Use
dbschema -d db1 -t t1 -ss
Listing 2. Output
create table "informix".t1 ( c1 integer ) extent size 16 next size 16 lock mode row;
Listing 3. Use
oncheck -pt db1:t1 Output: TBLspace Report for db1:informix.t1 Physical Address 1:64070 Creation date 07/14/2009 04:51:27 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 4 Number of special columns 0 Number of keys 0 Number of extents 1 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 8 Next extent size 8 Number of pages allocated 8 Number of pages used 1 Number of data pages 0 Number of rows 0 Partition partnum 1049067 Partition lockid 1049067 Extents Logical Page Physical Page Size Physical Pages 0 1:64999 8 8
Listing 4. Use
dbaccess to query systables from the system catalog tables of the database, db1
dbaccess db1 - > select * from systables where tabname='t1'; tabname t1 owner informix partnum 1049067 tabid 100 rowsize 4 ncols 1 nindexes 0 nrows 0.00 created 07/14/2009 version 6553601 tabtype T locklevel R npused 0.00 fextsize 16 nextsize 16 flags 0 site dbname type_xid 0 am_id 0 pagesize 2048 ustlowts secpolicyid 0 protgranularity
The field "locklevel" is R, indicating a row lock.
In addition, use
onstat -k to monitor the
lock mode. This is illustrated later, in the section
"Monitoring locks using
Types of locks
Databases, tables, pages, and rows (collectively referred to as objects in this section) have different types of locks to control access to them. The following types of locks are used by IDS:
When reading is required and rows are not allowed to be changed, a shared lock can be placed on the object, such as a table, page, or row. More than one shared lock can be placed on an object by different sessions. Therefore, multiple shared locks can be placed on an object at any given time.
An update lock is placed on an object with the intent to update it. If a shared lock is placed on an object, another session is allowed to place an update lock on it. If the object needs to be changed, the update lock must be promoted to an exclusive lock. However, to get the exclusive lock, no shared or update lock can be on that object during the promotion.
When a session requests exclusive use of an object, an exclusive lock can be placed on the object. No other type of lock can be placed on an object by another session if an exclusive lock is in place.
DELETE statements use this lock on the
rows they change. The locks are released at end of the transaction.
The notation used to represent the type of locks by the
onstat utility and in the syslocks table
are as follows:
- Shared lock - S
- Update lock - U
- Exclusive lock - X
In some instances, an intention to place a particular lock can be
indicated by placing an intent lock. For example, an intent shared
lock will have a notation of IS. An IS lock is typically seen on
tables during a
SELECT statement. Intent
exclusive lock, IX, is seen on tables during an
Using the same notation, the compatibility matrix in Table 1 shows the locks that can be requested by a session when a particular type of lock is already placed on it by another session.
Table 1. Lock compatibility matrix
|Hold X lock||Hold U lock||Hold S lock||Hold IS lock||Hold SIX lock||Hold IX lock|
|Request X lock||No||No||No||No||No||No|
|Request U lock||No||No||Yes||Yes||No||No|
|Request S lock||No||Yes||Yes||Yes||No||No|
|Request IS lock||No||Yes||Yes||Yes||Yes||Yes|
|Request SIX lock||No||No||No||Yes||No||No|
|Request IX lock||No||No||No||Yes||No||Yes|
Lock request failure
When a session fails to obtain the lock, the default behavior of the database server is to return an error. To enable the session to wait for the lock to be released by the owner session, run the following SQL statement before running the intended statement:
SET LOCK MODE TO WAIT;
This will let the session wait indefinitely until the lock is released. To specify a finite waiting period (for example, five seconds), use the following statement:
SET LOCK MODE TO WAIT 5;
To return to the default behavior:
SET LOCK MODE TO NOT WAIT;
The effect of the
SET LOCK MODE statement
is at the session level.
Let's examine the lock mode behavior with the following example:
Listing 5. Start session
dbaccess - - DROP DATABASE db1; CREATE DATABASE db1 WITH BUFFERED LOG; CREATE TABLE t1 (c1 int); INSERT INTO t1 VALUES (1);
Listing 6. Session A
dbaccess db1 - BEGIN WORK; UPDATE t1 SET c1=5;
Listing 7. Session B
dbaccess db1 - SELECT* FROM t1;
Listing 8. Error received
244: Could not do a physical-order read to fetch next row. 107: ISAM error: record is locked.
$ onstat -g sql IBM Informix Dynamic Server Version 11.50.FC4 -- On-Line -- Up 1 days 06:16:34 -- 149504 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 30 - db1 CR Not Wait 0 0 9.24 Off
The output shows the db1 isolation level is "Committed Read" (isolation level will be discussed in detail later). Because there is an uncommitted update on table t1, the error complaining that the record is locked is expected.
Let's try the
SET LOCK MODE WAIT statement
in session B:
SET LOCK MODE TO WAIT; SELECT* FROM t1;
This session does not immediately return an error, but waits for the lock to be released.
Now, in session A, finish the transaction:
In session B, the query result is now returned after the commit finishes.
A deadlock occurs when two sessions are holding onto the lock that each other needs. The result is that they will wait indefinitely for each other, but this situation is detected and prevented from happening by the database server. For example:
T1: Read row A and place a lock on it.
T2: Read row B and place a lock on it.
T1: Request a lock on row B.
T2: Request a lock on row A.
IDS maintains an internal lock table. To prevent a deadlock, it checks the internal lock table to see whether a deadlock can occur before a session can wait for a lock. This is what happens:
T1: Set lock mode to wait.
T2: Set lock mode to wait.
T1: Read row A and place a lock on it.
T2: Read row B and place a lock on it.
T1: Request a lock on row B; has to wait for T2 to release the lock on row B.
T2: Request a lock on row A, but is rejected because deadlock can occur if it has to wait for the lock.
From the internal lock table, IDS detects that:
- T1 is holding to the lock on row A
- T2 is holding to the lock on row B
- T1 is waiting for the lock on row B that T2 is holding
A deadlock will occur if T2 is allowed to wait for a lock on row A that T1 is holding. Thus, the lock request is rejected with an error (-143 ISAM error: deadlock detected.)
onstat -p output for deadlocks:
onstat -p output for deadlocks
$ onstat -p IBM Informix Dynamic Server Version 11.50.FC4 -- On-Line -- Up 2 days 22:34:37 -- 157696 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 13851 44301 1894394 99.27 22604 41159 429311 94.73 isamtot open start read write rewrite delete commit rollbk 1178884 64495 88313 413795 46723 58450 5657 17833 16 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 89.23 13.32 366 70849 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 191 0 750183 5 0 6 4010 6770 ixda-RA idx-RA da-RA RA-pgsused lchwaits 231 2 3080 3313 254
Looking at the "deadlks" field, you can see that five deadlock events have occurred since the database server came online. If deadlocks are happening frequently, examine the application and try to stagger the use of the frequently changed rows. By reducing the time to wait for a lock, the chances of deadlock can also be reduced.
For a distributed transaction, a session uses the
DEADLOCK_TIMEOUT configuration parameter to
wait for a response from the remote database server before it returns
an error. The "dlouts" field in the
onstat -p output shows the statistics for
distributed deadlock timeouts.
Logging and non-logging database
In a logging database, the database server keeps a record of its transaction and locks held over the span of the transaction. Often, the locks held by the transaction can be identified unless the transaction is not committed or is rolled back. In a non-logging database, there are no transactions, but locks are still used. Though easily missed, locks are used by the server when executing the Data Manipulation Language and released very quickly after the statement is completed.
Monitoring locks using the
The locks used by the database server are held in an internal lock
table. Use the
onstat -k command to monitor
the type and granularity of the locks being held.
The output of the
onstat -k command has the
Table 2. Fields of
|address||The address of the lock in the lock table. If a user thread is
waiting for this lock, the address of the lock appears in the
wait field of the |
|wtlist||The first entry in the list of user threads that is waiting for the lock, if there is one.|
|owner||The shared-memory address of the thread that is holding the
lock. This address corresponds to the address in the address
field of the |
|lklist||The next lock in a linked list of locks held by the owner just listed.|
|type||Uses the following codes to indicate the type of lock:
|tblsnum||The row identification number. The rowid provides the
following lock information:
|key#/bsiz||The index key number, or the number of bytes locked for a VARCHAR lock. If this field contains "K-" followed by a value, it is a key lock. The value identifies which index is being locked. For example, K-1 indicates a lock on the first index defined for the table.|
onstat -k output
This example uses a non-ANSI-compliant logging database and two tables with the following:
CREATE DATABASE db1 WITH LOG; CREATE TABLE t1 (c1 int); CREATE TABLE t2 (c1 int) LOCK MODE ROW;
The following values are inserted into the tables:
INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (2);
The following statements are executed:
BEGIN WORK; UPDATE t1 SET c1=5; UPDATE t2 SET c1=5;
onstat -k output:
onstat -k output
$ onstat -k IBM Informix Dynamic Server Version 11.50.FC4 -- On-Line -- Up 2 days 21:39:44 -- 157696 Kbytes Locks address wtlist owner lklist type tblsnum rowid key#/bsiz 10a3134d8 0 111535cd8 0 HDR+S 100002 204 0 10a44bcd8 0 111535488 0 S 100002 204 0 10a44bd58 0 111536528 0 S 100002 204 0 10a44c4d8 0 111537e18 0 HDR+S 100002 202 0 11348b028 0 111537e18 10a44c4d8 HDR+IX 1001ef 0 0 11348b0a8 0 111537e18 11348b028 HDR+X 1001ef 100 0 11348b128 0 111537e18 11348b0a8 HDR+IX 1001f0 0 0 11348b2a8 0 111537e18 11348b128 HDR+X 1001f0 101 0 U 11348b328 0 111537e18 11348b2a8 HDR+X 1001f0 102 0 U 9 active, 80000 total, 16384 hash buckets, 2 lock table overflows
This output has the following information:
- Table lock:
- Looking at the row with address 11348b028, the rowid is 0, indicating table lock. The IX indicates intent exclusive type.
- Page lock:
- Looking at the row with address 11348b0a8, the last two characters of the rowid are 00, which indicates a page lock. The type X means exclusive Lock. This is for table t1, which has page-level locking mode.
- Row lock:
- Looking at the row with the address 11348b328, the rowid is 0x102, which indicates a row lock and an exclusive lock in this case. This is for table t2, which has row-level locking mode.
- Database lock:
- Looking at the row with the address 10a3134d8, there is a row lock on a table. However, the tblsnum 0x100002 is a database tblspace, which is a special table that keeps the list of databases in the server. Thus, a lock on one of its rows is equivalent to locking a database. The S type lock is a shared lock.
To find out what the table name is for the tblsnum in the
onstat -k, use the following statement on
the systables table:
Listing 12. Identify table name for tbslnum
SELECT * FROM systables WHERE partnum=1049072; Output: tabname t2 owner informix partnum 1049072 tabid 101 rowsize 4 ncols 1 nindexes 0 nrows 0.00 created 07/20/2009 version 6619137 tabtype T locklevel R npused 0.00 fextsize 16 nextsize 16 flags 0 site dbname type_xid 0 am_id 0 pagesize 2048 ustlowts secpolicyid 0 protgranularity
Monitoring locks using the syslocks table in the sysmaster database
You can also monitor locks using the syslocks table in the sysmaster database. Table 3 lists the columns in the table and their descriptions:
Table 3. Fields of syslock table
|dbsname||Database on which the lock is held|
|tabname||Name of the table on which lock is held|
|rowidlk||ID of the row on which the lock is held (0 indicates a table lock)|
|keynum||The key number for the row|
|Type||Type of the lock|
|owner||Session ID of the lock owner|
|waiter||Session ID of the first waiter on the lock|
Run the following query in db1:
Listing 13. Query on db1
CREATE DATABASE db1 WITH LOG; CREATE TABLE t3 (c1 int); CREATE INDEX idx1 ON t3(c1); INSERT INTO t3 VALUES(10); INSERT INTO t3 VALUES(11); INSERT INTO t3 VALUES(12); INSERT INTO t3 VALUES(13); BEGIN WORK; UPDATE t3 SET c1=99 WHERE c1=10;
Select the information about db1 from syslocks:
Listing 14. Select db1 information from syslocks
SELECT * FROM syslocks WHERE dbsname='db1'; Output: dbsname db1 tabname t3 rowidlk 0 keynum 0 type IX owner 109 waiter dbsname db1 tabname idx1 rowidlk 0 keynum 1 type X owner 109 waiter dbsname db1 tabname t3 rowidlk 256 keynum 0 type X owner 109 waiter
The output clearly shows that locks are held for objects in db1. The
index name is listed in the tabname column. Note that the rowidlk is
interpreted the same way as the rowid column in the
onstat -k output. If you convert 256 to
hexadecimal, it is 0x100, indicating a page lock.
What is an isolation level?
"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 level (read uncommitted)
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 level (read committed)
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 level
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.
Repeatable read isolation level
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
onstat -k output
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:
onstat -k output
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.
Default isolation and changing the default isolation
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.
Monitoring isolation levels
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:
-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.
Now, you should have a good understanding of how the different types of locks are used in IDS to maintain the integrity of the data in a multi-user environment. In addition, you should also have a good understanding of how different isolation levels affect concurrency and how the different potential problems of each can be avoided.
- "IBM Informix Guide to SQL: Tutorial" (IBM, 2007): Get an introduction to the Informix implementation of SQL, including SQL statements, data types, and system catalog tables that provide information about database structures.
- developerWorks Informix zone: Get the resources you need to advance your skills in the Informix arena.
- "The IDS Detective Game" (developerWorks, April 2008): Learn or teach the basics of Informix Dynamic Server (IDS) and relational databases with an interactive game called "The IDS Detective Game".
- IDS roadmap for administrators, developers, and end users: Find resources for all aspects of IDS—planning, installing, configuring, administering, tuning, monitoring, and more.
- Informix Education Training Path: See the courses you need to take to achieve particular skills or certification.
- Informix library: Learn more details about IDS from the online manuals or the IDS Information Center.
- IBM Informix Dynamic Server 11.50 Information Center: Find information that you need to use the IDS family of products and features.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Informix Dynamic Server Express Edition: Download a trial version of Informix Dynamic Server Express Edition to get started with IDS.
- Informix Dynamic Server Enterprise and Developer Edition: Download a free trial version of Informix Dynamic Server Enterprise or Developer Edition.
- Download IBM product evaluation versions or explore the online trials in the IBM SOA Sandbox, and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
- Participate in the discussion forum.
- IDS Experts Blog: Read the technical notes on Informix Dynamic Server from a worldwide team of Development and Technical Support engineers.
- Participate in developerWorks blogs and get involved in the My developerWorks community; with your personal profile and custom home page, you can tailor developerWorks to your interests and interact with other developerWorks users.