20.07.2018 21:27, livius wrote:
Not exactly so. When index key is not changed by UPDATE statement, engine
doesn't
add it into the index.
Ok, so it has not changed.
Yes
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
Very informative, thank you very much.
Above describe the problem, but how it was fixed after FB2 that we have now
this issue?
*Before* fb2 was released, this was changed to insert entry into index
despite of its presence. Scenario above actually run as:
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), (k1, r1), (k2, r1)
note, tx3 *adds* index entry (k1, r1)
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: (k1, r1), (k2, r1)
Now index contains all necessary entries.
But sometime it could contain non-removable, excessive entries:
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: update r1 set key = k1
table contains tree records versions: r1(k1)-tx2, r1(k2)-tx2, r1(k1)-tx1
index contains tree entries: (k1, r1), (k1, r1), (k2, r1)
Then engine immediately removes first record version, created by tx2
remove from disk r1(k2)-tx1
build lists: staying r1(k1)-tx2, r1(k1)-tx1, going r1(k2)-tx2
keys: staying (k1, k1), going (k2)
remove index key (k2, r1)
On disk
table: r1(k1)-tx2, r1(k1)-tx1
index: (k1, r1), (k1, r1)
tx2: commit
garbage collector:
remove from disk: r1(k1)-tx1
lists: staying (r1-tx2), going (r1-tx1)
keys: staying (k1), going (k1)
no index key is removed
On disk
table: r1-tx2
index: (k1, r1), (k1, r1)
Now we have excess index entry on disk.
Hope it is clear,
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
