Re: Informix Locking Problem

Albert E. Whale writes: |> In article <36tmu4$nqa@hk.super.net>, gmwkc@hk.super.net (Mr Cheung Chi |> Kong Dannis) says: |> >I got the problem on informix locking |> >I am using Informix online 5.01 version. |> > |> >When I modify the record, it will lock current modified record and |> >previous one also. It look abnormal. |> |> Yes, the problem is identified as the Twin Locking problem for OnLine |> v.5.x. This is resolved in OnLine v.6.0. Well, not really. The behavior Mr. Dannis described is the *solution* to the "twin locking problem". It just so happens that the fix causes other problems, thus the 6.0 resolution. In a nutshell, the "twin" problem is (was) as follows: in a transaction, delete a record. In the meantime, another transaction comes along and inserts a record with the same key as the record deleted by the first transaction. Second transaction commits. First transaction attempts a rollback, but in re-inserting the key, finds a key with that value already in place. Thus it cannot complete the rollback (this is not a problem when duplicate keys are allowed). Pre-6.0 versions of OnLine "fixed" this by locking and testing adjacent keys in the btree. When a new record is inserted, the inserted key is locked exclusively and the adjacent key is tested for a lock. When a record is deleted, the current key value is tested for a lock and the adjacent is locked. While this approach solves the "twin" problem, it has th unfortunate side effect of potentially blocking new rows from being inserted whose keys are not involved in the transactions in question, as the solution effectively blocks entire ranges of key values. 6.0 solved this by adding a delete flag to the key structure along with a change in what gets locked. This should be well-covered in the doc ref'ed below. |> If you want a detailed explanation of the problem, I have forwarded the |> information and Technical Information from Informix to Kerry Sainsbury |> (he really is insane! - just kidding) for the next version of the |> Informix FAQ. This is an excellent document and a MUST READ!! Dave Kosenko Disclaimer: All opinions expressed in this message are well-reasoned and insightful; needless to say, they are not those of Informix Software, its partners or lackeys. Anyone who says otherwise is itching for a fight.