Informix Dynamic Server locking, Part 1: Understand locking behavior and analyze locking conflicts in IDS

Concepts and mechanisms

This is the first of a two-part article series talking about locking mechanisms in IBM's strategic data server, Informix® Dynamic Server (IDS). IDS is an optimal choice for high-end online transaction processing (OLTP) and embedded solutions. This article helps you to understand locking methodology and assists you in analyzing locking conflicts.

Share:

Eric Herber (eric@herber-consulting.de), Independent Consultant, Herber Consulting

Eric HerberEric Herber has worked as an independent consultant for IBM Informix database products for about 10 years. Beside being a consultant, he has written several articles about Informix database technology. In his free time, he loves mountain biking.



07 September 2006

Also available in Russian Japanese

Introduction

Multiuser databases with hundreds or even thousands of users and batch processes trying to update data simultaneously need some mechanisms to protect the data and keep it consistent. Beside transaction logging, locking is the key feature to achieve this.

However, locking often results in conflicts and wait situations. These are one of the most common problem areas a DBA is faced with during his daily work. Analyzing locking problems without the appropriate scripts in place is complex and error-prone.

This article explains IDS locking mechanisms and aids you in analyzing locking conflicts and lock wait situations.
Examples are based on the stores_demo database that can be created by executing the following command:

  • dbaccessdemo stores_demo -log

Lock types

IDS knows several different types of locks. Those types are as follows:

Shared lock

Shared locks could be placed on rows that do not have an exclusive lock on it. Other users might place additional shared locks or update locks on the same row, but no other exclusive locks are allowed.

Update lock

An update lock is a special kind of lock generated by a cursor that has been declared with the for update clause. Update locks could only be placed on a row that currently has no update or exclusive lock on it. Once an update lock has been placed on a row, it is promoted to an exclusive lock as soon as the row is updated.

Exclusive lock

An exclusive lock can only be placed on rows that do not have any other kind of lock on it. Once an exclusive lock is placed on a row, no other locks can be placed on the same row. It is exclusively reserved for this database session.

Intent lock

Intent locks are special kinds of locks. If, for example, a row is updated, an exclusive lock is placed on the row and an intent exclusive lock is placed on the table. The intent exclusive table lock ensures that no other session could place a share or exclusive lock on the table as long as individual rows in the table have been exclusively locked.

Locking granularity

IDS allows application developers to place locks on different objects. Those objects are discussed below.

Database locks

Databases could be locked explicitly in exclusive or share mode. An exclusive lock prevents anybody else from accessing the database. A share lock allows concurrent users to read and update data from this database but prevents the placement of an exclusive lock on this database.

  • Database share lock

    A share lock is automatically placed on the database as soon as you open the database. This ensures that no other session could place an exclusive lock on the database or drop the database. Utilities like onunload might also place a share lock on the database.

    • SQL Statement
      • database stores_demo
    Listing 1. Share lock on a database
    Output from onstat -k:
    ----------------------
    Locks
    address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
    300583dc 0        400d6998 0        HDR+S   100002  207      0

    Here you see a share lock (HDR+S) on a database. tblsnum=100002 represents the database tablespace. rowid=207 is the hexadecimal row ID of the row describing this database in table sysdatabases in the sysmaster database. Table sysmaster:sysdatabases represents the so called database tablespace.

    Databases and their hexadecimal row IDs could be selected with the following SQL query:

    Listing 2. Query on database sysmaster retrieving the row ID of a database tablespace entry
    Query:
    ------
    database sysmaster;
    
    select  name, hex(rowid) hex_rowid
    from sysdatabases;
    
    Result:
    -------
    name                 hex_rowid
    
    linux_mag            0x00000204
    onpload              0x00000206
    stores_demo       0x00000207
    sysmaster            0x00000201
    sysuser              0x00000203
    sysutils             0x00000202
    tpcb                 0x00000208
  • Database Exclusive Lock

    An exclusive lock can be explicitly set on a database. Utilities like dbexport might also place an exclusive lock on the database to be exported.

    • SQL Statement
      • database stores_demo exclusive
    Listing 3. Exclusive lock on a database
    Output from onstat -k:
    ----------------------
    Locks
    address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
    300583dc 0        400d63d8 0        HDR+X   100002  207      0

    Here you see an exclusive lock (HDR+X) on the database with the hexadecimal rowid=207.

Table locks

Like databases, tables could either be locked in exclusive or share mode. An exclusive lock prevents anybody else from reading or changing data in this table. An exception to this are sessions running with an isolation level of dirty read (read uncommitted). They are still able to read (possible inconsistent) data from the exclusively locked table. Locking a table in share mode allows others to select data from this table but prevents data modifications.

  • Table share lock

    A share lock can be explicitly placed on a table. Utilities like onunload or oncheck (depending if the locking mode of the table is page or row) might also place a share lock on the table they operate on.

    • SQL Statement
      • begin; lock table orders in share mode
    Listing 4. Share lock on a table
    Output from onstat -k:
    ----------------------
    Locks
    address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
    300582e0 0        400d63d8 300583dc HDR+S   10013b  0        0

    You see a share lock (HDR+S) and a row ID of zero (rowid=0). A zero row ID represents a table lock. The tablespace number (tblsnum=10013b) conforms to the hexadecimal partition number of this table.

    The affected table could be identified in two ways:

    • oncheck command
    • Data Dictionary Query command
    Listing 5. Identifying a table through its hexadecimal partition number
    Dictionary Query:
    -----------------
    database stores_demo;
    
    select st.tabname, dbinfo("dbspace", st.partnum), hex(st.partnum)
    from systables st
    where hex(st.partnum) = "0x0010013B";
    
    Result:
    ------
    tabname              dbspace              hex_partnum
    
    orders               rootdbs              0x0010013B
    
    
    oncheck command:
    ----------------
    oncheck -pt 0x0010013b
    
    Result:
    -------
    
    TBLspace Report for stores_demo:informix.orders
    
        Physical Address               1:1988
        Creation date                  08/21/2006 20:07:41
        TBLspace Flags                 801        Page Locking
        TBLspace use 4 bit bit-maps
        Maximum row size               80
        Number of special columns      0
    ...
    ...

    Notice that you need to put a 0x00 in front of the hexadecimal number gathered from the onstat -k output, and that you need to convert the letters to uppercase (10013 -> 0x0010013B) in order to find the table through the dictionary query.

    If it is a fragmented table you have to query, the sysfragments dictionary table:

    Listing 6. Identifying a fragmented table through its hexadecimal partition number
    Query:
    ------
    database stores_demo;
    
    select st.tabname, dbinfo("dbspace", sf.partn), hex(sf.partn)
    from systables st, sysfragments sf
    where st.tabid = sf.tabid
    and   sf.fragtype = "T"
    and   hex(sf.partn) = "0x0010013B";
    
    Result:
    -------
    No rows found.

    The table with partition number 0x0010013B in this example is not fragmented, so there exists no entry in dictionary table sysfragments for it. However, fragmented tables have entries in sysfragments, so you are able to identify them with the above mentioned dictionary query.

    The oncheck -pt <hex_partnum> does not work for fragmented tables because it does not display the real tablename, only the partition information.

  • Table exclusive lock

    An exclusive lock can be explicitly set on a table or might be implicitly set by statements like alter table.

    • SQL Statement
      • begin; lock table orders in exclusive mode
    Listing 7. Exclusive lock on table
    Output from onstat -k:
    ----------------------
    Locks
    address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
    300582e0 0        400d63d8 300583dc HDR+X   10013b   0        0

    Here you see an exclusive lock (HDR+X) on the table with the hexadecimal tblsnum=10013b.

Page or row locks

The lock granularity (page or row) could be specified during the creation of a table or later be changed with the alter table statement.

  • New Table
    • create table t1 (f1 int) lock mode (row)
  • Existing Table
    • alter table t1 modify lock mode (page)

The default lock mode, which is used if no lock mode is specified during the creation of a table, could be configured with the onconfig parameter DEF_TABLE_LOCKMODE or through the environment variable IFX_DEF_TABLE_LOCKMODE.. The lock mode setting in a create table statement takes precedence over the settings of the IFX_DEF_TABLE_LOCKMODE environment variable and the DEF_TABLE_LOCKMODE configuration parameter.

Page locking means that IDS always locks a whole database page instead of an individual row even if only a single row is modified. Depending on the row size and configured page size of the underlying dbspace, this behavior might result in reduced concurrency. It is important to notice that page level locking not only affects data pages but also index pages. This further reduces concurrency because index pages normally hold a larger number of entries compared to data pages.

You can determine the current lock mode of a table in two ways:

Listing 8. Determining the lock mode of a table using oncheck (a) or a dictionary query (b)
a) Command:
-----------
oncheck -pt stores_demo:eherber.orders

Output:
-------
TBLspace Report for stores_log:informix.orders

Physical Address               1:3905
Creation date                  05/08/2006 16:47:43
TBLspace Flags                 801        Page Locking
...
...

b) Query:
---------
database stores_demo;

select tabname, locklevel 
from systables 
where tabname = "orders";

Result:
-------
tabname              locklevel

orders               P

The following abbreviations for the locklevel are shown by IDS:

  • B -- Views (Check the locklevel of underlying tables)
  • P -- Page locking
  • R -- Row locking

Generating the necessary alter table commands for converting all tables to row locking can be done with the following meta SQL statement:

Listing 9. Using meta SQL to convert all tables to row locking
Meta SQL:
---------
database stores_demo;

output to alter_table.sql without headings
select "alter table '" || trim(owner) || "'." || trim(tabname) || " lock mode(row);"
from    'informix'.systables
where   tabid > 99
and     tabtype = 'T'
and     locklevel = 'P';

Generated output file 'alter_table.sql':
----------------------------------------
alter table 'informix'.customer lock mode(row);
alter table 'informix'.orders lock mode(row);
...
...
  • Page lock

    Page locks are placed on a database page during the processing of a row if the table has been configured for page level locking. They could be of type exclusive (insert, update, or delete) or share (select with an isolation level of repeatable read).

    • SQL Statement
      • begin; update orders set ship_charge = (ship_charge*1.2) where order_num = 1005
    Listing 10. Exclusive lock on a page
    Output from onstat -k:
    ----------------------
    Locks
    address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
    440cf9bc 0        4506c3d0 0        HDR+S    100002   207         0
    440cfa14 0        4506c3d0 440cf9bc HDR+IX   10013b   0           0
    440d082c 0        4506c3d0 440cfa14 HDR+X    10013b   100         0

    This example shows three locks:

    1. Share lock (HDR+S) on a database. tblsnum=100002 is the partition number of the database tablespace.
      • The share lock prevents other transactions from locking the database exclusively.
    2. Intent exclusive (HDR+IX) lock on a table. tblsnum=0 represents a table lock.
      • The intent exclusive lock prevents other transactions from locking the table in share or exclusive mode.
    3. Exclusive lock on a page (rowid=100. Page locks are represented with a double zero (00) at the end of a row ID.
      • The exclusive lock prevents other transactions from placing share or exclusive locks on this page.
  • Row lock

    Row locks are placed on a database page during the processing of a row if the table has been configured for row-level locking. They could be of type exclusive (insert, update, or delete) or type share (select with an isolation level of repeatable read).

    • SQL Statement
      • begin; set isolation to repeatable read; select * from customer where customer_num = 110
    Listing 11. Share lock on a row
    Output from onstat -k:
    ----------------------
    Locks
    address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
    440cf9bc 0        4506c3d0 0        HDR+S    100002   207         0
    440cfa14 0        4506c3d0 440cf9bc HDR+IS   100139   0           0
    440cfd84 0        4506c3d0 440d082c HDR+S    100139   10a         0
    440d082c 0        4506c3d0 440cfa14 HDR+SR   10013a   10a      K- 1

    This example shows four locks:

    1. Share lock (HDR+S) on a database. tblsnum=100002 is the partition number of the database tablespace.
      • The share lock prevents other transactions from locking the database exclusively.
    2. Intent share (HDR+IS) lock on a table. tblsnum=0 represents a table lock.
      • The intent share lock prevents other transactions from locking the table in exclusive mode.
    3. Share lock on an individual row (rowid=10a).
      • The share lock on the row prevents other transactions from placing an exclusive lock on the same row.
    4. Share lock on index key (K-1).
      • The share lock on the index key prevents other transactions from placing an exclusive lock on the same index key.

    You are able to identify the locked row with the following data dictionary query:

    Listing 12. Identifying a locked row
    Query:
    ------
    database stores_demo;
    set isolation to dirty read;
    
    select *
    from  customer
    where hex(rowid) = "0x0000010A";
    
    Result:
    -------
    customer_num  110
    fname         Roy
    ...
    ...

Index key locks

IDS also places locks on index keys like data rows in order to protect those index keys. Key value locking guarantees that unique keys remain unique because it doesn't allow a second transaction to insert a key with the same value until the transaction that deleted the unique key is committed.

Index key locks are identified by a K in the key#/bsiz column of the onstat -k output.

Database logging modes

Each database created in IDS can have one of four possible logging levels:

  • No logging
  • Buffered logging
  • Unbuffered logging
  • Log mode ANSI

Those logging modes are described in the following sections.

No logging

The database runs without transaction logging. No begin, rollback, or commit statements are allowed in the application and no transactions are written to the logical logs. Each SQL statement is atomic. Rows are locked for the duration of a single SQL statement.

  • Default isolation level
    • Dirty read -- The only available isolation level in these kind of databases.
  • SQL statement
    • create database stores_demo

Buffered logging

Transactions are written to the logical logs. An explicit begin statement is necessary if you want to group several SQL statements in a single transaction. The transaction ends when a commit or rollback statement is issued. Without an explicit begin statement, every SQL statement is treated as a single transaction.

If a commit statement is received, the transaction is not directly written to the logical logs on disk. As buffered logging implies, the transaction is held in the log buffer residing in the shared memory. The log buffer is written to disk as soon as it fills, or a transaction in an unbuffered or mode ANSI database is committed in the meantime.

You have to balance the benefits of an increased transaction throughput against the risk of loosing some transactions in case of a crash. Fast disks or a battery powered ramdisk should be the preferred way for storing the logical logs if you need high transaction throughput and the database contains critical business data.

  • Default isolation level
  • The default isolation level for this type of database is committed read, but the user or application could select another one with the set isolation to <isol_level> statement.
  • SQL statement
    • create database stores_demo with buffered log

Unbuffered logging

It is like buffered logging, but the log buffer is immediately flushed to disk as soon as a commit statement is received. You can't loose any transactions with this logging mode.

  • Default isolation level
  • A user or application could select a different isolation level with the set isolation to <isol_level> statement.
  • SQL statement
    • create database stores_demo with log

Log mode ANSI

The default isolation level for this type of database is repeatable read, which means that a shared lock is placed on every row processed. This behavior could lead to locking conflicts and timeouts. The isolation level could be switched with the set isolation to <isolation_level> statement.

  • A user or application could select a different isolation level with the set isolation to <isol_level> statement.
  • SQL statement
    • create database stores_demo with log mode ansi
Database logging modes and default isolation levels
Database logging modeCreate statementDefault isolation level
No loggingcreate database stores_demoDirty read
Buffered loggingcreate database stores_demo with buffered logCommitted read
Unbuffered loggingcreate database stores_demo with logCommitted read
Mode ANSI (unbuffered logging)create database stores_demo with log mode ansiRepeatable read

The current logging mode of a database could be determined with either the onmonitor utility or a sysmaster query:

  • onmonitor (status-databases)
    • N -- No logging
    • B -- Buffered logging
    • U -- Unbuffered logging
    • U* -- Mode ANSI (unbuffered logging)
  • sysmaster query
    Listing 13. Determining the logging mode of a database
    Query:
    ------
    database sysmaster;
    
    select name, is_logging, is_buff_log, is_ansi
    from sysmaster:sysdatabases;
    
    Result:
    -------
    name         stores_ansi
    is_logging   1
    is_buff_log  0
    is_ansi      1
     
    name         stores_demo
    is_logging   1
    is_buff_log  0
    is_ansi      0

You can switch the logging mode of a database with the ontape or ondblog utility. Note that IDS does not allow you to switch a mode ANSI database back to any other logging mode (once it is an ANSI, it is forever an ANSI).

Isolation levels

IDS provides, depending on the logging mode of the database, four different isolation levels. This section discusses those isolation levels in detail.

IDS isolation levels and their ANSI counterparts
Informix SQLANSI SQL
Dirty readRead uncommittted
Committed readRead committed
Cursor stabilityNot available
Repeatable readSerializable

Dirty read (ANSI: Read uncommitted)

If you read data with this isolation level, you will not lock anything yourself and you will not be blocked by any existing locks from other users. However, you might read inconsistent data; data that has not been committed yet.

Dirty read is the only possible isolation level for databases created without logging.

  • Informix SQL
    • set isolation to dirty read [retain update locks]
  • ANSI SQL
    • set transaction isolation level read uncommitted

Committed read (ANSI: Read committed)

This isolation level ensures that only committed data is read.

Sessions running with committed read check if it is possible to set a shared lock on a row but do not set it. This ensures that no rows could be read that are currently updated in a parallel running transaction. However, after reading a row, a parallel transaction might update it because a committed read does not set any locks to prevent this.

Committed read is the default isolation level for databases created with buffered or unbuffered logging, but not mode ANSI.

  • Informix SQL
    • set isolation to committed read [retain update locks]
  • ANSI SQL
    • set transaction isolation level read committed

Cursor stability (ANSI: --)

An isolation level of cursor stability is only relevant for cursors declared with the FOR UPDATE clause.

IDS places an update lock on the current fetched row. If the row is updated, the lock is converted to an exclusive lock that is held until the transaction ends, independent from the current position of the cursor. If the next row is fetched without updating the current one, the update lock on the previous row is released and a new one is placed on the current row. This behavior could be changed if you add the retain update locks to the set isolation statement. In this case, an update lock on a row is not released if you fetch the next row.

Update locks could only be placed on rows that currently have no update or exclusive locks on them. However, once the update lock is placed, other sessions are not prevented from placing a shared lock on the same row. This results in an error if you try to update that row, and IDS could not promote the update lock in an exclusive lock due to the shared lock(s) on it. This is the desired behavior. If you want to be sure that you can update the row later in the transaction, you have to perform a dummy update that promotes the update lock in an exclusive lock. This prevents parallel sessions from placing shared locks on the same row.

  • Informix SQL
    • set isolation to cursor stability [retain update locks]
  • ANSI SQL
    • --

Repeatable read (ANSI: Serializable)

An isolation level of repeatable read places a share lock on every row (or page if page level locking is active for this table) processed. This prevents other users from changing the row.

The retain update locks clause is not supported with this isolation level because repeatable read automatically retains share or update locks.

Repeatable read is the default isolation level for databases created with mode ANSI logging.

  • Informix SQL
    • set isolation to repeatable read
  • ANSI SQL
    • set transaction isolation level serializable

Differences between Informix SQL and ANSI SQL

There are two important differences between the Informix SQL statement set isolation to <isol_level> and the ANSI SQL statement set transaction isolation level <isol_level>.

The Informix implementation is session based. That means the isolation level that you set is active during the whole database session until you issue another set isolation to <isol_level> statement.

The ANSI implementation is transaction based. That means the isolation level is only active in the current transaction. As soon as the transaction ends, the isolation level gets switched back to the default for the current opened database. For more information, refer to the Database logging modes section.

You are also not able to change the isolation level inside a transaction using the ANSI SQL set transaction isolation level <isol_level> statement. This is not allowed. However, the Informix implementation allows you to switch between isolation levels inside a transaction.

Session isolation level

You can determine the current isolation level of a database session with the onstat -g sql command:

Listing 14. Listing isolation levels of individual database sessions
Command:
---------
onstat -g sql

Output:
-------
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain
50    -              stores_demo        RR  Wait 10    0    0    9.03 Off
45    -              stores_demo        DR  Not Wait   0    0    9.03 Off
...
...

The current isolation level is shown under Iso Lvl with the following abbreviations:

  • DR -- Dirty read
  • CR -- Committed read
  • CS -- Cursor stability
  • RR -- Repeatable read

A reasonable IDS feature request would be the ability to specify a default isolation level at the database level. The Informix XPS data server already contains this feature in form of the sysdbopen() stored procedure. A backport to IDS would be welcome.

Conclusion

Now that you have an overview about lock types, locking granularity, database logging modes, and isolation levels the following themes will be discussed in the second part of the series:

  • Lock wait time
  • Dynamic lock allocation
  • Deadlocks
  • Lock wait situations

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=157904
ArticleTitle=Informix Dynamic Server locking, Part 1: Understand locking behavior and analyze locking conflicts in IDS
publish-date=09072006