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
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.
More support for:
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: 26 June 2014