Hot answers tagged locked-objects
Would it be possible to find out which line of a stored procedure is causing these row lock contentions? Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure. SELECT sid, sql_text FROM v$session s LEFT JOIN v$sql q ON q.sql_id=s.sql_id WHERE state = 'WAITING' AND wait_class != 'Idle' ...
I will provide an answer from a developer point of view. In my opinion, when you encounter a row contention such as the one you describe, it's because you have a bug in your application. In most cases this type of contention is a sign of a lost-update vulnerability. This thread on AskTom explains the concept of a lost update: A lost update happens ...
This answer would probably qualify for an entry in The Daily WTF. Right, after tracing the sessions and searching through USER_SOURCE - I tracked down the root cause The cause, unsurprisingly was flawed logic Recently, an update statement was added to the SP. The update statement would basically update the entire table. Apparently the developer in ...
Look in DBA_BLOCKERS and DBA_WAITERS. If you don't have these views, as SYS run @?/rdbms/admin/catblock. You can remove the lock by killing the session, but you will then need to wait for Oracle, specifically PMON, to roll back that session's outstanding work.
I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting. If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).
I have figured this on my own and I wrote on my blog. For those interested in the solution visit this posts: RangeS-S, RangeS-U, RangeX-X
Check this link out, from the looks of it, an intent exclusive (IX) lock is illegal with an RI-N and RX-X lock. Or I am waaay off base, and really didn't help at all. I found the table in the print book, Professional SQL Server 2008 Internals and Troubleshooting,
Only top voted, non community-wiki answers of a minimum length are eligible