Re: Weird problem that enormous locks

Thanks, I've checked the "for update". No such queries there.

On Thu, Jul 14, 2011 at 15:36, Radoslaw Smogura <rsmogura(at)softperience(dot)eu>wrote:

> Once time I've read 9.x PostgreSQL locks everything before offset, if You
> execute select for update offset. Do you call such query at least once? It's
> the way why we think about having 9.x server.
>
> ------------------------
> Regards,
> Radoslaw Smogura
> (mobile)
> ------------------------------
> From: Tony Wang
> Sent: 14 lipca 2011 07:00
> To: John R Pierce
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Weird problem that enormous locks
>
>
> On Thu, Jul 14, 2011 at 12:35, John R Pierce <pierce(at)hogranch(dot)com> wrote:
>
>> On 07/13/11 8:47 PM, Tony Wang 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.
>>>
>>
>> that query should be quite fast. is it part of a larger transaction? is
>> there any possibility of multiple sessions/connections accessing the same
>> player.id?
>>
>>
> That's possible, but I think only one row will be locked for a while, but
> not thousands of locks for an hour. It's rare that thousands of users update
> the value at once.
>
>
>>
>> it would be interesting to identify the process that issued the exclusive
>> lock and determine what query/queries its made. if its not apparent in
>> pg_stat_activity, perhaps enable logging of all DDL commands, and check the
>> logs.
>>
>
> yeah, I've made the log_statement to "all" now. Previously, it only logged
> slow queries more than 50ms. I could know something from logs if it happens
> again (hope not).
>
>
>>
>> if there's a lot of active queries (you ahve 800 connections)
>>
>> select count(*),current_query from pg_stat_activity group by
>> current_query order by count(*) desc;
>>
>
> that's helpful, thanks.
>
>
>>
>> can help you make sense of them.
>>
>>
>>
>> --
>> john r pierce N 37, W 122
>> santa cruz ca mid-left coast
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>>
>
>