On Thu, Jul 14, 2011 at 8:28 PM, Tony Wang <wwwjfy@xxxxxxxxx> wrote: > On Fri, Jul 15, 2011 at 10:05, Scott Marlowe <scott.marlowe@xxxxxxxxx> > wrote: >> >> On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@xxxxxxxxx> wrote: >> > On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@xxxxxxxxx> >> > wrote: >> >> >> >> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@xxxxxxxxx> wrote: >> >> > On Fri, Jul 15, 2011 at 01:13, Scott Marlowe >> >> > <scott.marlowe@xxxxxxxxx> >> >> > wrote: >> >> >> >> >> >> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@xxxxxxxxx> wrote: >> >> >> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@xxxxxxxxxxxx> >> >> >> > 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. >> >> >> > In the postgresql documentation >> >> >> > (http://www.postgresql.org/docs/8.4/static/explicit-locking.html), >> >> >> > it's >> >> >> > said >> >> >> > about the Exclusive "This lock mode is not automatically acquired >> >> >> > on >> >> >> > user >> >> >> > tables by any PostgreSQL command." >> >> >> >> >> >> You need to figure out what part of your app, or maybe a rogue >> >> >> developer etc is throwing an exclusive lock. >> >> > >> >> > Yeah, that's what I'm trying to do >> >> >> >> Cool. In your first post you said: >> >> >> >> > select pg_class.relname, pg_locks.mode, pg_locks.granted, >> >> > pg_stat_activity.current_query, pg_stat_activity.query_start, >> >> > pg_stat_activity.xact_start as transaction_start, >> >> > age(now(),pg_stat_activity.query_start) as query_age, >> >> > age(now(),pg_stat_activity.xact_start) as transaction_age, >> >> > pg_stat_activity.procpid from pg_stat_activity,pg_locks left >> >> > outer join pg_class on (pg_locks.relation = pg_class.oid) where >> >> > pg_locks.pid=pg_stat_activity.procpid and >> >> > substr(pg_class.relname,1,3) != 'pg_' order by query_start; >> >> >> >> > The only special thing I can find is that there were a lot >> >> > ExclusiveLock, while it's normal the locks are >> >> > only AccessShareLock and RowExclusiveLock. >> >> >> >> So what did / does current_query say when it's happening? If it says >> >> you don't have access permission then run that query as root when it >> >> happens again. >> > >> > As I said, it's normal update like "UPDATE player SET money = money + >> > 100 >> > WHERE id=12345", but there are quite many >> >> A regular update like that can't get a full exclusive lock by itself, >> there'd have to be a previous query in the same transaction that took >> out an explicit lock. Is it possible for you to set up query logging >> such that you can track connections to see which one does that in the >> future? > > Yeah, and I also wonder when will an ExclusiveLock acquired. > I set up query logging after that, that'll be really big file. > >> >> Were there more than 1 exclusive lock (now row exclusive, but just >> plain exclusive)? > > There were many such locks (not row exclusive) updating different player id. How many just plain exclusive locks were there? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
