hello guys,
I have this one prod DB, that have been experiencing deadlocks for quite a while.
I already made a proposition to use an explicit locking of tables like:
LOCK TABLE tab_name IN ROW SHARE MODE;
I know this can be very helpful, but it seems to me that developers don't want to touch the
codes(politics plays).
Is there INIT parameters that I can set, to overcome this locking problem without touching the codes?
I always have to monitor locks before an ora-02049 error will occur.
I read params like enqueue_resources, dml_locks, row_locking, initrans.
but I'm not sure if this can be much of a help or otherwise.
You dont have to do that in Oracle..or in many other databases of todays generation for that matter.The deadlock will produce a trace file just dig into that tarce file for analysis.
abhaysk,
yes this is 8.1.7 version. And it has only few parent-child rel. It has index on FKs too. The problem with regards to the application is
that the table is so huge that it takes time before a user can complete one record. And before it insert/update data it acquire a row exclusive mode. So the select.. statement doesn't even have a FOR UPDATE included when updating at least.
hrisky,
actually, I did trace this from trace file. And it shows that it is an application error. I am aware that ORACLE RDBMS has a LOCK management itself, but it can be greatly(poorly) influenced by poor programming. And since the DEVELOPER TEAM doesn't want to take word from their DBA, I can only try to find another way to overcome such scenario. And that is why I did come up with this INQUIRY.
Originally posted by SANJAY_G Is is always advisable to let Oracle hadle the locks itself.
Absolutely.
Deadlocks = bad system design and poor coders who developed it and allowed it to progress. A deadlock isn't an 'error' as such. its Oracle - quite sensibly - telling you that something is wrong with the system design.
Get the developers to change the code and the design otherwise you're setting yourself up for big trouble. Let Oracle handle the locks - thats what you pay big bucks to Larry Ellison for.
I think i read advice in an Oracle doc that developers should follow a pattern of always locking master reccords before child, or vice versa (it doesn't matter as long as it's consistent everywhere), to reduce the probability of deadlocking. Seems like good advice.
thanks for the input guys, but the fact remains that this system still experiencing ora-02049 and ora-00060 at least 15 times a day.
There must be a problem somewhere else. I did suggest to use a LOCK using ROW SHARE mode, because most of those sessions that involve in locks is either have a status of EXCLUSIVE or ROW EXCLUSIVE locks. I'm not sure yet of what the actual codes of those program. But for all those sessions that has a ROW SHARE lock status doesn't have a deadlock problem in other database.
Anyway, thanks all. I have to further investigate this case. My only nemesis is that, we are in separate company maintaining their infrastructure(network and DB,email,etc.), so any proposition that involves changes to their system is an issue.
You will observe that a bunch of table have a row exclusive lock in a single session. And another session is currently waiting for the lock to be release.
Usually, I can only observe few tables being locked by a single session, and more often it is a ROW SHARE lock at row level.
And one thing, I can remember the default dml locking that oracle implicitly assign when a user perform a DML is a SHARE LOCK at the table level, and a ROW EXCLUSIVE LOCK in row level. But why in this case if you can observe at the buttom in the same session the acquired lock for table is EXCLUSIVE.
That is why when a session issues a command SELECT .... FOR UPDATE statement, the row level locking changes from ROW EXCLUSIVE to ROW SHARE.
Any interpretation, or just idea of what could cause the problem?
Originally posted by slimdave I think i read advice in an Oracle doc that developers should follow a pattern of always locking master reccords before child, or vice versa (it doesn't matter as long as it's consistent everywhere), to reduce the probability of deadlocking. Seems like good advice.
While I'll agree with HRISKY, SANJAY_G, and JMAC about letting the ORACLE handle the LOCK management, I can only bear with this, only if the design of the application is good or better. Otherwise if it is bad, like what I'm in right now and the developers doesn't have any intension of re-designing the structure or re-program(major changes).
I must find a way if there is, to alleviate the situation.
Slimdave,
Thanks, I think you're leading me to what I believe I need.
abhaysk,
I did try to dig in, and I was wrong, there was a lot of dependencies with regards to tables involved.
SANJAY_G,
Is is always advisable to let Oracle hadle the locks itself.
Is it distributed environment? If yes, try this parameter _distributed_lock_timeout.
even if I can set _distributed_lock_timeout, it will only prolong the process of the waiters because the lockers is still yet to commit/rollback.
In my part it leaves me no choice but to use the LOCK explicitly. Let the developer insert the LOCK command before DML.
I think that when a user save/rollback its process, the LOCK that was set to a table will be released automatically, therefore no need to worry about LOCK mismanagement.
I hope that some of the DBAs here have the same situation with mine, and can share ideas of how they overcome such situation.
Reply With Quote
