My script locks up using DBI

[grinder]

I've been writing a lot of DBI code this year. Miles and miles of the stuff. On two different occasions, I came up with a couple of really puzzling bugs.

I resolved the bugs by myself, but I would like to share my experience, in the hope that it may help someone who winds up in the same situation. First and foremost, what you have to remember is to all intents and purposes, DBI is bug-free. Thousands of developers have been pounding on it for years, so all the show-stoppers have been shaken out. It's highly unlikely you are going to stumble upon a brand new bug.

Consider the symptoms, I had two different scripts. One worked for months and months, and suddenly stopped working. At another time I wrote a brand new script, and it didn't work, ever.

The symptoms were baffling. Bear in mind that in my case I have two DBI connections open to two separate systems, and I'm ferrying data over from one, massaging it, and posting it to the second. The first script would work correctly for an arbitrary number of records, 500, 2 000, 50 000 records, and then a call to insert or select method would just never come back.

The second script was even stranger; it would insert 134 records, and then on the 135th insert it would just lock up. No matter how I ordered the select, I could never get more than 134 over to the other, and I scratched my head wondering what kind of magic was vested in such an ordinary number.

I no longer clearly remember the path I took to solving these two bugs, probably because I had so many false starts. But in the end, this is what was going wrong.

In the first instance, I had a DBI object, from which I created two placeholder insert statements, like insert into foo (a, b) values (?, ?) and after months of loyal service I also had to insert into foo (a, b, c) values (?, ?, ?). On top of that I was doing explicit commits every n insertions, where n was 500 or 1 000. Somehow I was setting up a deadlock situation whereby I needed to commit one batch (e.g. with the 2 values) before the other (with 3 values), so when I called commit on one it blocked until the other was committed (which couldn't be, hence the hang).

It took me ages to figure out that the additional insert was the cause of the problem, because it worked correctly the first couple of times. Once I figured out what the problem was, I took the expedient hack of just enabling AutoCommit on each line, figuring if performance was too dreadful I would clean up the code and do The Right Thing. The script runs fast enough, so I've just left it as it is, but the moral of the story is "Beware of multiple statements inserting into a table". (or updates, or whatever).

The second instance was weirder, but conceptually much easier to solve. I was transferring information from the new system back to the old system (as they have to be run in parallel for a while), so in order to bring new stuff over I first have to scan the old system to see if it's truly new or just an update to existing data.

I was tripped up by laziness. On this project I developed the habit of writing code like:

  my $db = DBI->connect( DB, USER, PW ) or die "foo\"n";
  END { $db->disconnect }

i.e. when doing resource acquisition, just fire and forget: no matter how the script ends, everything will be cleaned up in an orderly fashion. Sometimes it's a useful idiom to have around.

So what was happening was that I was selecting information, and then inserting information back into the same table without having cleaned up after the select. Maybe it's something to do with indexes, but after a number of inserts, the script just locked up in the 135th insert.

I was guided by the certainty that the fault lay in my own code rather than the library code. Once it dawned on me that I was being rather cavalier with my database connections, I finish'ed them all and/or disconnect'ed as soon as possible and voilà, everything started working as expected. So the moral of that story would be "Beware of input and output on the same table".

So, should you be using DBI, and find your script locking up in similar ways, look very closely and where and when your $dbs and $sss are coming to life, and especially when are you getting rid of them? To get rid of them as soon as possible should be your golden rule.</o>

Here endeth the lesson for today...

--
g r i n d e r

[knobunc]

What database(s) are you using? The reason I ask is that a statement handle should not block another statement handle on the same DB connection.

But you post is definitely useful since I have seen many issues where there were multiple DB connections from one process that caused deadlock problems. Frequently these were because the same process locked itself by doing an uncommited update through one connection and a select through the other. Or an interaction between two processes each with a connection to two DBs. Even if your database has deadlock detection, it will be unable to detect a problem in these cases since the DB does not know that a single process has multiple connections open.

I would change your advice to the more generic make sure that you hold a lock for as short a time as possible which in this case means commit your changes as soon as possible. Do as much select work as you can before making changes & issue the commit as soon as all related work is done. And any time a process has multiple DB handles open, you have to make absolutely certain that it will not deadlock. Usually you do this by making all processes that select or update do it in a defined order. And yes, doing safe transactional computing is hard.

-ben

[George_Sherston]

grinder kindly pointed me to the present thread after I posted this related cautionary tale.

§ George Sherston