In Oracle SQL update statement, assuming the update would affect 5 rows, does the update statement updates all 5 rows concurrently or sequentially? E.g.
UPDATE table1 set column2 = 'completed' WHERE index between 1 AND 5
In the above statement, would index 1 to 5 be updated in sequence, i.e. 1, 2, 3, 4 then 5, or would it occur concurrently (1-5 all at once).
I had referred to Oracle documentation but it seems that nothing is mentioned on this.
After the UPDATE statement has executed, the effects of the statement will become visible to the rest of the transaction (and if you commit, to other transactions). In what order will Oracle physically do it, is an implementation detail (similarly how the order of SELECT result is not guaranteed unless you specify ORDER BY).
In most cases, this order does not matter to the client. One case where it might is to avoid deadlocks with another transaction that is updating the overlapping set of rows. UPDATE will lock the row being updated until the end of the transaction, so if two transactions try to lock the same rows, but in different order, a deadlock may ensue.
The standard way of avoiding deadlocks is to always lock in a well-defined order. Unfortunately, UPDATE does not have the ORDER BY clause, but you can do this:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT ... WHERE condition ORDER BY ... FOR UPDATE; UPDATE ... WHERE condition; COMMIT;
condition is same for both statements. The serializable isolation level is necessary for
WHERE to always see the same set of rows in both statements.
Or, in PL/SQL you could 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; /