[cefe]

Hello

I have an informix database called "test" (buffered log) with one table, very simple. It has two columns: id (smallint) and name (char(20)).
There are two rows in the table.

I open a session.
1. Start a transaction.
2. Select * from test where id=1 for update

I open a second session
1. Start a transaction
2. Select * from test where id=2 for update
3. Get this error message:

"244 Could not do a physical order read to fetch next row
107: ISAM error: Record is lock"

I don't understand why record with id=2 is locked if I'm selecting for update record with id=1.

I would thank you very much any help.

[Roelwe]

You table is probably set to page locking.
Check the systables table for the field locktype. If it is R or P (row or page).

You can alter this with
ALTER TABLE tablename LOCK MODE ROW;

[cefe]

I have taken a look at the systables table and locklevel=R. ¿?

Thanks.

[cefe]

Does anybody knows any other reason that explain why this lock occurs if lock mode is set to row?

Thanks

[Roelwe]

Normally, it's not possible. Depends a bit on the ISOLATION level.
or the lock you placed.

Check with onstat -k or onstat -K, and verify it with http://www.oninit.com/onstat

Are you sure you looked at the correct record while checking the systables table? In many cases, the default locking level is PAGE.

You can check it with also with
dbschema -t tabname -d databasename -ss.

[cefe]

I have been using "onstat -k" and this is the result

1.I open a session
2. onstat -k
There is 1 lock
HDR+S with rowid=206

2.Begin Work
3. Select * from test where id=1 for update
4. onstat -k

There are 3 rows (3 locks)
HDR+S with rowid=206
HDR+U with rowid=103
HDR+IX with rowid=0 (I understand that this is a table lock)

It's sure that lock level in "test" table is ROW (I have checked systables table)

Any idea?

Thanks

[Roelwe]

Hi what client tool do you use to test this?

Could you try it with two dbaccess sessions?
instead of select ... for update, could you open two sessions in dbaccess wher eyou just do the update and leave the transaction open:

session1:
begin work;
update test set field = newvalue
where id = 1;

session2:
begin work;
update test set field = newvalue
where id = 2;

This works!
Both transactions are left open and two locks are placed on the correct table.
I suggest you trace the communication between whatever client tool you use and the Informix server. Informix has tracing facilities in ODBC and the SQLIDEBUG variable.

[cefe]

I have follow your instructions and get an error message "244 Could not do a physical order read to fetch next row" when I try to update the record with id=2 from the second session of dbaccess.

After updating the record with id=1 in the first session of dbaccess, onstat -k give me the following result:

3 locks with rowid=206
HDR+
S
S

1 lock with rowid=103
HDR+X

1 lock with rowid=0
HDR+IX

Thanks

[Roelwe]

err 244:
Could not do a physical-order read to fetch next row.

The database server cannot read the disk page that contains a row of a table. Check the accompanying ISAM error code for more information. A hardware problem might exist, or the table or index file might have been corrupted. Unless the ISAM error code or an operating-system message points to another cause, run the bcheck or secheck utility to verify file integrity.

what is the isam err number?

[cefe]

The ISAM error is 107 record is locked..

[Roelwe]

I'm sorry, I don't see the light on this one. Perhaps Eric can help us out here?

[cefe]

Thank you very much for your help

I would like to know what result do you get when you do the following if possible:

Create a little database with a table called "test" with two columns (id, name)
Insert two records with id=1 and id=2

From dbaccess:

1. Begin work
2. update test set name='proof' where id=1

From console: onstat -k

How many locks do you see?
Is there a lock with rowid=0 (table lock)?

When I do this I have a lock with rowid=0 type HDR+IX

[eherber]

If you update a row in a table, you will always have
an IX-Lock on the table itself beside the lock on the
individual row. This is an Intent-Exclusive-Lock and
prevents other users from exclusively locking the table
or dropping the table. However this does not prevent
other users from placing locks on individual rows inside
that table.

Your problem seems to be that you have no index
on your test table. The first session locks places an
U-Lock (promotable lock) on the record with id=1. The second session has to locate the record with id=2 and because there is no index on the table, the databaseserver does a sequential scan (you can check this with 'set explain on'). Now the DBMS has the problem that it can't
read the record with id=1 beause it is already locked by
session 1. Because the DBMS can't read the row it is
not able to decide if this row would meet the criteria of
the update cursor or not.

If you place an index on your 'id'-column or start the two
sessions in the reverse way (first sessions starts and
places a lock on id=2 and second session starts and
places a lock on id=1) it will work

You can find more information on this in the Informix SQL Tutorial, Chapter "Programming for a MultiUser Environment".

[cefe]

Thank you very much for your help.

After creating the index on my test table and updating statistics I had the same error message (244 and ISAM error 107).
The problem was that the table test had only 2 records and the dbms decided to make a sequential scan. (I have test it with "set explain on")
It is necessary to add {+index(test idx)} to the "select" or "update" sentence.
ej: UPDATE {+index(test idx)} test set name='proof' where id=2

In this way you say it to use the index and avoid that it makes a sequential scan.

Now it works OK.

Thanks again.

Cefe.