CODEWITHSUNDEEP
databaseoracle-databaseconcurrencysql-update
Ted
Ted

Reputation: 733

In Oracle SQL update statement, does row update occur concurrently?

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.

Upvotes: 7

Views: 2600

Answers (5)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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;

Where 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;
/

Upvotes: 4

ceth
ceth

Reputation: 45295

All records will be updated as one record in one transaction. Oracle doesn't guarantee any order in the update sequence.

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

Upvotes: 0

jmoreno
jmoreno

Reputation: 13561

UPDATE, DELETE and INSERT do not have a defined order. Conceptually they apply to a set and are done all at once. Practically speaking, don't rely upom any sequence you may observe -- it's an implementation detail which can change, and only happens because the real world gets in the way of theory.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231661

Potentially either.

In this case, since you're just updating 5 rows, it would be exceedingly unlikely that parallel DML would be appropriate. Assuming that the UPDATE does not invoke parallel DML, the rows will be updated sequentially though the order in which rows are updated is arbitrary. INDEX 1 might be the first to be updated, the last to be updated, or it could be updated in the middle. It depends on the query plan.

Upvotes: 4

gordy
gordy

Reputation: 9786

The link you provided does actually cover this. Oracle always enforces statement-level read consistency - this means that no query on table1 will return some updated records and some not. It will be all or nothing and regardless of the isolation level.

Upvotes: 2

Related Questions