On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura <rsmogura@xxxxxxxxxxxxxxx> 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: Weird problem that enormous locks
On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
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 doCool. In your first post you said:So what did / does current_query say when it's happening? If it says
> 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.
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
