Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Locks question

Status
Not open for further replies.

yevgeni123

Programmer
May 2, 2008
2
IL
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 !
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top