I noticed something funny with my table's index column after running an experiment to answer a different question.
In my experiment, I had two conditions. In one, autocommit=FALSE and in another autocommit=TRUE. I had 2 sessions connected to the server and tried various combinations of session #1 starting transactions / selecting FOR UPDATE and session #2 attempting selects and inserts, etc under those conditions. Important note: all transactions started by session #1 were rolled back, not committed.
Reviewing my results, I noticed that when the first session was in a transaction AND had selected FOR UPDATE, the insert made by session 2 (after the first session finished its transaction of course) had its index advanced by 2. In all other inserts by session 2 the index only advanced by 1. That includes inserts forced to wait because session 1 had selected FOR UDPATE (this is possible when autocommit is off btw).
Without losing another hour or two to testing, I was hoping someone could explain how the index advanced by 2. My best guess is this: I assume that the index +1 was reserved for session #1, which was rolled back instead of committed and the index + 2 was reserved for session #2 because it’s request came in while index +1 was reserved. After session #1 rolled back, nothing was put at index + 1, and session #2 inserted at the index already reserved for it, index + 2.
Is that true? If it is, I am concerned that if many sessions request to update at the same time and then don’t commit, whole patches of the index may go unused… Can I prevent this? Can I remediate it if and when it happens?
Thank you in advance.
