Re: Weird problem that enormous locks

On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy(at)gmail(dot)com> wrote:
> On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> wrote:
>>
>> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy(at)gmail(dot)com> wrote:
>> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce(at)hogranch(dot)com>
>> > wrote:
>> > It's a game server, and the queries are updating users' money, as
>> > normal.
>> > The sql is like "UPDATE player SET money = money + 100 where id =
>> > 12345".
>> > The locks were RowExclusiveLock for the table "player" and the indexes.
>> > The
>> > weird thing is there was another ExclusiveLock for the table "player",
>> > i.e.
>> > "player" got two locks, one RowExclusiveLock and one ExclusiveLock.
>> > In the postgresql documentation
>> > (http://www.postgresql.org/docs/8.4/static/explicit-locking.html), it's
>> > said
>> > about the  Exclusive "This lock mode is not automatically acquired on
>> > user
>> > tables by any PostgreSQL command."
>>
>> You need to figure out what part of your app, or maybe a rogue
>> developer etc is throwing an exclusive lock.
>
> Yeah, that's what I'm trying to do

Cool. In your first post you said:

> select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start,
> pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age,
> age(now(),pg_stat_activity.xact_start) as transaction_age, 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 and
> substr(pg_class.relname,1,3) != 'pg_' order by query_start;

> The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are
> only AccessShareLock and RowExclusiveLock.

So what did / does current_query say when it's happening? If it says
you don't have access permission then run that query as root when it
happens again.