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.
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.
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.
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
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
It can take the value of either
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
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 -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 -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
dbaccessto 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
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:
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.
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.
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.
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
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 lock||Hold U lock||Hold S lock||Hold IS lock||Hold SIX lock||Hold IX lock|
|Request X lock||No||No||No||No||No||No|
|Request U lock||No||No||Yes||Yes||No||No|
|Request S lock||No||Yes||Yes||Yes||No||No|
|Request IS lock||No||Yes||Yes||Yes||Yes||Yes|
|Request SIX lock||No||No||No||Yes||No||No|
|Request IX lock||No||No||No||Yes||No||Yes|
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.
onstat -g sqloutput
$ 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:
In session B, the query result is now returned after the commit finishes.