On Fri, Jul 15, 2011 at 19:47, Radosław Smogura <rsmogura@xxxxxxxxxxxxxxx> wrote:
On Fri, 15 Jul 2011 19:07:45 +0800, Tony Wang wrote:
On Fri, Jul 15, 2011 at 18:50, Radosław Smogura wrote:No. It's depend on pooler, application server and transaction manager, for example there are possibilities to return connection which is not associated with transaction manager, so You still need to manually commit or rollback at the end of business logic. You may return C1 to poll, and I believe Your application makes this, but transaction may be uncommited. Watch your query log if You have COMMIT or ROLLBACK there, You may as well add tracking of connection id to associate query flow per connection; or check If you have auto commit turned on.
On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote:
Actually I dont know what pool You use (I think PHP - I dont knowWeird that I receive your each message twice.
On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura wrote:
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.
no commit after transaction is possible (Im trying to check the
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
null. will add that, though no idea why its null
------------------------
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 Fri, Jul 15, 2011 at 08:22, Scott Marlowe wrote:
#ccc solid;padding-left:1ex"> > Its a game server, and theOn 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:
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px
queries are updating users money,
as
#ccc solid;padding-left:1ex"> > normal.blockquote> id =
> The sql is like "UPDATE player SET money = money + 100
where
>> > 12345".
the indexes.
0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> > The
> weird thing is there was another ExclusiveLo
ockquote class="gmail_quote" style="margin:0 0 0uot;player" got two locks, one RowExclusiveLock and one
.8ex;border-left:1px #ccc solid;padding-left:1ex">
ExclusiveLock.
kquote> acquired on
c solid;padding-left:1ex"> > user
> tables by any PostgreSQL command."
You need to figure out what part of your app, or maybe a
rogue
>> developer et
order-left:1px #ccc solid;padding-left:1ex">"gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
Yeah, thats what Im trying to do
Cool. In your first post you said:
solid;padding-left:1ex"> 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_st
e> pg_locks.pid=pg_stat_activity.procpid and,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where
> substr(pg_class.relname,1,3) != pg_ order by query_start;
cial thing I can find is that there were a lotbr>
ExclusiveLock, while its normal the locks are
only AccessShareLock and RowEx
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
[7] mailto:rsmogura@softperience.eu [8]
Links:
------
[1] mailto:wwwjfy@xxxxxxxxx [2]
[2] mailto:scott.marlowe@xxxxxxxxx [3]
[3] mailto:wwwjfy@xxxxxxxxx [4]
[4] mailto:pierce@xxxxxxxxxxxx [5]
[5]
http://www.postgresql.org/docs/8.4/static/explicit-locking.html
[6]
[6] mailto:scott.marlowe@xxxxxxxxx [7]
much about this), but I imagine following, If You dont use auto
commit or commit:
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
The interesting thing is, I didnt find any timeout/exception after the
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?
"lock" period ended in postgresql log, only long query time.
I meant I'm sure the pooler will do that, when a request ends.
Regards,
Radosław Smogura
