On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang wrote:
On Fri, Jul 15, 2011 at 01:13, Scott Marlowe
> wrote:
On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang wrote:
> On Thu, Jul 14, 2011 at 10:35, John R Pierce
>> > wrote:
> Its 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
[5]), its
>> > 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, thats what Im 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 its normal the locks are
only AccessShareLock and RowExclusiveLock.
So what did / does current_query say when its happening? If it
says
you dont have access permission then run that query as root when
it
happens again.
