Locks question

[yevgeni123]

Hello to everyone

I'm completely new in Informix but had a lot of experience with DB2

Here is my problem:

Assume that my TABLE has an integer ZZZ column. Further assume that I explicitly create an index on ZZZ.

Table was created lock mode ROW and ZZZ as primary key

Now consider this "start of transaction":

set isolation to repeatable read;
begin;
select * from TABLE where ZZZ=5;

I expected that it should place a lock ONLY ON those records for which the value of ZZZ=5.

In my case ZZZ is a primary key , so there is only one record where ZZZ=5

Now ...
running onstat -k, I see that the WHOLE TABLE IS LOCKED (rowid=0).

Since part of my design relies on the expected behavior of repeatable read, what am I missing?


Thanks in advance for any help !

 

[mberni]

yes, rowid=0 means that it is a table lock.
no, it does not mean that it is an exclusive lock.

look at the type-column:

type Uses the following codes to indicate the type of lock:
HDR Header
B Bytes
S Shared
X Exclusive
I Intent
U Update
IX Intent-exclusive
IS Intent-shared
SIX Shared, intent-exclusive

[taken from Informix Administrators reference]

So i guess you see "IS" there.

Try to insert new rows from another session and you will see that your lock is not exclusive.