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.
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
