Query with COUNT(*) fails with errors -252/-113

Problem(Abstract)

When running a select query that contains COUNT(*) the following errors are returned:
> echo "select count(*) from customer;" | dbaccess stores_demo

Database selected.

252: Cannot get system information for table.

113: ISAM error: the file is locked.
Error in line 1
Near character position 61


Database closed.

Symptom

Errors -252/-113 are returned:

252: Cannot get system information for table.

113: ISAM error: the file is locked.

Cause

Isolation level Committed Read or Last Committed is used and there is an exclusive lock on the table

Diagnosing the problem

Using 'onstat -g sql' check the isolation level that sessions are using:

> onstat -g sql

IBM Informix Dynamic Server Version 11.70.FC3 -- on-Line -- Up 00:52:17 -- 182532 Kbytes

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
39 - stores_demo CR Not Wait 0 0 9.24 Off
27 sysadmin DR Wait 5 0 0 - Off
26 sysadmin DR Wait 5 0 0 - Off
25 sysadmin DR Wait 5 0 0 - Off

In the above example session #39 is using an isolation level of Committed Read (CR).

Then check the table for exclusive locks. To do so, first get the partnum of the table. This can be done by running the 'oncheck -pt <dbname:tabname>' command:

> oncheck -pt stores_demo:customer

TBLspace Report for stores_demo:informix.customer

Physical Address 2:678
Creation date 08/05/2011 15:55:41
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 134
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 129
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 3
Number of data pages 2
Number of rows 28
Partition partnum 1049059
Partition lockid 1049059
<...>

Next, convert the partnum value into HEX: 1049059 = 0x1001e3

Finally, grep the partnum value (without the leading 0x) from 'onstat -k' output:

> onstat -k | grep 1001e3
4ebef028 0 4b85c418 4ebeed28 HDR+X 1001e3 102 0 D
4ebef328 0 4b85c418 4ebef228 HDR+X 1001e3 103 0 D
4ebef528 0 4b85c418 4ebef428 HDR+X 1001e3 104 0 D
4ebef728 0 4b85c418 4ebef628 HDR+X 1001e3 105 0 D
4ebef828 0 4b85c418 4ebeefa8 HDR+X 1001e3 106 0 D
4ebefa28 0 4b85c418 4ebef9a8 HDR+X 1001e3 107 0 D
4ebefb28 0 4b85c418 4ebefaa8 HDR+X 1001e3 108 0 D
4ebefd28 0 4b85c418 4ebefca8 HDR+X 1001e3 109 0 D
4ebeff28 0 4b85c418 4ebefea8 HDR+X 1001e3 10a 0 D
4ebf0128 0 4b85c418 4ebf00a8 HDR+X 1001e3 10b 0 D
4ebf0328 0 4b85c418 4ebf02a8 HDR+X 1001e3 10c 0 D
4ebf0528 0 4b85c418 4ebf04a8 HDR+X 1001e3 10d 0 D
4ebf0728 0 4b85c418 4ebf06a8 HDR+X 1001e3 10e 0 D
4ebf0928 0 4b85c418 4ebf08a8 HDR+X 1001e3 201 0 D
4ebf0b28 0 4b85c418 4ebf0aa8 HDR+X 1001e3 202 0 D
4ebf0ca8 0 4b85c418 4ebf0c28 HDR+X 1001e3 203 0 D
4ebf0ea8 0 4b85c418 4ebf0e28 HDR+X 1001e3 204 0 D
4ebf10a8 0 4b85c418 4ebf1028 HDR+X 1001e3 205 0 D
4ebf12a8 0 4b85c418 4ebf1228 HDR+X 1001e3 206 0 D
4ebf1428 0 4b85c418 4ebf13a8 HDR+X 1001e3 207 0 D
4ebf1628 0 4b85c418 4ebf15a8 HDR+X 1001e3 208 0 D
4ebf1828 0 4b85c418 4ebf17a8 HDR+X 1001e3 209 0 D
4ebf1a28 0 4b85c418 4ebf19a8 HDR+X 1001e3 20a 0 D
4ebf1c28 0 4b85c418 4ebf1ba8 HDR+X 1001e3 20b 0 D
4ebf1e28 0 4b85c418 4ebf1da8 HDR+X 1001e3 20c 0 D
4ebf2028 0 4b85c418 4ebf1fa8 HDR+X 1001e3 20d 0 D
4ebf2228 0 4b85c418 4ebf21a8 HDR+X 1001e3 20e 0 D
4ebfeda8 0 4b85c418 4ebfee28 HDR+X 1001e3 101 0 D
4ebfee28 0 4b85c418 44452788 HDR+IX 1001e3 0 0

In the above example, the table has an intent-exclusive lock on the whole table (HDR+IX) and some exclusive locks on its rows (HDR+X).

Resolving the problem

Set the isolation level to Dirty Read at the session level before running the query:

> echo "set isolation to dirty read; select count(*) from customer;" | dbaccess stores_demo

Database selected.

Isolation level set.


(count(*))

28

1 row(s) retrieved.

Database closed.

Another option is to set lock mode to wait in order to allow the query to wait until the lock is released:

> echo "set lock mode to wait; select count(*) from customer;" | dbaccess stores_demo