Key-Value locking with RR (was Re: Does Informix do Versioning ...)

June Tong (junet@informix.com) wrote: : Graeme Sargent <graeme@pyramid.com> wrote: : {slightly editted} : : User A User B : : 1 SET TRANSACTION READ WRITE; : : 2 SELECT * FROM DUAL FOR UPDATE; : : 3 INSERT INTO DUAL VALUES ('Y'); : : 4 SELECT COUNT(*) FROM DUAL; : : 5 COMMIT; : : 6 SELECT COUNT(*) FROM DUAL; : : : : : :My suspicions that OnLine >= 6.00 would allow inserts into a Repeatable : :Read set proved unfounded. User B would be locked out by Informix until : :User A commits, and Repeatable Read would be maintained. I understand : :how this was achieved in <= 5.0x, but not in >= 6.00. : : : :Given the addition of a WHERE clause to the above example to inhibit : :table locking and a LOCK MODE ROW table, then User B appears to place : :locks on uncommitted data in a different (new) data page, rather than : :the next available slot, yet Dirty Read transactions cannot see the : :data, and oncheck reports the page as an empty data page (and includes : :it in the count of allocated pages, but does not yet include it in the : :count of data pages). User B is still inhibited by a key lock placed by : :User A, although I cannot work out which one. : : : :Are you monitoring this thread, June? : Barely (assuming you're talking to me -- I haven't seen any other June's : lurking about). When I see the word "Oraxxx" ... ;-) Nor had I, I assumed you would know I meant you. :-) : Assuming that when you say you are adding a WHERE clause, you are adding : WHERE col = "Y" : to User A's SELECT, so that User B cannot add a row with the value "Y", User A : should get an SR (Shared/Repeatable-Read) lock on all the key values where : col's value is "Y", and also on col="Z", or the first key value that does not : match. Even with Key Value Locking, I'm pretty sure that this "adjacent key" : lock is required to ensure Repeatable Read. When User B tries to insert his : row with col="Y", he must still test the adjacent key for the presence of a : Repeatable Read lock (SR or XR). This is the only adjacent key locking left : in 6.0. : I'm surprised you even see that User B has locks on the free page - is that : because you SET LOCK MODE TO WAIT? Yes. Having re-read your technote, I am not sure why this wasn't clear to me before, as it clearly states that an INSERT does a TRR on the adjacent row. I guess I was thrown a bit by the data getting inserted into a new page, when I didn't think it should be. I've just rerun my tests and this didn't happen again. I think this can be explained as follows: I forgot to change the table to row level locking before I started. I think I must have attempted an insert before I noticed the omission. I think that must have updated an "insert next row" pointer in the tablespace header in shmem. All subsequent inserts got pointed at the new page instead of the partially full page. Tablespace was reopened causing the shmem pointer to revert to the partially full page. Does that make sense? : June : ---- June Tong Informix Asia/Pacific ---- : ---- On-Loan Engineer Singapore ---- : ---- junet@informix.com (65) 298-1716 ---- -- graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.