• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3399
  • Last Modified:

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.
1 Solution
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.
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?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now