Weird that I receive your each message twice. On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura <rsmog...@softperience.eu>wrote:
> Simple and obvious question right now do You call commit after > transaction? If yes do you use any query or connection pooler? > Yes. connection pool is used as application level, not db level. no commit after transaction is possible (I'm trying to check the logic), I just cannot imagine it happened for so many users at the same time, and then calmed down for long time, and came again. I found the query I used to log locks would miss locks that relname is null. will add that, though no idea why it's null > > ------------------------ > Regards, > Radoslaw Smogura > (mobile) > ------------------------------ > From: Tony Wang > Sent: 15 lipca 2011 03:51 > To: Scott Marlowe > Cc: PostgreSQL > > Subject: Re: [GENERAL] Weird problem that enormous locks > > 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 >
