Re: Weird problem that enormous locks - Mailing list pgsql-general

On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@gmail.com> wrote:
> On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:
>> > On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com>
>> > wrote:
>> >>
>> >> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:
>> >> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.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.
>
> As I said, it's normal update like "UPDATE player SET money = money + 100
> WHERE id=12345", but there are quite many

A regular update like that can't get a full exclusive lock by itself,
there'd have to be a previous query in the same transaction that took
out an explicit lock.  Is it possible for you to set up query logging
such that you can track connections to see which one does that in the
future?

Were there more than 1 exclusive lock (now row exclusive, but just
plain exclusive)?