Re: Weird problem that enormous locks

Weird that I receive your each message twice.

On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura <rsmogura(at)softperience(dot)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(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
>