If I have table:
CREATE TABLE table_name(
id SERIAL
);
And I have following id's inserted: ..., 68, 69.
Then I have 2 and I have to competing transactions (T1, T2) running in parallel. I understand that it could happened that transaction finishing first gets higher number because id is assigned and written in WAL before transaction commits.
T1 (Take Number = 70), T2 (Take number = 71), T2 (Commit), T1(Commit)
What is situation when having AUTOCOMMIT (When inserting row outside of transaction). And I have to very close insert is id guaranteed that first inserted row will get lower number?
Use case is following:
After inserting row, I would execute SELECT id FROM table_name ORDER BY id. Could it happen that i execute this command twice once after another and then get following result:
Select 1 result: 68,69,71
Select 2 result: 68,69,70,71
Even if you don't use explicit transactions, it is not guaranteed that the statement that gets the lower sequence value will also commit first.