Re: Weird problem that enormous locks

On Fri, Jul 15, 2011 at 10:42, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Thu, Jul 14, 2011 at 8:28 PM, Tony Wang <wwwjfy(at)gmail(dot)com> wrote:
> > On Fri, Jul 15, 2011 at 10:05, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> > wrote:
> >>
> >> On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy(at)gmail(dot)com> wrote:
> >> > On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com
> >
> >> > wrote:
> >> >>
> >> >> 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.
> >> >
> >> > 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?
> >
> > Yeah, and I also wonder when will an ExclusiveLock acquired.
> > I set up query logging after that, that'll be really big file.
> >
> >>
> >> Were there more than 1 exclusive lock (now row exclusive, but just
> >> plain exclusive)?
> >
> > There were many such locks (not row exclusive) updating different player
> id.
>
> How many just plain exclusive locks were there?
>

There were 2519 RowExclusiveLock and 85 ExclusiveLock