Results 1 to 14 of 14
  1. #1
    Join Date
    May 2003
    Posts
    8

    Question Unanswered: Problems with locks

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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;
    rws

  3. #3
    Join Date
    May 2003
    Posts
    8
    I have taken a look at the systables table and locklevel=R. ?

    Thanks.

  4. #4
    Join Date
    May 2003
    Posts
    8
    Does anybody knows any other reason that explain why this lock occurs if lock mode is set to row?

    Thanks

  5. #5
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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.
    rws

  6. #6
    Join Date
    May 2003
    Posts
    8
    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

  7. #7
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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.
    rws

  8. #8
    Join Date
    May 2003
    Posts
    8
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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?
    rws

  10. #10
    Join Date
    May 2003
    Posts
    8
    The ISAM error is 107 record is locked..

  11. #11
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    I'm sorry, I don't see the light on this one. Perhaps Eric can help us out here?
    rws

  12. #12
    Join Date
    May 2003
    Posts
    8
    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

  13. #13
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    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".

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  14. #14
    Join Date
    May 2003
    Posts
    8

    Thumbs up

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •