On Wed, 26 Nov 2014 02:37:51 +0530 Dev Kumkar <devdas.kumkar@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
