CODEWITHSUNDEEP
oracle-databasedeadlockisolation-leveldatabase-deadlocks
dbza
dbza

Reputation: 326

Can this update cause a deadlock in oracle 10g

I came across this update statement and was wondering how the internal working is. It updates a column which also is used in the where clause of the update.

Should this be ideally done in two steps, or does oracle takes care of it automatically?

UPDATE TBL1 SET DATE1=DATE2 WHERE DATE2> DATE1

Upvotes: 0

Views: 71

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Oracle takes care of it automatically. Effectively when it runs the update, Oracle performs the following steps:

  1. Queries the table - i.e. evaluate the WHERE clause predicate for each row in the table

  2. For each row that is returned by step 1, update it as per the SET clause. The values of each column are those that were fetched.

For this reason, it is perfectly possible to run an update like this which swaps the values of columns:

UPDATE TBL1 SET DATE1=DATE2, DATE2=DATE1 WHERE DATE2 > DATE1;

The update might be blocked if another session tries to update or delete one of the same rows. Deadlocks are possible but Oracle automatically resolves these by rolling back one of the sessions and raising an exception.

Upvotes: 2

Related Questions