Thursday, May 23, 2013
An attempt to explain the rationale behind Vlads fix for CORE-4100.
When a sweep has successfully finished its work it advances the Oldest Interesting Transaction (OIT) up to the value of the Oldest Snapshot Transaction (OST) that was recorded when the sweep started. The OIT transaction is the first transaction in a state other than committed in the database’s Transaction Inventory Pages (TIP), while the OST is the oldest transaction that was started in Snapshot mode.
If while the sweep is running, there are more new transactions started than is the sweep interval (by default, when the OIT is 20,001 transactions less then the Oldest Snapshot Transaction), it is possible that the new OIT value could again be more than the sweep interval less the OST value, thus ensuring that a new sweep could start immediately.
After a sweep has completed the first new transaction will pick up the updated OIT value from the saved OST on the database header page that was recorded when the sweep began, it will also read the actual OST from the header page, as well as the Oldest Active Transaction (OAT), the first transaction marked as active in the TIP pages. If the sweep condition is then met, a sweep begins.
Ideally what should happen is that the transaction should pick up the recalculated OIT from (in transaction) rather than the OIT from the header page in order to determine whether a sweep should start or not.
1. Transaction 1000 was rolled back, therefore the next transaction when it calculates the OIT will use 1000 and is now considered a stuck or “interesting” value.
2. By the time transaction 21001 occurs we have the following numbers:
3. An automatic sweep is started, and it makes a note that the OST is 21000
4. While the sweep is running 30000 new transactions get started and committed.
5. When the sweep has finished doing its garbage collection and is about to advance the OIT, the numbers on the database header page are in effect
6. The sweep then advances the OIT up to previously noted OST (21000)
7. A new transaction is started and it then obtains the following numbers from the database header page:
tra_oldest (OIT) 21000
tra_oldest_active (OAT) 51002
tra_number (OST) 51002
However within the transaction it has also recalculated the new oldest interesting transaction number as 51001 which will be written to the database header page at the end of the transaction.
8. However based on the OIT read from the database header page the following condition below is true
tra_oldest_active (OAT) - tra_oldest (OIT) > sweep_interval
51002 – 21000 > 20000 therefore a sweep will be started again.
9. However when sweep starts the database header page will have been updated to contain an OIT of 51001, so instead of doing the above, we really should check the local OIT that is going to be written out to the header page rather than the header page itself, before deciding on whether to do a sweep or not.