ymlew
asked on
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
TABLE2 UL 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.
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
TABLE2 UL 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Are you currently having problems with deadlocks?
ASKER
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
The program is written in C
ASKER
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.
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.
ymlew,
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...
Ryan
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...
Ryan
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
-Tom
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.
-Brad
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.
-Brad
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.
ASKER
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?