How does PL/SQL handle table deadlocks?

[weberm]

I have to schedule several PL/SQL scripts which perform inserts and updates against some common tables. It's been my experience (I have a COBOL mainframe background) that when a job tries to perform these operations against a database being updated that it will abort. Is this the case for ORACLE, or does a job wait until another is finished performing its updates if a locked table is encountered?
The answer to this question will help determine if I will have to force jobs to run sequentially or can allow them to start at the same time. I am asking because my manuals do not give a definate answer (or I am not looking in the right place). Thank you for your assistance.

 

[carp]

Oracle has a very extensive locking scheme that works at many levels.
To begin with, Oracle handles DML with row-level locks, so if Process1 is updating some rows within a table, it does not necessarily hinder Process2 from inserting/updating/deleting rows.
However, if Process2 wants to update a row that Process1 has a lock on, Process2 will be enqueued and wait until Process1 issues a commit on its transaction (which will release the lock on the row). No exceptions will be generated.
Note that in Oracle, Process2 waiting for Process1 to release a lock is NOT necessarily a deadlock. A deadlock occurs when Process1 wants to update/delete a row that Process2 has locked and Process2 wants to update/delete a row that Process1 has locked. In this case, neither process can proceed. Fortunately, Oracle has a mechanism that usually works (I've heard of cases where it didn't; never experienced one). If Oracle detects a deadlock, one transaction will be rolled back (which releases the locks) and the other will continue on its merry way.

Elbert, CO
0925 MST

 

[avjoshi]

Typically, any RDBMS engine runs the transactions in serializable fashion. From a programmer’s perspective, it allows them to write programs with assumed serialization.

Database data integrity is completely transparent to the transactions. Most RDBMSs come with all bells and whistles to maintain transaction integrity; such as transaction redo or undo entries and data rollbacks or before images.

If any transaction is deemed to not conform to these rules (as in two transactions waiting on each others results, resulting in dead lock) then database will take appropriate action (terminate the later transaction render error to that session). All this activity is internal to the database service.

As a good practice it may help you trap those error messages for further analysis but beyond that, programs are written with assumed serialization.

I hope this helps.

Anand.