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