Hunting deadlocks (part 1)

Occasionally, just occasionally, I am asked to leave my sane, safe data warehouse world (OK, not many DBAs out there would agree with that) and take a look at some other systems. For the past couple of days I have been looking at ‘deadlock’ problems on a legacy application and to suggest solutions that did not involve the application code.

Probably the best description of the application is a data switch interfacing a company’s 2000 or so retail outlets with their central accounting and DW systems. The application does a few other things in the working day but for the batch is just moves data around. The application is about 8 years old and was written in a 4GL object-orientated language that is no longer on this planet. Coupled with the excessive zeal of the designers to implement every last aspect of OO dogma they could come up with such as 5 levels of abstraction and a single-table-to-contain-everything database design. Tempered with the developers sense of practicality to introduce more tables to allow the application to actually work and you get a dog’s breakfast of an application that is almost impossible to find contract programming skills to fix or enhance (or even understand). Can any one tell me why the developers insisted that all tables had six-character table names (padded with Xs if need be) and how that boosts performance?

Deadlocks have always been an intermittent feature of the application. The developers could never come up with a reason why as only one session was updating a record at time and all accesses were by primary key. Recently the number of deadlocks has increased and as the application has not been changed it was thought to be a problem with the data or a business process change.

Next part – deadlock investigation and possible solutions.