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

From Tony Wang
Subject Re: Weird problem that enormous locks
Date
Msg-id CAH1z_A7uF6f4qywz7oCQ_vFnWUkjcFJzTfzbMoON6u8ZEcmPng@mail.gmail.com
Whole thread Raw
In response to Re: Weird problem that enormous locks  (John R Pierce <pierce@hogranch.com>)
List pgsql-general
On Thu, Jul 14, 2011 at 12:35, John R Pierce <pierce@hogranch.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: Weird problem that enormous locks
Next
From: "Lars Kanis"
Date:
Subject: Re: Using LDAP roles in PostgreSQL