It’s quite hard to avoid thinking about gaps in a note if you visit it some time after it was originally written. Prompted by a tweet I made about this note Nenad Noveljic supplied a link to another variation on the theme with a deadlock between a library cache lock and a row cache lock. I haven’t yet worked out the exact detail of how the OP got into their specific deadlock but (especially in view of their closing comments) I don’t think it’s something I need to spend more time on. If you’ve looked at the original OTN posting you’ll see that the Chain Signature in that case is “Chain Signature: ‘library cache lock'<=’library cache pin’ (cycle)”, which indicates a collision restricted entirely to the library cache (the lock suggests someone is using a package/cursor while the pin suggests that another session is trying to destroy/(re)compile it – and they’re each trying to do it to each other’s package! (In the past when I’ve seen a deadlock of this type it’s been reported as ORA-04020 rather than ORA-00060.) The rest of the trace file told me what the other two processes had been doing when the ORA-00060 was initiated, but the point I want to pick up here is that we have a very brief summary in the “Chain Signature” that tells us we’ve had a collision between some DML (enq: TX) and some sort of cursor-like problem (library cache pin) and not a simple data cross-over. Of course, the diag process also dumped a trace file, summarising the situation and this started with the following important note:Ĭhain Signature: 'enq: TX - row lock contention'<='library cache pin' (cycle) The other process didn’t mention a deadlock, but started at the “HUNG PROCESS DIAGNOSTIC” line – the trace file had been triggered by the diag process. ![]() Trace file: /u01/app/oracle/diag/rdbms/test/test/trace/test_dia0_cĭump requested by process (pid: 8, osid: 17861, DIA0) ![]() Performing diagnostic dump and signaling ORA-00060Ĭomplete deadlock information is located in the trace file of process (pid: 8, osid: 17861, DIA0) The trace from the process that reported the deadlock started like this: While this doesn’t show up in the alert log, I do get a trace file dumped for the session in fact I got a trace file from both processes. ORA-00060: deadlock detected while waiting for resource I got the following result from session 2: Session 2: wait a couple of seconds, then execute p2.All it takes in this case is a simple sequence of actions followed by a short wait (ca. ![]() Of course you’re asking for trouble if you start doing DDL as part of your production code and you’re asking for trouble if you start playing around with autonomous transactions and if you use one to do the other you’re almost guaranteed to hit a problem eventually. It’s a situation that shouldn’t arise in a production system because it’s doing the sorts of things that you shouldn’t do in a production system: but if you’re trying to do some maintenance or upgrades while keeping the system live it could possibly happen. Prompted by a question on OTN I came up with a strategy for producing an ORA-00060 deadlock that didn’t produce a deadlock graph (because there isn’t one) and didn’t get reported in the alert log (at least, not when tested on 11.2.0.4).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |