Re: Lock Management: Waiting on locks - Mailing list pgsql-general

On Wed, 26 Nov 2014 02:37:51 +0530
Dev Kumkar <devdas.kumkar@gmail.com> wrote:

> Am debugging a race condition scenario where multiple transaction are
> running in parallel and there are insert/update these transactions are
> performing.
>
> I was able to identify the blocking query and blocked query using following
> SQL.
>
> However observed the blocking query is holding the locks on the tuple in
> minutes thereby making the other transaction query to wait on locks and
> slowness.
>
> Can I get more insight why the blocking query is not releasing locks?

In addition to what you're getting from that query, include the xact_start and
state_change columns from pg_stat_activity. My guess is that your code is
starting a transaction, then running a query, then processing the query
results before committing the transaction. Since query locks are held for
the duration of the transaction, this will cause the locks to be held for a
long time if the processing step takes a while.

That's quite a wild guess for me to make: you haven't really provided enough
information for anyone to to make an intelligent response. However, I've
seen the problem I just described often enough that I figured I'd suggest it
as a guess. You should be able to determine if that's what's happening with
the additional columns as well as a look at whatever code is running the
queries.

If that turns out not to be the problem, then you'll probably need to provide
a bit more detail before anyone will be able to provide a better answer. I
mean, I'm even guessing that it's an app making the queries.

> SELECT
>     kl.pid as blocking_pid,
>     ka.usename as blocking_user,
>     ka.query as blocking_query,
>     bl.pid as blocked_pid,
>     a.usename as blocked_user,
>     a.query as blocked_query,
>     to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
> FROM pg_catalog.pg_locks bl
>     JOIN pg_catalog.pg_stat_activity a
>         ON bl.pid = a.pid
>     JOIN pg_catalog.pg_locks kl
>         ON bl.locktype = kl.locktype
>         and bl.database is not distinct from kl.database
>         and bl.relation is not distinct from kl.relation
>         and bl.page is not distinct from kl.page
>         and bl.tuple is not distinct from kl.tuple
>         and bl.virtualxid is not distinct from kl.virtualxid
>         and bl.transactionid is not distinct from kl.transactionid
>         and bl.classid is not distinct from kl.classid
>         and bl.objid is not distinct from kl.objid
>         and bl.objsubid is not distinct from kl.objsubid
>         and bl.pid <> kl.pid
>     JOIN pg_catalog.pg_stat_activity ka
>         ON kl.pid = ka.pid
> WHERE kl.granted and not bl.granted
> ORDER BY a.query_start;
>
> Regards...


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