On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@xxxxxxxxx> wrote: > On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@xxxxxxxxx> > wrote: >> >> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@xxxxxxxxx> wrote: >> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@xxxxxxxxxxxx> >> > 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
