Concurrency and Locking
By Ron M. Flannery
In a multi-user system, you'll almost certainly need to have more than one user access the same row(s) of data at the same time. This is especially true in OLTP systems. The ability to allow multiple users to access the same rows of data is known as concurrency. Concurrency control is essential for performance and integrity. Informix handles concurrency through various strategies, which will be described in this article.
Informix uses different locking strategies to control concurrency. It is very important that you consider these locking strategies when designing any multi-user application. Locks can be placed on these types of objects : databases, tables, pages, and rows. Here are the three types of locks that can be applied to these objects:
1. Shared. A shared lock is essentially a read-only lock on an object. It allows you to read the object and also allows others to place shared locks on the object. No one can update the object when a shared lock exists on it.
2. Exclusive. An exclusive lock gives the user exclusive access to the object. This type of lock is used when the user is going to change the object. No other locks are allowed when an exclusive lock exists. If an exclusive lock is attempted and a shared lock exists, an error will occur if the lock is not released before the wait time expires (see "lock wait times" below).
3. Promotable. A promotable lock is a hybrid between a shared and exclusive lock. This lock only applies to rows. The user is basically saying "I want to update this row, but not right now." This type of lock can be placed when shared locks already exist on the data. When the user fetches the row, a shared lock is obtained. When he is about to update the row, the lock is upgraded to an exclusive lock and no other lock (not even shared) can exist.
Here is a description of how each of the objects interact with locking:
Database - Simply opening a database (with a "DATABASE dbname " statement) places a shared lock on the database. To exclusively lock a database, use the "DATABASE dbname EXCLUSIVE" command. An exclusive lock is automatically placed on a database by commands such as dbexport and onunload .
Table - You can lock a table with the LOCK TABLE command. A table can be locked in shared mode with LOCK TABLE tabname IN SHARE MODE or exclusively with LOCK TABLE tabname IN EXCLUSIVE MODE. A table level lock is automatically done by SQL commands such as ALTER TABLE, CREATE INDEX, and RENAME COLUMN.
Page and Row - Page or row level locking is done automatically when a row is locked. The scope of the lock (page or row) is determined by the definition of the table itself . The CREATE TABLE and ALTER TABLE statements allow you to specify "lock mode." Tables defined as "lock mode page" lock the whole page containing the row while tables defined as "lock mode row" only lock the specific row.
Index key - Informix automatically does an index key lock in certain situations. An index key lock is done to preserve an index value that does not exist. This is done when a user might be adding or deleting a row; Informix must wait until the update is completed and thus preserves the index key value. This is done "behind the scenes" but is something you should know.
Lock wait times
You can define how long a user process can wait for a lock by using the SET LOCK MODE command. This can be done interactively (through dbaccess or isql), in a program, or SQL file. The syntax is:
SET LOCK MODE TO NOT WAIT; (this is the default behavior) - or -
SET LOCK MODE TO WAIT [seconds];
If you have used the SET LOCK MODE TO NOT WAIT command, statements trying to obtain a lock will immediately return an error if the lock can not be granted.
The SET LOCK MODE TO WAIT command will wait until the requested lock becomes available. If seconds is specified, the process will wait that many seconds for the requested lock to become available. If seconds is not specified, the process will wait indefinitely, so be careful.
A deadlock can occur when more than one user process is trying to perform certain locks on the same object (table, page, etc.). It happens when all the processes have used SET LOCK MODE TO WAIT. When the transaction involves only one database server, Informix detects this condition immediately and reports an error. If the transaction involves more than one database server, a true deadlock can occur unless the Online administrator has set the ONCONFIG parameter DEADLOCK_TIMEOUT on the involved servers.
Rules of locking
To maintain consistency of a database, locks need to follow certain rules. If a lock can not be granted because it breaks the rules, an error will occur. The same rules apply to each object that can be locked (database, table, page, row):
1. If the lock being requested is shared (a shared lock or the initial step of a promotable lock) and there are no exclusive locks, the lock is granted.
2. If the lock being requested is exclusive (an exclusive lock or the update step of a promotable lock) and there are no shared or exclusive locks, the lock is granted.
3. If the lock can not be granted immediately, the process will wait the amount of time specified with SET LOCK MODE. If the lock can not be granted in this amount of time, an error will be reported to the calling process.
Logged and non-logged databases
An Informix database can be logged or non-logged. In a logged database, every transaction (insert, update, and delete) is tracked and must be committed to the database. In a non-logged database, this is not the case.
A logged database enables transactions by using the "BEGIN WORK COMMIT WORK" syntax in programs and SQL files. If a BEGIN WORK is not issued, a "singleton transaction" is performed, meaning that a transaction is automatically done. A logged database is necessary in applications that require a series of commands to complete for an update to be successful. A logged database does, however, require more overhead in locking and transaction logging.
In a logged database, you need to declare all cursors WITH HOLD if any COMMIT WORK statements occur within them. If this is not done, the cursors are closed.
An update cursor is declared by using the FOR UPDATE clause in a DECLARE statement. An update cursor will place a promotable lock on each row it fetches. If the row is fetched successfully, the program knows that no other program can update the row. Other users can continue to read the row. When the user is ready to update or delete the row, the lock is promoted to exclusive (if possible) and the action is performed.
The behavior of an update cursor is different between logged and non-logged databases. In a logged database, all updated rows hold a lock until the transaction completes. In a non-logged database, each lock is released when the row is written to disk. This behavior can vary slightly, depending on the isolation level (see next section).
The isolation level helps you control the level of concurrency and how your program will work with other active transactions. You don't have to set isolation level; there are defaults (see descriptions below). The SET ISOLATION command uses the syntax "SET ISOLATION TO isolation_level " where isolation_level is one of the following:
Dirty read - This isolation level simply reads a row from the database without placing locks or checking if any other programs are updating the row. It is a good option for static tables or low usage applications. This is the only isolation level available in databases without logging.
Committed read - If isolation level is committed read, Informix will not read rows that have a pending update (i.e., rows that have an exclusive lock on them). This is the default isolation for logged, non ANSI-compliant databases.
Cursor stability - This isolation level will place a lock on each row being fetched from the database. For a normal cursor, it will place a shared lock; for an update cursor, a promotable lock. When a row is updated, the lock is held until the end of the transaction (i.e., until the next COMMIT WORK or the cursor is closed). If the row is not updated, the lock is freed.
Repeatable read - The repeatable read isolation level is very similar to cursor stability except that it maintains the lock on every row it fetches, even for rows that aren't updated. The locks are shared for an ordinary cursor and exclusive for an update cursor. The locks aren't freed until the cursor is closed or the transaction ends. This type of isolation level is good for cursors that must ensure that none of the retrieved rows are updated while the user scrolls through them. This is the default isolation level for ANSI-compliant databases.
Tying it together
We've now learned the following:
1. There are three different kinds of locks: shared, exclusive, and promotable.
2. There are five types of database objects that can be affected by locks: database, tables, pages, rows, and index keys. The type of object being locked is known as the scope of the lock.
3. Lock wait times determine how long a program will wait for a lock before an error occurs.
4. Logged and non-logged databases handle locking differently.
5. Update cursors place locks on rows. In logged databases, the lock remains until the end of the transaction.
6. Isolation level is used to control the concurrency of your transactions. The types of isolation levels are dirty read, committed read, cursor stability, and repeatable read. Non-logged databases only allow dirty read.
How does this all fit together? It is generally best practice to include SET LOCK MODE and SET ISOLATION statements in your programs, though the default behavior might do. This is best illustrated through an example. Here is a 4GL program sample that will help you understand locking and isolation level. I will explain what happens in some different scenarios.
If this is a non-logged database, each time a row is fetched a promotable lock is obtained. This means that the lock is shared after a row is initially fetched. If the row is updated, an exclusive lock is obtained. When the row is written to disk and the user fetches the next row, the lock is freed.
If this is a logged, non ANSI-compliant database, the behavior of this code will differ only in that a lock will be held on all updated rows until the CLOSE update_curs statement. This is true because the default isolation level is committed read for a logged, non ANSI-compliant database.
If this is a logged database and the statement SET ISOLATION TO repeatable read is included before the FOREACH, a lock will be held on every row that is fetched until the "CLOSE update_curs" statement. This includes rows that are not updated.
If update_curs is not declared FOR UPDATE, you can still include the "UPDATE customer" statement. The program will simply use less locks: A shared lock on each row fetched (depending on isolation level) and an exclusive lock when the row is updated (the lock might be held, depending on isolation level). Remember that if this is a logged database, you need to declare update_curs WITH HOLD so it is not closed during the UPDATE.
Running out of locks
As with other system resources, there are only a finite number of locks. We've probably all seen "ISAM error: No more locks." at one time or another. You must be very careful in controlling the number of locks that your application uses. The easiest way to run out of locks is to update every row in a table:
update customer set cust_status = "A";
In a logged database, this will place an exclusive lock on every row in the table until the transaction is complete. If there are 100,000 rows in the table and your system only allows 10,000 locks (LOCKS parameter in ONCONFIG), you're in trouble. Two ways around this are:
1. Lock the whole table (lock table customer in exclusive mode)
2. Create a program that will read a row, update it, and go to the next record.
Also, be mindful of creating cursors that will lock every row. If you have a FOR UPDATE cursor with isolation level of repeatable read or committed read, be sure not to read too many rows at one time.
There are many different variations on locking strategies. Don't worry: If you understand the concepts of locking, lock scope, logged/non-logged databases, and isolation level, you should be well on your way.
Ron M. Flannery (email@example.com)
Author of the upcoming book, "Special Edition, Using Informix" by Que Publishing
President of One Point Solutions, Inc. (www.one-point.com)
President of Michigan Informix User Group (www.zenacomp.com/miug/)
Phone: (248)-887-8470 Fax: (248)-887-5698