Re: Deadlocks -- what can I do about them?

moreno(at)mochima(dot)com (Carlos Moreno) wrote in message news:<412A5C47(dot)5070908(at)mochima(dot)com>...
> Hello,
>
> I'm using PostgreSQL 7.4.3 on a RedHat 9 Linux server
> (a P4 HyperThreaded, using the SMP kernel, in case this
> makes a difference).
>
> I'm not 100% sure I understand exactly why I am causing
> them, but let's say that several inserts inside an SQL
> transaction (i.e., a BEGIN / COMMIT-ROLLBACK block),
> where each insert references two different foreign-keys
> kind of sets the alarm.
>
> The thing is, what can I do? Is it ok if I check the
> error and whenever a deadlock is detected, a execute
> a delay of some random number of milliseconds and
> then try again?
>
> After some preliminary analysis, I'm not sure there is
> anything (sensible) that I can do about those SQL insert
> statements or the fact that they're enclosed in an SQL
> transaction. I can't seem to see why those would be
> fundamentally wrong. That's why I'm looking for a
> solution that may involve recovering from the error.
> The transaction has been rolled back, so I'm guessing
> I could try again a little bit later, no?
>
> Is there some standard practice to deal with these
> deadlocks, or to avoid them in a situation like I
> described (I know, I didn't post any details, but
> details would only bore you at this point, I guess)
>
> Thanks for any advice/comments,
>
> Carlos

Best thing to do is know your locking. IF you are using Pg you have
several different levels avail. You know what causes deadlocks, the
key is to use as little locking as is necesary so that under no
circumstances /can/ you get a deadlock, while at the same time
ensuring consistency (ACID). Pg even supports row-level locks I think
so you have a lot of options.

Sometimes complex trx can cause challenges but there is always a way.

gl