Re: Strange record locking PROBLEMS
In article <9610171111.aa01153@qwsserv.qws.msk.su>, Victor Kronberg
<kron@qws.msk.su> writes
>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. }
>
No index so the search which is performed for rows which satisfy
'WHERE locking_key = 1' reads row containing value 1 and row
containing value 2. Locks both rows (assuming commited read
isolation level.
>{ 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 }
>
Again assuming commited read isolation level this a)
reads stored procedure execution plan from sysprocplan
reads systables/syscolumns to check that tables/coilumns referenced
by the stored procedure have not changed. This will take a shared lock
out on sysprocplan/systables rows/syscolumns rows and possibly other
related tables E.g. sysprocbody/sysproctext to check the text of
the stored procedure has not changed.
>{ 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 ?
>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
>
See above, also I have heard of a known bug where the execution plan
for the stored procedure (sysprocplan) would always get updated thus
taking an exclusive lock on it every when it did not need to be updated.
>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.
>
--
David Williams