Search Postgresql Archives

Re: Weird problem that enormous locks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Jul 14, 2011 at 12:35, John R Pierce <pierce@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux