Re: Weird problem that enormous locks - Mailing list pgsql-general

 On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote:
> Weird that I receive your each message twice.
>
> On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura  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 (Im 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 its 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 wrote:
>>
>>> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang wrote:
>>>> On Fri, Jul 15, 2011 at 01:13, Scott Marlowe
>>> > wrote:
>>>>>
>>>>> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang wrote:
>>>
>>>>> > On Thu, Jul 14, 2011 at 10:35, John R Pierce
>>> >> > wrote:
>>>>> > Its 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
>>> [5]), its
>>> >> > 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, thats what Im 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 its normal the locks are
>>>> only AccessShareLock and RowExclusiveLock.
>>>
>>> So what did / does current_query say when its happening?  If it
>>> says
>>> you dont have access permission then run that query as root when
>>> it
>>> happens again.
>>
>> As I said, its normal update like "UPDATE player SET money = money +
>> 100 WHERE id=12345", but there are quite many
>
>
>
> Links:
> ------
> [1] mailto:wwwjfy@gmail.com
> [2] mailto:scott.marlowe@gmail.com
> [3] mailto:wwwjfy@gmail.com
> [4] mailto:pierce@hogranch.com
> [5] http://www.postgresql.org/docs/8.4/static/explicit-locking.html
> [6] mailto:scott.marlowe@gmail.com
> [7] mailto:rsmogura@softperience.eu
 Actually I don't know what pool You use (I think PHP - I don't know
 much about this), but I imagine following, If You don't use auto commit
 or commit:
 1. User A updates moneys, gets connections C1, locks his row, no commit
 2. User A updates moneys again, gets connection C2, but C1 still holds
 lock.
 Regards,
 Radosław Smogura