Re: Does Informix do Versioning Read-Only Transactions?

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" ... ;-) 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? June ---- June Tong Informix Asia/Pacific ---- ---- On-Loan Engineer Singapore ---- ---- junet@informix.com (65) 298-1716 ----