Re: Indexing problem with On-Line

> > Hi there. I have an amazing problem with Informix On-Line version 4.00.UE2. > > When two programs try to insert a row in the same table (within a > transaction ) and the one that has the greater value in a column with a > unique index, inserts his row first, then the other one aborts. If the > opposite arises everithing works fine. > > etc. This sounds like Informix's solution to the 'Twin Problem' (A pet hate of mine for quite some time). Suppose that 2 transactions were accessing a table with a unique index at the same time. Txn 1 deletes a row, but does not commit or rollback yet. Txn 1 can't have a lock on the item it has deleted (because it doen't exist). Txn 2 then adds a row with the same key value as the one Txn 1 deleted, and does a commit work. Txn 1 then does a rollback work - resulting in a duplicate value in a unique index!!! This is the 'Twin problem'. Informix have implemented a (really bad) workaround to this. When txn1 deletes it's row, it locks the row with the next-highest key value. The code which inserts a row, checks the next-highest key value, and if it's locked, the insert fails. This avoids the twin problem. However, this can cause your problem. Your first txn adds a row to the table, (and has an exclusive lock on that row). Your second txn then tries to insert a row with a smaller key value. The insert code checks the next-highest key value, and discovers that it is locked by txn 1. It assumes that the reason for the lock is that txn1 has deleted the row that txn2 is trying to insert, so txn2 gets a locking error. Suppose that we have a table with a smallint column, with a unique index. This table has two rows, one with a key value of 1,000, and one with a key value of 10,000. Someone comes along and updates the 10,000 row, thus locking it. Until they release their lock, no-one can insert rows 1,001 to 9,999 !!! This problem is made worse by the fact that the same strategy is used when dealing with duplicate indexes, even though there is no concept of the twin problem with duplicate indexes. It can get really bad on a table with a large number of indexes. I have tried on several occasions to get Informix to realise the importance of this shortcoming, and consider an alternative strategy, but they just aren't interested. The twin problem is discussed on page 22 of the Summer 1988 Tech Notes, but it doesn't mention the additional problems caused by Informix's solution. (The Tech notes are actually for Informix-Turbo, but the same strategy is used for OnLine). -- Regards, Andrew. ------------------------------------------------------------------------------- Andrew Roberts UUCP: ...uknet!valstr!ar Valstar Systems Limited Internet: ar%valstr@uknet.ac.uk Burghmuir Drive, Inverurie Voice: +44 467 22720 Aberdeenshire, Scotland Fax: +44 467 24120 AB51 9GY -------------------------------------------------------------------------------