When two transactions become deadlocked, they will be able to determine that this condition exists. The first transaction to detect a deadlock condition is terminated. By terminating one of the transactions, the locks are released and the remaining transaction can now complete.
If you are receiving deadlocks, then your only course to resolve these are through better programming techniques. There are two common actions to perform.
- Make sure that your application performs DML operations on tables in the same order. Having one section of your application code perform DML on three tables in one order while another section of your application code performing DML on these same tables in a different order can introduce a deadlock condition if two users simultaneously execute the different sections of code.
- Make sure that you are committing as often as possible. When you commit (or roll back) your transaction, you release the locks that transaction held.
Dig Deeper on Oracle database performance problems and tuning
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading