(Knob) Re: My script locks up using DBI

[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