Concerns: what could be the worst side effects? inconsistent tracking data stored in some cases?
Note: this should ONLY apply in Tracker mode, not for API/UI/etc.
SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.
FYI: So far we have barely any deadlocks anymore. I reckon it might not really be needed anymore so much. Seems the changes we did helped avoid the deadlocks
For performance it might be actually quite good to change the isolation mode during tracking to
read committed. This is also the default for most other RDBS and it should faster since less versions need to be maintained etc.
It be interesting to try this as it's quick to implement (we already have a way to set this for aurora using the
aurora_readonly_read_committed ini setting) but the more difficult part is actually measuring it.
Overall it be also worth considering using read committed instead of repeatable read. Meaning for every query, whether it's archiving or something else.
If someone has issues with gap locks, please comment and let us know about it.