Re: Weird problem that enormous locks

[Radoslaw Smogura <rsmogura@xxxxxxxxxxxxxxx>]

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.

------------------------
Regards,
Radoslaw Smogura
(mobile)

---

From: Tony Wang
Sent: 14 lipca 2011 07:00
To: John R Pierce
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re:  Weird problem that enormous locks

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