Geek Answers Handbook

In an Oracle SQL update statement, is a row update updated at the same time?

In an Oracle SQL update statement, assuming that the update will affect 5 rows, does the update statement execute every 5 rows simultaneously or sequentially? For example.

UPDATE table1 set column2 = 'completed' WHERE index between 1 AND 5

In the above statement, indexing from 1 to 5 will be updated sequentially, that is, 1, 2, 3, 4, then 5, or it will happen simultaneously (1-5 all at a time).

I referenced the Oracle documentation , but it seems like nothing is said about it.

+7
source share
5 answers

After executing the UPDATE statement, the effect of the statement will become visible to the rest of the transaction (and if you complete the transaction with other transactions). The order in which Oracle physically does this is an implementation detail (similarly, the order of the SELECT result is not guaranteed unless you specify ORDER BY).

In most cases, this order does not matter to the customer. In one case, when possible, you should avoid deadlocks with another transaction that updates an overlapping rowset. UPDATE will lock the updated row until the end of the transaction, therefore, if two transactions try to lock the same rows, but in a different order, a deadlock may occur.

The standard way to avoid deadlocks is to always lock in a specific order. Unfortunately, UPDATE does not have an ORDER BY clause, but you can do this:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT ... WHERE condition ORDER BY ... FOR UPDATE; UPDATE ... WHERE condition; COMMIT;

Where condition same for both operators. A serializable isolation level is necessary for WHERE to always see the same rowset in both statements.

Or, in PL / SQL, you can do something like this:

 DECLARE CURSOR CUR IS SELECT * FROM YOUR_TABLE WHERE condition ORDER BY ... FOR UPDATE; BEGIN FOR LOCKED_ROW IN CUR LOOP UPDATE YOUR_TABLE SET ... WHERE CURRENT OF CUR; END LOOP; END; /
+4
source

Potentially.

In this case, since you are just updating 5 lines, it would be incredibly unlikely that concurrent DML would be appropriate. Assuming UPDATE does not invoke parallel DML, the rows will be updated sequentially, although the order in which the rows are updated is arbitrary. INDEX 1 may be the first to be updated, the latter will be updated, or it may be updated in the middle. It depends on the request plan.

+4
source

The link you provided really covers this. Oracle always ensures consistency of reading at the instruction level - this means that not a single query in table1 will return some updated records, and some will not. It will be all or nothing and regardless of the level of isolation.

+2
source

UPDATE, DELETE and INSERT do not have a specific order. Conceptually, they apply to a set and are executed all at once. Practically speaking, do not rely on any sequence that you can observe - this is a part of the implementation that can change, and happens only because the real world interferes with theory.

+2
source

All records will be updated as one record in one transaction. Oracle does not guarantee order in the sequence of updates.

You can update any field by the value of dbms_transaction.local_transaction_id in your table to check it.

0
source

More articles:


All Articles