On Fri, Jul 15, 2011 at 10:42, Scott Marlowe <scott.marl...@gmail.com>wrote:
> On Thu, Jul 14, 2011 at 8:28 PM, Tony Wang <www...@gmail.com> wrote: > > On Fri, Jul 15, 2011 at 10:05, Scott Marlowe <scott.marl...@gmail.com> > > wrote: > >> > >> On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <www...@gmail.com> wrote: > >> > On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marl...@gmail.com > > > >> > wrote: > >> >> > >> >> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <www...@gmail.com> wrote: > >> >> > On Fri, Jul 15, 2011 at 01:13, Scott Marlowe > >> >> > <scott.marl...@gmail.com> > >> >> > wrote: > >> >> >> > >> >> >> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <www...@gmail.com> > wrote: > >> >> >> > On Thu, Jul 14, 2011 at 10:35, John R Pierce < > pie...@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? > > > > 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
