Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 6: Data concurrency

Monitoring locks

Monitoring locks using the onstat utility

The locks used by the database server are held in an internal lock table. Use the onstat -k command to monitor the type and granularity of the locks being held.

The output of the onstat -k command has the following fields:

Back to top

Example of onstat -k output

This example uses a non-ANSI-compliant logging database and two tables with the following:

CREATE DATABASE db1 WITH LOG;

CREATE TABLE t1 (c1 int);
CREATE TABLE t2 (c1 int) LOCK MODE ROW;

The following values are inserted into the tables:

INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2);

The following statements are executed:

BEGIN WORK;
UPDATE t1 SET c1=5;
UPDATE t2 SET c1=5;

The onstat -k output:

$ onstat -k

IBM Informix Dynamic Server Version 11.50.FC4     -- On-Line -- Up 2 days 21:39:44 
-- 157696 Kbytes

Locks
address       wtlist   owner        lklist       type     tblsnum  rowid    key#/bsiz
10a3134d8     0        111535cd8    0            HDR+S    100002   204         0
10a44bcd8     0        111535488    0                S    100002   204         0
10a44bd58     0        111536528    0                S    100002   204         0
10a44c4d8     0        111537e18    0            HDR+S    100002   202         0
11348b028     0        111537e18    10a44c4d8    HDR+IX   1001ef   0           0
11348b0a8     0        111537e18    11348b028    HDR+X    1001ef   100         0
11348b128     0        111537e18    11348b0a8    HDR+IX   1001f0   0           0
11348b2a8     0        111537e18    11348b128    HDR+X    1001f0   101         0     U
11348b328     0        111537e18    11348b2a8    HDR+X    1001f0   102         0     U
 9 active, 80000 total, 16384 hash buckets, 2 lock table overflows

This output has the following information:

• Table lock:
  • Looking at the row with address 11348b028, the rowid is 0, indicating table lock. The IX indicates intent exclusive type.
• Page lock:
  • Looking at the row with address 11348b0a8, the last two characters of the rowid are 00, which indicates a page lock. The type X means exclusive Lock. This is for table t1, which has page-level locking mode.
• Row lock:
  • Looking at the row with the address 11348b328, the rowid is 0x102, which indicates a row lock and an exclusive lock in this case. This is for table t2, which has row-level locking mode.
• Database lock:
  • Looking at the row with the address 10a3134d8, there is a row lock on a table. However, the tblsnum 0x100002 is a database tblspace, which is a special table that keeps the list of databases in the server. Thus, a lock on one of its rows is equivalent to locking a database. The S type lock is a shared lock.

To find out what the table name is for the tblsnum in the onstat -k, use the following statement on the systables table:

SELECT * FROM systables WHERE partnum=1049072;

Output:

tabname          t2
owner            informix
partnum          1049072
tabid            101
rowsize          4
ncols            1
nindexes         0
nrows            0.00
created          07/20/2009
version          6619137
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

Back to top

Monitoring locks using the syslocks table in the sysmaster database

You can also monitor locks using the syslocks table in the sysmaster database. Table 3 lists the columns in the table and their descriptions:

Run the following query in db1:

CREATE DATABASE db1 WITH LOG;

CREATE TABLE t3 (c1 int);
CREATE INDEX idx1 ON t3(c1);

INSERT INTO t3 VALUES(10);
INSERT INTO t3 VALUES(11);
INSERT INTO t3 VALUES(12);
INSERT INTO t3 VALUES(13);


BEGIN WORK;
UPDATE t3 SET c1=99 WHERE c1=10;

Select the information about db1 from syslocks:

SELECT * FROM syslocks WHERE dbsname='db1';

Output:

dbsname  db1
tabname  t3
rowidlk  0
keynum   0
type     IX
owner    109
waiter

dbsname  db1
tabname  idx1
rowidlk  0
keynum   1
type     X
owner    109
waiter

dbsname  db1
tabname  t3
rowidlk  256
keynum   0
type     X
owner    109
waiter

The output clearly shows that locks are held for objects in db1. The index name is listed in the tabname column. Note that the rowidlk is interpreted the same way as the rowid column in the onstat -k output. If you convert 256 to hexadecimal, it is 0x100, indicating a page lock.

4 of 8 | Previous | Next

Comments

Back to top

Table of contents

• Before you start
• Understanding locks
• Deadlocks
• Monitoring locks
• Isolation levels
• Conclusion
• Resources
• About the author

---

4 of 8 | Previous | Next

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers