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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
