Strange record locking PROBLEMS
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.