Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZoKnx-0000bo-FH for pgsql-admin@arkaria.postgresql.org; Tue, 20 Oct 2015 00:29:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZoKnw-0003ef-BH for pgsql-admin@arkaria.postgresql.org; Tue, 20 Oct 2015 00:29:28 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZoKmH-0001lh-Jj for pgsql-admin@postgresql.org; Tue, 20 Oct 2015 00:27:45 +0000 Received: from mail-ig0-f175.google.com ([209.85.213.175]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZoKmA-0008ER-AK for pgsql-admin@postgresql.org; Tue, 20 Oct 2015 00:27:44 +0000 Received: by igbdj2 with SMTP id dj2so3263901igb.1 for ; Mon, 19 Oct 2015 17:27:37 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:content-type:mime-version:subject:from :in-reply-to:date:cc:content-transfer-encoding:message-id:references :to; bh=RmVgYzvbI/0nghES52/UixGXdW7Lf5cUmECixDIGQwc=; b=Diz7CAgTKHyaC+2rI+anj8R8/zsOepNbwWZbqKAC+1MfmO+YbCkxjn6/Y5z774L4vV rjbtcJrH2ZRqxpaPxYCavEIlsqUY1e+keXwzLPeHGuprieDStuJ2KKaLyksZZD6vcccA ryE84zuA67opYJq0Wks/g8h3NWsdfo/ZGq6dL7w63QpXOsYR4BD3bTQK/VqRhYASJv2t KIkZZkt5rI62NiNNYZB2mUAd0dA0Ze7b/StfhBZz6sj1rXZI6BeFDvfT3pcfN2QTcD+d x9LaIJODumExV5GtACmXxD8CtMSVmomXRC8pHoTN7ew4bFy7SqLdoF401hoY73qrw+Fd 6vCw== X-Gm-Message-State: ALoCoQmPLtIWvr6lIY3ZBc3liSy90Hrc5fzb/iPy4bHr7i0w4hL9vEe/e4RGmklq1vWoejA8U9hQ X-Received: by 10.50.20.38 with SMTP id k6mr19264039ige.97.1445300857320; Mon, 19 Oct 2015 17:27:37 -0700 (PDT) Received: from [100.120.104.249] (128.sub-70-195-137.myvzw.com. [70.195.137.128]) by smtp.gmail.com with ESMTPSA id cj4sm343635igc.16.2015.10.19.17.27.35 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Mon, 19 Oct 2015 17:27:36 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-143EC4B2-6883-4638-BF24-7C50F14883FD Mime-Version: 1.0 (1.0) Subject: Re: Locking: PostgreSQL Vs. Oracle From: Scott Mead X-Mailer: iPhone Mail (13A452) In-Reply-To: <8538b4e2251545379a2e081f68f2b53d@DB4PR48MB0124.041d.mgd.msft.net> Date: Mon, 19 Oct 2015 20:27:34 -0400 Cc: "pgsql-admin@postgresql.org" Content-Transfer-Encoding: 7bit Message-Id: References: <8538b4e2251545379a2e081f68f2b53d@DB4PR48MB0124.041d.mgd.msft.net> To: HEMPLEMAN Matthew X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --Apple-Mail-143EC4B2-6883-4638-BF24-7C50F14883FD Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable > On Oct 19, 2015, at 19:55, HEMPLEMAN Matthew wrote: >=20 > Hi all, > =20 > I=E2=80=99m in the process of migrating a web application from Oracle to P= ostgreSQL and, after quite a few headaches, it=E2=80=99s almost there. The l= ast step (I hope) is getting to the bottom of an intermittent locking issue I= see when I run integration tests. Most of the time, all tests are passing,= but occasionally a test will fail with a stale state exception. Another de= veloper ran a few IT tests and said one run stalled and never finished. Bas= ed on a cursory look at the internal PG tables, he said it looked like too m= any tables were locked due locks propagating out via foreign keys. I=E2=80=99= m not sure how this could be possible. > =20 > I=E2=80=99ve been looking into the differences between PostgreSQL and Orac= le locking behavior, but nothing has jumped out at me so far. I=E2=80=99m n= ot a dba so hopefully I=E2=80=99m missing something obvious. Any advice wou= ld be greatly appreciated! Thanks. > =20 > =20 > Additional information; > =20 > Isolation Level: read committed for both dbs > JBoss 6.1 eap > PostgreSQL 9.4 > Hibernate 3.64 Very commonly, you'll run in to a state know as 'idle in transaction'. (Sele= ct pid,datname,waiting,state,query from pg_stat_activity;) =20 I've seen this on many apps migrated to Postgres from Oracle / SQLServer.= Essentially, this means that you are: 1. Not in auto-commit 2. You have started a transaction (BEGIN;) 3. You may have done some amount of work 4. You never ended the transaction ( COMMIT, ROLLBACK, etc...) When this happens any locks (from row-updates / deletes, etc...) could possi= bly cause lock contention. You if you look at pg_stat_activity where waitin= g =3D't' you can correlate this (via pid) to pg_locks.=20 You can also look at pg_locks where granted=3D'f', look at the type of lo= ck (say accessexclusive on a relation) then select * from pg_locks where rel= ation=3D'xyz' and see who has been granted the lock, then, go back to pg_sta= t_activity to find the offender.=20 Just a note: if you see 'idle in transaction in 'pg_stat_activity', the qu= ery column contains the query that was previously executed before the applic= ation stopped issuing commands.=20 Good luck! -- Scott Mead OpenSCG PostgreSQL, Linux and Java experts www.openscg.com scottm@openscg.com ( via mobile ) >=20 > CONFIDENTIALITY : This e-mail and any attachments are confidential and may= be privileged. If you are not a named recipient, please notify the sender i= mmediately and do not disclose the contents to another person, use it for an= y purpose or store or copy the information in any medium. --Apple-Mail-143EC4B2-6883-4638-BF24-7C50F14883FD Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

On Oct 19, 2015, at 19:= 55, HEMPLEMAN Matthew <ma= tthew.hempleman@alstom.com> wrote:

=

Hi all,

 

I=E2=80=99m in the process of migrating a web applica= tion from Oracle to PostgreSQL and, after quite a few headaches, it=E2=80=99= s almost there.  The last step (I hope) is getting to the bottom of an i= ntermittent locking issue I see when I run integration tests.  Most of the time, all tests are passing, but occasionally a te= st will fail with a stale state exception.  Another developer ran a few= IT tests and said one run stalled and never finished.  Based on a curs= ory look at the internal PG tables, he said it looked like too many tables were locked due locks propagating out via fo= reign keys.  I=E2=80=99m not sure how this could be possible.

 

I=E2=80=99ve been looking into the differences betwee= n PostgreSQL and Oracle locking behavior, but nothing has jumped out at me s= o far.  I=E2=80=99m not a dba so hopefully I=E2=80=99m missing somethin= g obvious.  Any advice would be greatly appreciated!  Thanks.=

 

 

Additional information;

 

Isolation Level:  read committed for both dbs

JBoss 6.1 eap

PostgreSQL 9.4

Hibernate 3.64

Very c= ommonly, you'll run in to a state know as 'idle in transaction'. (Select pid= ,datname,waiting,state,query from pg_stat_activity;)
  <= /div>
   I've seen this on many apps migrated to Postgres from= Oracle / SQLServer. Essentially, this means that you are:
1. Not i= n auto-commit
2. You have started a transaction (BEGIN;)
3. You may have done some amount of work
4. You never ended the t= ransaction ( COMMIT, ROLLBACK, etc...)

When this ha= ppens any locks (from row-updates / deletes, etc...) could possibly cause lo= ck contention.  You if you look at pg_stat_activity where waiting =3D't= ' you can correlate this (via pid) to pg_locks. 

   You can also look at pg_locks where granted=3D'f', look at t= he type of lock (say accessexclusive on a relation) then select * from pg_lo= cks where relation=3D'xyz' and see who has been granted the lock, then, go b= ack to pg_stat_activity to find the offender. 

  Just a note: if you see 'idle in transaction in 'pg_stat_activity', t= he query column contains the query that was previously executed before the a= pplication stopped issuing commands. 

  G= ood luck!

--
Scott Mead
OpenSCG=
PostgreSQL, Linux and Java experts

scottm@openscg.com ( via mobile )




CONFIDENTIALITY : This e-mai= l and any attachments are confidential and may be privileged. If you are not= a named recipient, please notify the sender immediately and do not disclose= the contents to another person, use it for any purpose or store or copy the information in any medium.
= --Apple-Mail-143EC4B2-6883-4638-BF24-7C50F14883FD--