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)


Question

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

Cause

The output of 'onstat -k' shows an update locks on systables was placed by ALTER TABLE TYPE (RAW):
Locks
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:

begin;
alter table my_table type (raw);

[Any SQL queries on the table like inserts]

commit;

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.

Answer

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.

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Informix Servers

Software version:

11.70, 12.1

Operating system(s):

AIX, HP-UX, Linux, Mac OS X, Solaris, Windows

Software edition:

Developer, Enterprise, Express, Growth, Innovator, Ultimate, Workgroup

Reference #:

1659749

Modified date:

2014-01-14

Translate my page

Machine Translation

Content navigation