On Fri, Jul 15, 2011 at 18:50, Radosław Smogura <rsmogura@xxxxxxxxxxxxxxx> wrote:
On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote:
Actually I don't know what pool You use (I think PHP - I don't know much about this), but I imagine following, If You don't use auto commit or commit:Weird that I receive your each message twice.On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura wrote:
no commit after transaction is possible (Im trying to check the
Simple and obvious question right now do You call commit after
transaction? If yes do you use any query or connection pooler?
Yes. connection pool is used as application level, not db level.null. will add that, though no idea why its null
logic), I just cannot imagine it happened for so many users at the
same time, and then calmed down for long time, and came again.
I found the query I used to log locks would miss locks that relname is
------------------------On Fri, Jul 15, 2011 at 08:22, Scott Marlowe wrote:
Regards,
Radoslaw Smogura
(mobile)
-------------------------
From: Tony Wang
Sent: 15 lipca 2011 03:51
To: Scott Marlowe
Cc: PostgreSQL
Subject: Re: Weird problem that enormous locks
On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang wrote:> 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
> Its a game server, and the queries are updating users money,
as[5]), its
id => normal.
> The sql is like "UPDATE player SET money = money + 100 where
>> > 12345".
the indexes.> The locks were RowExclusiveLock for the table "player" and
"player",> The
> weird thing is there was another ExclusiveLock for the table
>> > i.e.
ExclusiveLock.> "player" got two locks, one RowExclusiveLock and one
(http://www.postgresql.org/docs/8.4/static/explicit-locking.html> In the postgresql documentation
>
>> > said
acquired on> about the Exclusive "This lock mode is not automatically
>> developer etc is throwing an exclusive lock.> user
> tables by any PostgreSQL command."
You need to figure out what part of your app, or maybe a rogue
Yeah, thats what Im trying to do
ExclusiveLock, while its normal the locks are
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) wherepg_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
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.
As I said, its normal update like "UPDATE player SET money = money +
100 WHERE id=12345", but there are quite many
Links:
------
[1] mailto:wwwjfy@xxxxxxxxx
[2] mailto:scott.marlowe@xxxxxxxxx
[3] mailto:wwwjfy@xxxxxxxxx
[4] mailto:pierce@xxxxxxxxxxxx
[5] http://www.postgresql.org/docs/8.4/static/explicit-locking.html
[6] mailto:scott.marlowe@xxxxxxxxx
[7] mailto:rsmogura@softperience.eu
1. User A updates moneys, gets connections C1, locks his row, no commit
2. User A updates moneys again, gets connection C2, but C1 still holds lock.
Regards,
Radosław Smogura
Any connection pool behaves similarly. The connection C1 surely will be committed and returned after the operation finished. Having said that, the ONLY possible reason is some transactions hanged holding the locks, and cause others cannot work any more, and the "ExclusiveLock" is not a problem, right?
The interesting thing is, I didn't find any timeout/exception after the "lock" period ended in postgresql log, only long query time.
