Re: Strange record locking PROBLEMS
Victor Kronberg wrote:
>
> Hi all,
>
> We are running INFORMIX (Online 7.10.UC1 and SE 7.12.UC1)
> on SCO UNIX (SCO_SV-3.2v5.0.0 and 3.2v4.2).
>
> Simplified test situation N 1 :
> -------------------------------
>
> CREATE DATABASE hangover WITH LOG;
> CREATE TABLE LockTable (locking_key INTEGER) LOCK MODE ROW;> { ^^^^^^^^^^^^^ }
> { ONLINE ONLY ! }
> { Table created WITHOUT INDEXES ! }
> { }
> INSERT INTO LockTable VALUES (1);
> INSERT INTO LockTable VALUES (2);>
> { Process N 1 (via dbaccess) }
> BEGIN WORK;
> UPDATE LockTable SET locking_key = 1 WHERE locking_key = 1;> { Transaction NOT COMMITTED. }
>
> { Process N 2 (via dbaccess) after 'UPDATE N 1' is finished }
> BEGIN WORK;
> UPDATE LockTable SET locking_key = 2 WHERE locking_key = 2;>
> RESULT :
> 244: Could not do a physical-order read to fetch next row.
> 107: ISAM error: record is locked.>
> BUT if UNIQUE INDEX ON locktable(locking_key) created before updates
> result is OK (Process N 2 successfully updates row) !?
>
> QUESTION N 1 : Why ?
> ^^^^^^^^^^^^^^^^^^^^
>
> Simplified test situation N 2 :
> -------------------------------
>
> CREATE DATABASE hangover WITH LOG;
> CREATE TABLE LockTable (locking_key INTEGER) LOCK MODE ROW;> { ^^^^^^^^^^^^^ }
> { ONLINE ONLY ! }
> { ^^^^^^^^^^^^^ }
> CREATE UNIQUE INDEX LockIdx ON LockTable(locking_key);
> INSERT INTO LockTable VALUES (1);
> INSERT INTO LockTable VALUES (2);
> UPDATE STATISTICS HIGH FOR TABLE LockTable;
> CREATE PROCEDURE LockSPX(lv INTEGER)
> BEGIN WORK;
> UPDATE LockTable SET locking_key = lv WHERE locking_key = lv;> END PROCEDURE;
> UPDATE STATISTICS FOR PROCEDURE LockSPX;> { }
> UPDATE STATISTICS; { Statements order IS VERY IMPORTANT ! }
> GRANT DBA TO bilgates; { Or something else 'to touch' system catalogs. }
> { }>
> { Process N 1 (via dbaccess) }
> EXECUTE PROCEDURE LockSPX(1);> { Procedure executed }
>
> { Process N 2 (via dbaccess) after 'EXECUTE PROCEDURE N 1' is finished }
> EXECUTE PROCEDURE LockSPX(2);>
> RESULT (ONLY IF I EXECUTE PROCEDURES IMMEDIATELY AFTER 'GRANT DBA ...' !!! ) :
> 211: Cannot read system catalog (sysprocplan).
> or something else (unpredictable)
> 144: ISAM error: key value locked>
> If I try to repeat procedures executing after first failure as described above
> I am getting OK-RESULTS !?
>
> If I 'UPDATE STATISTICS' after 'GRANT DBA ...' I am getting OK-RESULTS also !?
>
> QUESTION N 2 : Why-y-y-y-y-y ?
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> Any help will be greatly appreciated.
>
> Best Regards, QWERTYS Ltd.
> Victor Kronberg, 10, Elektrodnaya St.,
> +7 (095) 368-8061 App # 425,
> E-Mail : kis@qws.msk.su Moscow, Russia,
> October 17, 1996, 11:15 111524.
Hi Victor,
the first problem mentioned is easy to describe. When a row is locked
in exclusive mode, no other process can place a Shared Lock on the
same row. But exactly this is needed when another process wants to
read the table in sequential mode. Because your second update process
wants to read all the rows in your table ( there is no unique index
that prevents from reading all the rows ) it will try to read the row
where the first process still has placed it's lock. Therefore you
receive the error message "cannot to a physical order read". Physical
order means sequential scan.
After you created an index on the column "locking_key" Informix has
used this index to find the matching rows. Now, because you use Row
Locking, Informix can read the correct row. Just to understand the
error messages: if you would try an update where locking_key >= 1 with
the second process, you would receive an error message sth. like:
"Cannot do an indexed read: Key value locked."
Hope this helps,
Best regards,
Stefan
stefan@weideneder.de
PS: I guess you've used an isolation level >= "COMMITTED READ".
Your second problem sounds strange. I think it would help if you
could describe, which process performs the "GRANT DBA ..."
statement. Everytime when you call a stored procedure, Informix tries
to find out, if the procedure must be re-optimized. Perhaps this is
the problem. Can you do an "onstat -k" or
"select * from sysmaster:syslocks" before the lock problem occurs ?