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:

					
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
• committed read
• 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
• committed read
• 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.

• Default isolation level
• repeatable read

• 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

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.

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

• 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

• IDS 10 Information Center: Get easy Web-based access to the whole documentation set of IDS Version 10.
  
  
• developerWorks Informix Zone: Read articles and tutorials and connect to other resources to expand your Informix skills.
  
  
• IDS Developer and User Forum: Find a place for IDS developers and users to share their questions, thoughts, and ideas with others.
  
  
• IDS Support: Find helpful technotes and other support information regarding IDS.
  
  
• Informix Webcasts: Find the latest information about Informix webcasts.
  
  
• Visit the International Informix User Group to network with thousands of Informix users world-wide.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Download a free trial version of IDS Version 10.
  
  
• Find other Informix evaluation and trial software.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  
• Visit the Informix-Newsgroup comp.databases.informix via Google discussion group.
  
  

About the author

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

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Lock types
• Locking granularity
• Database logging modes
• Isolation levels
• Conclusion
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers