Deadlock time out

Is there a way to set ORACLE time out on a particular table?

I started 5 instances of my program to do some processing, and the commit/rollback will only be issued after one transaction has been completed. In this case, if the transaction is big, it may take up to a few hours to process.

In the following example, I have used the following abbreviation:

UL - In the process of updating by locking the whole table
U  - In the process of updating without locking the whole table
W  - Waiting for update
*  - Finish updating
P1 - Process 1

TABLE      P1   P2   P3   P4   P5
TABLE1      *    *        U    W    W

In the above scenario, P1 and P2 have finished updating TABLE1, however the commit/rollback will only be issued after TABLE2 has been completed. If there are a lot of records to be updated in TABLE2 table, the rest of the processes will have to wait until P1 has completed if they were to update the same row (Update lock the whole row until a commit/rollback, am I right?).

In this case, is there a way to set the waiting time of deadlock in ORACLE, so that, P2 to P5 will not return a deadlock message when they can't perform an update.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Oracle does not lock the whole table for UPDATES, it only locks the rows it needs, unless you specifically (manually lock the table). The likelyhood of two or more processes needing the same rows at the same time FOR UPDATE is very low. But deadlock do happen.

No there is no way to set deadlock timeout. You will have to take care of this by checking for deadlocks with in you code and retry. Normally Deadlock are not a big issue in Oracle unless the application is written badly.

Note if all the processes acces the tables in the same sequence the likelihood of deadlock is usaully not an issue.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ymlewAuthor Commented:
The processes access the tables in the same sequence. This is a billing program which processes the customer's spending. Therefore, each process processing time will depend mainly on the customer records. If a customer has a lot of spending done on that month, then the processing time is longer.

In my case, the likelyhood of two or more processes needing the same rows at the same time FOR UPDATE is very high. Therefore, I would like to be able to set a lock timeout, so that, if a process is unable to update, it will wait for a certain time, let say, 20 minutes and retry again.

Is there a way to set the 20 minutes?
There is no way to do this in Oracle. This is on of the differences between Oracle and some other databases like Informix.

Are you currently having problems with deadlocks?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ymlewAuthor Commented:
Yes. I have already tried to increase the INITRAN value, it works for sometime, but the problem happen again.

The program is written in C
ymlewAuthor Commented:
Will the parameter DISTRIBUTED_LOCK_TIMEOUT in init.ora help?
If you increase INITRAN value you should also increase PCTFREE to make room for the larger INITRAN value.

I think DISTRIBUTED_LOCK_TIMEOUT has any thing to do with DEADLOCK time out. If I remmember correctly, it is a part of Distributed db configuration. I will check tomorrow and let you know.

But, I know that Deadlock timeout can't be set.

Please forgive me if I'm misinterpreting how your program works. But what many designers will do when they come across many processes that  wish to update the same resource and the update can occur in any order without application constraints (like a balance), then the application will put the request in a queue and have a separate process that does one update after another and then informs the user application of the update via a second queue.

Of course, it only helps in a very small set of instances...

DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time in seconds for distributed transactions to wait for locked resources.   This is only for distributed database environment. Nothing to do with Oracle's Deadlocking.
I hope you all realize that the problem being described has nothing to do with "deadlocks".  This is just a lock, not a deadlock.

Tom is right.  This situation is one where a process is waiting for another to release a lock before it can update a row.

A deadlock is a circular sort of thing where process 1 locks a row, process 2 locks another row that process 1 needs in order  to continue, but process 2 won't continue because it needs the row process 1 has locked as well.  So they wait endlessly.  But the Oracle server detects deadlocks like these and terminates one of the sessions.

Yes, as tbcox says this example is not a valid deadlock senario.  But, you are right to think that accessing the same rows by many processes will increase the likelyhod of a deadlock. The best way to minimize the this is to make sure that the different processes that update the same set of table do so in the same order as much as possible with in a transaction.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.