Deadlock detection – unindexed foreign keys

Recently I have had a shortage of things to blog about. Nevertheless I have resisted the urge to join the cacophony by just announcing that 11g is in fact available for download on Linux.

Sure enough, eventually an opportunity has presented itself. Well, two opportunities but more of the other elsewhere later, but in this instance – deadlocks, specifically regular and numerous deadlocks caused by regular and numerous jobs, jobs which should have been non-conflicting.

However, dear reader, I shall spoil the surprise by revealing that these deadlocks were caused by unindexed foreign keys – more detail further down. It’s an old problem that’s been around for a long time, but it still happens (although, granted, more often than not in the databases that I see these days, this tends not to be a problem because people haven’t bothered with any sort of constraint in their database, but that’s a rant for another day).

I’m reluctant to admit it for fear of being shot down by the BAAG police ;-) , but at the time of my proclaimation that unindexed foreign keys was the issue, it was a hunch, a gut feeling backed by a decade of experience and the briefest of brief glances at the resulting trace file from the deadlock (is it just me or is there a blurred line between a guess and a hunch / gutfeeling / shortcut that experience suggests?) . Fortunately, a longer look at the trace file backed me up and the subsequent indexing of the columns in the foreign keys was a massive success.

From an end user perspective (including both developers connecting to the database via whatever tool and also application users), the symptoms initially were of an unresponsive development database, seemingly hanging every couple of minutes for a couple of minutes.

Immediately, to the alert log, where there were deadlock entries and in the background dump dest, where there were trace files from the jobs with more detailed information on the deadlocks detected.

Deadlock detection takes some time and so whilst waiting for these to be detected, the sessions on both sides of the deadlock were hogging the limelight and in the meantime bringing the database to its knees for everyone else.

In the trace files, the regular entries were all very similar, such as this:

the two sessions are trying to escalate a held TM lock from SX to SSX. A TM lock, a DML Enqueue, makes sure that the structure of a table is not altered whilst, in this example, you are updating a row in a table.
From the concepts guide:
A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table.
...
A share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) is more restrictive than a share table lock... Only one transaction at a time can acquire a share row exclusive table lock on a given table. A share row exclusive table lock held by a transaction allows other transactions to query or lock specific rows using SELECT with the FOR UPDATE clause, but not to update the table... A share row exclusive table lock held by a transaction prevents other transactions from obtaining row exclusive table locks and modifying the same table.

What object is this TM lock being held on?
Note the lock name – TM-00024f42-00000000 – that 00024f42 is a hex number, 151362 is its decimal converstion. Look up object_id 151362 in dba_objects and you’ll find the details of the object. In the continued interest of obscuring client code and object names, in this example it was TABLE_B, a child of TABLE_A with a foreign key enforcing the constraint but no index.

So, to the solution – to index all the foreign keys between these handful of tables. A bit of a sledgehammer to crack a nut approach, but under the circumstances and the local urgency to get this done and out, an advisable one with the caveat that there might be some impact to blindly adding indexes left, right and centre.

In terms of SQL to identify unindexed foreign keys, I was lazy and did a search on google, albeit with this caveat in mind. Interestingly I found a script on experts-exchange that I wrote to do exactly the same thing nearly a decade ago. But it was a rubbish script, and I used the one linked to from Tom Kyte’s page.

Just as a conclusion on this topic, note some points that Tom Kyte makes on how things change, the subject matter of which is exactly this problem. Also, I managed to deadlock myself in a single session last week by using an autonomous transaction to update a table, the child of which had been modified by the outer transaction – not clever.

Like this:

LikeLoading...

Related

6 Responses to Deadlock detection – unindexed foreign keys

“Is it just me or is there a blurred line between a guess and a hunch / gutfeeling / shortcut that experience suggests?”
I think the difference between a “guess” and a “hypothesis” is that you make an attempt to validate the hypothesis before proceeding, like you did in check the trace files.
My feeling on BAAG is that they are against making changes based on guesses, rather than using them as a starting point for investigation.

Also on the “hunch/gutfeeling” topic, I’ve been reading lately a good book that discusses research on the process of decision-making that gives some credence to the “skilled guess” way of doing things. “Sources of Power: How People Make Decisions”, Gary A. Klein.