I've been creating a databased application with PostgreSQL for a while now and
have loved it, though we just recently stumbled on an interesting bug.
I have a particular dialog which can do all kinds of incredibly complicated
things to the database. Of course I didn't want to have to write the "undo
it to the database" code, nor did I want to force the user into being screwed
with a stray click.
So when the dialog opens, it executes a BEGIN, and then based on whether they
clicked ok or cancel on the dialog, it'd COMMIT or ROLLBACK. This worked
fine for me in testing, but now that I have two people using this app
simulatneously, if they both click on ok at the same time, postgres seemingly
deadlocks. My setup is such that I have n ruby clients talking with DRb to
another ruby process on the server, which then does all the communication to
postgres. Each user has his/her own database connection.
So how can I accomplish this behavior without the deadlock?