Re: Weird problem that enormous locks

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?


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.

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;

can help you make sense of them.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast