20.07.2018 14:45, liviuslivius wrote:
Hi, i ask here to not spam into the tracker. I know that indexes in Firebird are not transactional objects. My understanding was that when we update record then old record vesion go to new place and new record version replace place where old record version was.
More-or-less correct
This prevent for updating index entry when nothing in the indexed fields was changed.
>
But now i read that: >> So, many years ago (when FB 2.0 was developed, iirc), algorithm above was changed a bit - since then Firebird >> engine adds index key despite of its presence with the same record number. >> This could lead to the non-removable keys but it is much less evil than missed entries. do i understand correctly that now it store new entry always?
Not exactly so. When index key is not changed by UPDATE statement, engine doesn't add it into the index.
And it store with link to the same record not to old record version?
>
>> This doesn't work in concurrent environment, unfortunately, and could produce index with missed entries (which is >> very serious problem). I don't want to go too deep into details here.
>
Maybe you can bring light here if not on the tracker?
tx1: insert record (r1) with index key (k1) table contains one record version: r1-tx1 index contains one entry: (k2, r1) tx1: commit tx2: update r1 set key = k2 table contains two records versions: r1-tx2, r1-tx1 index contains two entries: (k1, r1), (k2, r1) tx2: commit garbage collector: remove from disk r1-tx1, build lists: staying (r1-tx2), going (r1-tx1) going to cleanup indices context switch... table: r1-2 index: (k1, r1), (k2, r1) tx3: update r1 set key = k1 table: r1-tx3, r1-tx2 index: (k1, r1), (k2, r1) note, tx3 doesn't add index entry (k1, r1) as it is already present tx3: commit garbage collector: ...continue to cleanup indices lists: staying (r1-tx2), going (r1-tx1) keys: staying (k2), going (k1) remove index key (k1, r1) On disk table: r1-tx3, r1-tx2 index: (k2, r1) r1-tx3 contains key = k1, but index have no such entry
>> So far i have no good solution for this problem.
>
if described in details, more heads can got some concept how to solve this. regards,
This is old and well known in this list (at least) problem Regards, Vlad ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
