Обсуждение: Misunderstanding deadlocks
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of snacktime
Sent: Thursday, October 16, 2014 1:02 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Misunderstanding deadlocks
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:
I'm running postgres 9.3 on ubuntu, configuration is the default.
Chris
Deadlocks don’t “hang forever”.
Postgres is pretty good at discovering deadlocks.
Do you see circular dependency in your locks?
The fact that some query hangs forever only means that some resource that this query is looking for was not released by some other connection (user locked some object and went for a coffee break J
Regards,
Igor Neyman
On Thu, 16 Oct 2014 10:02:08 -0700 snacktime <snacktime@gmail.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