... But can this case really happen when the sequence is
fetched in the insert statement and followed by a commit. I would think the
first person who gets 42 will be committed before the next one who got 43
unless there is something internal i am not aware of.
...
think about what happens on a multi-user computer. Things get pre-empted, blocked, scheduled, bumped on and off cpu's.
Also, think about multi-user concurrency issues, more complex transactions. For example - suppose User-A does more than just insert into T, they insert into T and then do something else and then commit. Heck, keep the example as simple as it is - but add a third person to the mix
drop table t;
drop sequence s;
create table t
( x int primary key, y int unique, z timestamp, msg varchar(20) );
create sequence s;
insert into t values ( s.nextval, 0, systimestamp, 'sess 1' );
set echo off
prompt SESSION 2:
prompt insert into t values ( s.nextval, 0, systimestamp, 'sess 2' );;
prompt commit;;
prompt SESSION 3:
prompt insert into t values ( s.nextval, 1, systimestamp, 'sess 3' );;
prompt commit;;
prompt THEN COME BACK AND HIT ENTER
pause
rollback;
exec dbms_lock.sleep(1);
select * from t;
session 1 blocks session 2, session 3 comes in after session 2 and creates their row and commits, when 1 rolls back, 2 commits.
Now, as I said - whether this 'matters' or not to you is something only you can answer - but there are millions of ways for a row to be created - a transaction to get blocked, pre-empted, paused - and another transaction to come in and do stuff.
Do not think linear here, things happen more or less simultaneously