INFORMIX
Informix Guide to SQL: Tutorial
Chapter 7: Programming for a Multiuser Environment
Home Contents Index Master Index New Book

Locking with Other Database Servers

 Universal Server manages its own locking so that it can provide the different kinds of locks and levels of isolation described in the preceding topics. Other Informix database servers implement locks using the facilities of the host operating system and cannot provide the same conveniences.

Some host operating systems provide locking functions as operating-system services. In these systems, database servers support the SET LOCK MODE statement.

Some host operating systems do not provide kernel-locking facilities. In these systems, the database server performs its own locking based on small files that it creates in the database directory. These files have the suffix .lok.

To determine the kind of system in which your database server is running, execute the SET LOCK MODE statement and test the error code, as shown in the following fragment of INFORMIX-ESQL/C code:

If the database server does not support the SET LOCK MODE statement, your program is effectively always in NOT WAIT mode; that is, whenever it tries to lock a row that is locked by another program, it receives an error code immediately.

Isolation While Reading

Informix database servers other than Universal Server do not normally place locks when they fetch rows. Nothing exists that is comparable to the shared locks that Universal Server uses to implement the Cursor Stability isolation level.

If your program fetches a row with a singleton SELECT statement or through a cursor that is not declared FOR UPDATE, the row is fetched immediately, regardless of whether it is locked or modified by an unfinished transaction.

This design produces the best performance, especially when locks are implemented by writing notes in disk files, but you must be aware that the program can read rows that are modified by uncommitted transactions.

You can obtain the effect of Cursor Stability isolation by declaring a cursor FOR UPDATE, and then using it for input. Whenever the database server fetches a row through an update cursor, it places a lock on the fetched row. (If the row is already locked, the program waits or receives an error, depending on the lock mode.) When the program fetches another row without updating the current one, the lock on the current row is released, and the new row is locked.

To ensure that the fetched row is locked as long as you use it, you can fetch through an update cursor. (The row cannot become stale.) You are also assured of fetching only committed data because locks on rows that are updated are held until the end of the transaction. Depending on the host operating system and the database server, you might experience a performance penalty for using an update cursor in this way.

Locking Updated Rows

When a cursor is declared FOR UPDATE, locks are handled as follows. Before a row is fetched, it is locked. If it cannot be locked, the program waits or returns an error.

The next time a fetch is requested, the database server notes whether the current row is modified (using either the UPDATE or DELETE statement with WHERE CURRENT OF) and whether a transaction is in progress. If both these things are true, the lock on the row is retained. Otherwise, the lock is released.

So if you perform updates within a transaction, all updated rows remain locked until the transaction ends. Rows that are not updated are locked only while they are current. Rows updated outside a transaction, or in a database that does not use transaction logging, are also unlocked as soon as another row is fetched.



Informix Guide to SQL: Tutorial, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.