Re: Misunderstanding deadlocks

On Thu, 16 Oct 2014 10:02:08 -0700
snacktime <snacktime(at)gmail(dot)com> wrote:

> I'm confused about how deadlock detection and breaking deadlocks works.
> Googling around it seems that the server should be detecting deadlocks and
> aborting one of the queries.
>
> But I'm getting occasional deadlocks that literally hang forever. I'm
> assuming they are deadlocks because they show up when running the queries I
> got from this url:
>
> https://wiki.postgresql.org/wiki/Lock_Monitoring
>
> I'm running postgres 9.3 on ubuntu, configuration is the default.

Yes, PostgreSQL will detect deadlocks and randomly kill one of the locked
queries to break the deadlock.

Without seeing the actual queries you're having trouble with, I can only
speculate, but my speculation is that you're creating a situation that
creates a deadlock in the application that is not detectable from Postgres.
This isn't that hard to do, really. For example, if you're using Java, the
multi-threaded synchronization doesn't have deadlock detection, so if your
taking out some DB locks in PG, then grabbing some Object locks in Java that
create a Java deadlock, Java will hold the PG locks until you kill the
process.

The other thing I've seen people get confused about is the fact that deadlocks
have nothing to do with time. A deadlock is a very specific condition where it
becomes impossible for two processes to ever move forward, and this is what
Postgres detects and breaks. It's entirely possible that your application could
be taking out non-deadlocking locks on the DB and holding them for a long time
for whatever reason, causing other processes to wait. This is a performance
problem to be solved, but it is _not_ a deadlock because eventually the blocking
process will finish and other processes will be allowed to continue. A common
mistake I've seen is processes that start transactions, then lock various tables,
then don't commit the transaction until some other time-consuming operation
finishes. E.g., not committing a transaction until a web browser responds is
putting the ability to lock your database solid into the hands of anyone who
can access your web site.

HTH

--
Bill Moran
I need your help to succeed:
http://gamesbybill.com