Re: Strange record locking PROBLEMS
Victor Kronberg <kron@qws.msk.su> 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 ?
:^^^^^^^^^^^^^^^^^^^^
Because the second update needs to scan the table sequentially to find
any row that contains the value 2. It has to read the one that is
locked by process N 1 and can't of course. This is standard behaviour.
Someone else will have to look at the next one. It's too much to go
through for me right now.
: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.
Nils.Myklebust@idg.no
NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org