Re: Weird problem that enormous locks

On Fri, Jul 15, 2011 at 19:47, Radosław Smogura <rsmogura(at)softperience(dot)eu>wrote:

> On Fri, 15 Jul 2011 19:07:45 +0800, Tony Wang wrote:
>
>> On Fri, Jul 15, 2011 at 18:50, Radosław Smogura wrote:
>>
>> 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:
>>>>>>>
>>>>>> #ccc solid;padding-left:1ex"> > Its a game server, and the
>>>>>>
>>>>>> queries are updating users money,
>>>>>>
>>>>>> as
>>>>>> class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px
>>>>>>
>>>>>>> #ccc solid;padding-left:1ex"> > normal.
>>>>>>>
>>>>>>> > The sql is like "UPDATE player SET money = money + 100
>>>>>>> where
>>>>>>>
>>>>>> blockquote> id =
>>>>>> >> > 12345".
>>>>>> the indexes.
>>>>>> 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> > The
>>>>>>
>>>>>> > weird thing is there was another ExclusiveLo
>>>>>>
>>>>>> ockquote class="gmail_quote" style="margin:0 0 0
>>>>>>> .8ex;border-left:1px #ccc solid;padding-left:1ex">
>>>>>>>
>>>>>> uot;player" got two locks, one RowExclusiveLock and one
>>>>>> ExclusiveLock.
>>>>>> kquote> acquired on
>>>>>> c solid;padding-left:1ex"> > user
>>>>>>
>>>>>> > tables by any PostgreSQL command."
>>>>>>
>>>>>> You need to figure out what part of your app, or maybe a
>>>>>> rogue
>>>>>> >> developer et
>>>>>>
>>>>>> order-left:1px #ccc solid;padding-left:1ex">
>>>>>>>
>>>>>>> Yeah, thats what Im trying to do
>>>>>>>
>>>>>>> Cool. In your first post you said:
>>>>>>>
>>>>>> "gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
>>>>>> solid;padding-left:1ex"> 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_st
>>>>>>
>>>>>> ,pg_locks left
>>>>>>> outer join pg_class on (pg_locks.relation = pg_class.oid)
>>>>>>> where
>>>>>>>
>>>>>> e> pg_locks.pid=pg_stat_activity.**procpid and
>>>>>>
>>>>>> > substr(pg_class.relname,1,3) != pg_ order by query_start;
>>>>>>
>>>>>> cial thing I can find is that there were a lot
>>>>>>
>>>>>>> ExclusiveLock, while its normal the locks are
>>>>>>> only AccessShareLock and RowEx
>>>>>>>
>>>>>> br>
>>>>>>
>>>>>> 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(at)gmail(dot)com [2]
>>>> [2] mailto:scott(dot)marlowe(at)gmail(dot)com [3]
>>>> [3] mailto:wwwjfy(at)gmail(dot)com [4]
>>>> [4] mailto:pierce(at)hogranch(dot)com [5]
>>>> [5]
>>>> http://www.postgresql.org/**docs/8.4/static/explicit-**locking.html<http://www.postgresql.org/docs/8.4/static/explicit-locking.html>
>>>> [6]
>>>> [6] mailto:scott(dot)marlowe(at)gmail(dot)com [7]
>>>> [7] mailto:rsmogura(at)softperience(dot)**eu <rsmogura(at)softperience(dot)eu> [8]
>>>>
>>> Actually I dont know what pool You use (I think PHP - I dont know
>>> much about this), but I imagine following, If You dont 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
>>>
>>
>> Any connection pool behaves similarly. The connection C1 surely will
>> be committed and returned after the operation finished. Having said
>> that, the ONLY possible reason is some transactions hanged holding the
>> locks, and cause others cannot work any more, and the "ExclusiveLock"
>> is not a problem, right?
>> The interesting thing is, I didnt find any timeout/exception after the
>>
>> "lock" period ended in postgresql log, only long query time.
>>
> No. It's depend on pooler, application server and transaction manager, for
> example there are possibilities to return connection which is not associated
> with transaction manager, so You still need to manually commit or rollback
> at the end of business logic. You may return C1 to poll, and I believe Your
> application makes this, but transaction may be uncommited. Watch your query
> log if You have COMMIT or ROLLBACK there, You may as well add tracking of
> connection id to associate query flow per connection; or check If you have
> auto commit turned on.
>

I meant I'm sure the pooler will do that, when a request ends.

>
> Regards,
> Radosław Smogura
>
>