On Fri, Jul 15, 2011 at 19:47, Radosław Smogura <rsmog...@softperience.eu>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: >> >> On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote: >>> >>> Weird 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: [GENERAL] Weird problem that enormous locks >>>>> >>>>> On Fri, Jul 15, 2011 at 08:22, Scott Marlowe wrote: >>>>> >>>>> 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: >>>>>>> >>>>>> #ccc solid;padding-left:1ex"> > Its a game server, and the >>>>>> >>>>>> queries are updating users money, >>>>>> >>>>>> as >>>>>> class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px >>>>>> >>>>>>> #ccc solid;padding-left:1ex"> > normal. >>>>>>> >>>>>>> > The sql is like "UPDATE player SET money = money + 100 >>>>>>> where >>>>>>> >>>>>> blockquote> id = >>>>>> >> > 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 0 >>>>>>> .8ex;border-left:1px #ccc solid;padding-left:1ex"> >>>>>>> >>>>>> uot;player" got two locks, one RowExclusiveLock and one >>>>>> 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"> >>>>>>> >>>>>>> Yeah, thats what Im trying to do >>>>>>> >>>>>>> Cool. In your first post you said: >>>>>>> >>>>>> "gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc >>>>>> 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 >>>>>> >>>>>> ,pg_locks left >>>>>>> outer join pg_class on (pg_locks.relation = pg_class.oid) >>>>>>> where >>>>>>> >>>>>> e> pg_locks.pid=pg_stat_activity.**procpid and >>>>>> >>>>>> > substr(pg_class.relname,1,3) != pg_ order by query_start; >>>>>> >>>>>> cial thing I can find is that there were a lot >>>>>> >>>>>>> ExclusiveLock, while its normal the locks are >>>>>>> only AccessShareLock and RowEx >>>>>>> >>>>>> br> >>>>>> >>>>>> 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:www...@gmail.com [2] >>>> [2] mailto:scott.marl...@gmail.com [3] >>>> [3] mailto:www...@gmail.com [4] >>>> [4] mailto:pie...@hogranch.com [5] >>>> [5] >>>> http://www.postgresql.org/**docs/8.4/static/explicit-**locking.html<http://www.postgresql.org/docs/8.4/static/explicit-locking.html> >>>> [6] >>>> [6] mailto:scott.marl...@gmail.com [7] >>>> [7] mailto:rsmogura@softperience.**eu <rsmog...@softperience.eu> [8] >>>> >>> Actually I dont know what pool You use (I think PHP - I dont know >>> 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 >>> >> >> 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 didnt find any timeout/exception after the >> >> "lock" period ended in postgresql log, only long query time. >> > 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. > I meant I'm sure the pooler will do that, when a request ends. > > Regards, > Radosław Smogura > >
