Index corruption. Validation put "missing entries" message into firebird.log ----------------------------------------------------------------------------
Key: CORE-3515 URL: http://tracker.firebirdsql.org/browse/CORE-3515 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 2.1.4, 2.5.0, 2.0.6, 1.5.6, 2.1.3, 2.1.2, 2.0.5, 2.1.1, 2.0.4, 2.1.0 Reporter: Vlad Khorsun Imagine a table T with two indices PK (unique, it is important) and IDX2 (in my case it is very bad index with 2 different values only but this is not so important). So, we have : CREATE TABLE T ( ID INT NOT NULL, VAL INT ); ALTER TABLE T ADD CONSTRAINT PK PRIMARY KEY ON (ID); CREATE INDEX IDX2 ON T (VAL); INSERT INTO T VALUES (1, 0); COMMIT; The sequence of actions is following: 1. tx1: insert into T values (1, 0) 2. tx1: VIO_store returns OK, new record have recno = 1 3. tx1: IDX_store 4. tx1: insert_key index == PK, key == 1, recno == 1 returns duplicate error i.e. we have unique violation in index PK note, there was no attempt to insert key into index IDX2 5. tx1: VIO_backout 6. tx1: delete_record ... OK 7. tx2: insert into T values (2, 0) 8. tx2: VIO_store ... returns OK, new record have recno = 1, yes same recno !!! 9. tx2: IDX_store 10. tx2: insert_key index == PK, key == 2, recno == 1 returns OK 11. tx2: insert_key ... returns OK index == IDX2, key == 0, recno == 1 returns OK 12. tx2: commit 13. tx1: IDX_garbage_collect 14. tx1: BTR_remove index == PK, key == 1, recno == 1 15. tx1: BTR_remove index == IDX2, key == 0, recno == 1 here we removed not ours index entry !!! So, we have at disk (ignoring pre-existing record with recno = 0): after (4) : recno = 1, PK : entry {key = 1, rec = 1}, IDX2 : no entries after (6) : no records, PK : entry {1, 1}, IDX2 : no entries after (12) : recno = 1, PK : entries {1, 1} and {2, 1}, IDX2 : entry {0, 1} after (14) : recno = 1, PK : entry {2, 1}, IDX2 : entry {0, 1} after (15) : recno = 1, PK : entry {1, 1}, IDX2 : no entries and finally we have missed entry at index IDX2 for record 1. The issue happens when all conditions are met at the same time : a) first insert violates indexed constraint (unique or foreign key) b) this indexed constraint is not a physically last index, so some indices have no entries for the failed record c) VIO_backout deleted record and at the same time new record is inserted at the same slot on data page and have assigned the same record number d) at least one index from the second group in (b) have the same key value in the new record as in failed record e) second insert completes before backout started to remove index entries of failed record I think the problem could happen no only with two inserts, but with two updates too (first update failed, backout started and second update came at inappropriate moment). Also it seems possible to have "blob not found" errors by the same reason. To fix the issue i offer to delay physical record removal (in case of backout) until all going index entries will be removed. This will prevent concurrent insert from creating new record with the same record number until backout maintains indices. To do it, i offer to split backout on the two phases. At the first phase don't remove record from disk but mark it with current transaction number and flag rpb_gc_active. Then cleanup indices and after remove backed out record version completely. It seems safe as : a) marking record with current transaction number prevents concurrent backouts b) marking record with rpb_gc_active flag allow readers to skip this record version and read previous one (which will be primary record version after backout completes) c) if our process will die during backout, next process will see our transaction as dead and will start all over again The patch passed my tests and also runs in production over month by the Pavel Zotov who reported the bug and made great help to investigate it. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira ------------------------------------------------------------------------------ Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Discover what all the cheering's about. Get your free trial download today. http://p.sf.net/sfu/quest-dev2dev2 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel