Re: 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>
>