Under what conditions (if any) can two transactions share an SCN?
To put another way, is there a guarantee that if several DML statements share an SCN they are guaranteed to be part of the same transaction?
oraclerac
Under what conditions (if any) can two transactions share an SCN?
To put another way, is there a guarantee that if several DML statements share an SCN they are guaranteed to be part of the same transaction?
Best Answer
Two transactions won't share an SCN barring a bug in Oracle.
Exactly how Oracle ensures this varies by version, platform, and configuration settings. For example, back in the Oracle 9.2 days, there was by default a non-zero
max_commit_propagation_delay
which meant that a row that was inserted on one node and committed might not be immediately visible to a query on the other node. In that configuration, Oracle could use a Lamport SCN generation algorithm where SCN's were generated in parallel without a need for internode communication. In 10.1 and later, a broadcast on commit strategy (link is to a PPT presentation from Julian Dyke which is excellent but may be bigger than you'd expect) is used instead andmax_commit_propagation_delay
defaults to 0 (no idea what happens if you use 10.1 and set a non-defaultmax_commit_propagation_delay
-- that might well flip back to Lamport).