Recently we discovered that a stored procedure that we run locks some
table(s) and prevents some SQL from running.
We discovered this because doing a simple grep against postgres
processes revealed several processes "WAITING":
-- snip --
00:04:31 postgres: dataman our_db 10.0.0.103 INSERT waiting
01:10:30 postgres: dataman our_db 10.0.0.103 UPDATE waiting
00:07:04 postgres: dataman our_db 10.0.0.103 UPDATE waiting
00:03:52 postgres: dataman our_db 10.0.0.103 INSERT waiting
00:04:30 postgres: dataman our_db 10.0.0.103 INSERT waiting
01:31:37 postgres: dataman our_db 10.0.0.103 SELECT
00:02:21 postgres: dataman our_db 10.0.0.103 INSERT waiting
00:02:58 postgres: dataman our_db 10.0.0.103 UPDATE waiting
-- snip
The SELECT statement listed is our stored procedure -- it takes about an
hour and a half to run.
I'm trying to figure out how to avoid the locking.
I ran a query against the pg_locks, but am having some trouble
dissecting it.
Query I ran:
select pg_stat_activity.datname, pg_class.relname, pg_locks.transaction,
pg_locks.mode, pg_locks.granted, pg_stat_activity.usename,
substr(pg_stat_activity.current_query,1,30), pg_stat_activity.procpid
from pg_stat_activity,pg_locks left outer join pg_class on
(pg_locks.relation = pg_class.oid) where
pg_locks.pid=pg_stat_activity.procpid;
One thing I noticed is it seems like for every transaction lock there's
an "ExclusiveLock" (to be expected, a lock on the transaction num) as
well as a "ShareLock" on the same transaction which has not been granted
the lock -- does this mean that two processes are trying to share the
same transaction? Could this be the case?
I also see a couple "RowExclusiveLock"s related to the stored procedure
that we're running.
Am I on the right track here? Any suggestions would be very much
appreciated as I'm still a little lost on what exactly is holding the
lock(s).
Thanks -
Mott
