How does Oracle 12c handle the execution order of identical DML statements across distinct transactions?

Question

I'm administering an Oracle 12c database and need to understand how the database handles the execution order of DML (Data Manipulation Language) statements when executed across different transactions initiated by distinct connections.

Here’s the specific scenario I'm dealing with:

Transaction 1: Connection A initiates a transaction and executes a series of identical UPDATE statements:

update my_table set col1 = val2 where primary_key = 1;
update my_table set col1 = val2 where primary_key = 2;
...
update my_table set col1 = val2 where primary_key = 50;

Transaction 2: Connection B starts a transaction almost simultaneously, executing the same series of UPDATE statements in the same order.

Questions:

1. Execution Order: Will Oracle 12c ensure that these UPDATE statements are executed in the same order across both transactions?

2. Influencing Factors: What factors could influence the execution order of these DML statements across different transactions and connections?

I've reviewed the Oracle 12c documentation, particularly on transaction management and DML execution, but couldn’t find clear guidance on whether Oracle guarantees consistent execution order in such scenarios. I would appreciate any insights, particularly on how Oracle handles transaction scheduling or any internal optimizations that might affect the execution order

Answer 1

Will Oracle 12c ensure that these UPDATE statements are executed in the same order across both transactions?

Not sure what you mean by "across both transactions". Statements will be executed in the same order within each transaction, but statements executed in concurrent sessions generally will be interleaved in an undetermined fashion. That would be a moot point in your case, because the session that starts slightly later will block as soon as it hits the first row updated by the other session, until that other session commits.