Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 6: Data concurrency

This tutorial is the sixth in a series of nine tutorials designed to help you become familiar with all the different aspects of IBM® Informix® Dynamic Server (IDS) and help you get ready for the IDS Fundamentals Certification exam. In this part, which corresponds with Part 6 of the exam, gain an understanding of the data concurrency mechanisms in IDS.

Joo Guan Yap (yapjg@sg.ibm.com), Advanced Support Engineer, IBM

Joo Guan graduated from Nanyang University of Singapore with a Bachelor of Applied Science (Computer engineering) Degree in 1996 and was involved in various IT development projects. He joined IBM Informix support in 2001 and is now involved in Advanced Support work.



13 August 2009

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.

Objectives

At the end of the tutorial, you should be able to:

  • Understand the lock management in IDS
  • Identify isolation levels and their effects

Prerequisites

This tutorial is written for users with basic knowledge of Structured Query Language (SQL).

System requirements

To understand and test the concepts discussed in this tutorial, you need a running instance of IDS 11.50.


Understanding locks

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. For example:

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 LOCK 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 configuration parameter DEF_TABLE_LOCKMODE. It can take the value of either ROW or 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 ROW.

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
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
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 onstat utility."

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:

Shared lock

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.

Update/Promotable lock

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.

Exclusive lock

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.

INSERT, UPDATE, and 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 INSERT, UPDATE, or DELETE statement.

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 lockHold U lockHold S lockHold IS lockHold SIX lockHold IX lock
Request X lockNo No No No No No
Request U lockNo No Yes Yes No No
Request S lockNo Yes Yes Yes No No
Request IS lockNo Yes Yes Yes Yes Yes
Request SIX lockNo No No Yes No No
Request IX lockNo 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.
Listing 9. onstat -g sql output
$ 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:

COMMIT WORK;

In session B, the query result is now returned after the commit finishes.


Deadlocks

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.)

Examine the onstat -p output for deadlocks:

Listing 10. 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

Monitoring locks using the onstat utility

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 following fields:

Table 2. Fields of onstat -k
FieldDescription
addressThe 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 onstat -u (users) output.
wtlistThe first entry in the list of user threads that is waiting for the lock, if there is one.
ownerThe shared-memory address of the thread that is holding the lock. This address corresponds to the address in the address field of the onstat -u (users) output. When the owner value is displayed in parentheses, it represents the shared memory address of a transaction structure. This scenario is possible only when a lock is allocated for a global transaction. This address corresponds to the address field of the output for onstat -G.
lklistThe next lock in a linked list of locks held by the owner just listed.
typeUses the following codes to indicate the type of lock:
  • HDR - Header
  • B - Bytes
  • S - Shared
  • X - Exclusive
  • I - Intent
  • U - Update
  • IX - Intent exclusive
  • IS - Intent shared
  • SIX - Shared intent exclusive
tblsnumThe row identification number. The rowid provides the following lock information:
  • If the rowid equals zero, the lock is a table lock.
  • If the rowid ends in two zeros, the lock is a page lock.
  • If the rowid is six digits or fewer and does not end in zero, the lock is probably a row lock.
  • If the rowid is more than six digits, the lock is probably an index key-value lock.
key#/bsizThe 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.

Example of 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;

The onstat -k output:

Listing 11. 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
ColumnDescription
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.


Isolation levels

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 deleted item

    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 search

    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;

The onstat -k output shows that a shared lock is placed on the table t1, although the only requested row is c1=5:

Listing 21. 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:

tabname  t1

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;

The onstat -k output will look like this:

Listing 23. 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 onstat -g ses commands.

For example, a session with a session ID of 26 (third column of the onstat -u output) shows this information:

Listing 24. 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
Listing 25. 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.


Conclusion

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=420556
ArticleTitle=Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 6: Data concurrency
publish-date=08132009