Thanks, I've checked the "for update". No such queries there. On Thu, Jul 14, 2011 at 15:36, Radoslaw Smogura <rsmog...@softperience.eu>wrote:
> 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@postgresql.org > Subject: Re: [GENERAL] Weird problem that enormous locks > > > On Thu, Jul 14, 2011 at 12:35, John R Pierce <pie...@hogranch.com> 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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >> > >
