IBM Support

User session running select count(*) in dirty read on a table getting errors 211 / 107 if 'alter table type' was executed on that table due to lock on systables

Technote (FAQ)


When an alter table type was run against a table, other sessions won't be able to run select count(*) in dirty read due to lock on systables and will get error 211 / 107


The output of 'onstat -k' shows an update locks on systables was placed by ALTER TABLE TYPE (RAW):
address wtlist owner lklist type tblsnum rowid key#/bsiz
44196358 0 44c17ff8 44196688 HDR+X 100006 a0c 0 U
44196820 0 44c17ff8 44196600 HDR+X 100124 0 0

as well as an exclusive lock on the table to alter.

Only the update lock on systables will lead other sessions in dirty read to not being able to run such a simple command 'select coun(t*)' which reads from systables.

If the alter table is executed outside of a transaction, the lock will be release quickly.
If the alter table is executed following this scenario:

alter table my_table type (raw);

[Any SQL queries on the table like inserts]


the update lock won't be released until after the transaction has been commited and all sessions running the 'select count(*)' on the my_table will get error 211 / 107.


To avoid this, you will have to execute the alter table outside of the transaction so that the update lock on systables is taken a very short time and will reduce this concurrency problem.

Document information

More support for: Informix Servers

Software version: 11.70, 12.1

Operating system(s): AIX, HP-UX, Linux, OS X, Solaris, Windows

Software edition: Developer, Enterprise, Express, Growth, Innovator, Ultimate, Workgroup

Reference #: 1659749

Modified date: 14 January 2014

Translate this page: