Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZoKIa-0007fk-UO for pgsql-admin@arkaria.postgresql.org; Mon, 19 Oct 2015 23:57:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZoKIa-0002p0-Fd for pgsql-admin@arkaria.postgresql.org; Mon, 19 Oct 2015 23:57:04 +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 1ZoKGt-0007zv-JF for pgsql-admin@postgresql.org; Mon, 19 Oct 2015 23:55:19 +0000 Received: from mcegsmtp2.alstom.com ([159.245.16.222]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZoKGn-0007Xr-Qr for pgsql-admin@postgresql.org; Mon, 19 Oct 2015 23:55:15 +0000 Received: from 041-smtp-out.Alstom.com (unknown [141.251.120.84]) by MCEGSMTP2.alstom.com with smtp (TLS: TLSv1/SSLv3,256bits,AES256-SHA256) id 68fc_000c_db404cd0_12cd_4d90_81d8_87db25d2197f; Tue, 20 Oct 2015 01:55:09 +0200 Received: from DB4PR48MB0124.041d.mgd.msft.net (141.251.120.84) by DB4PR48MB0124.041d.mgd.msft.net (141.251.120.84) with Microsoft SMTP Server (TLS) id 15.1.286.20; Mon, 19 Oct 2015 23:55:06 +0000 Received: from DB4PR48MB0124.041d.mgd.msft.net ([141.251.120.84]) by DB4PR48MB0124.041d.mgd.msft.net ([141.251.120.84]) with mapi id 15.01.0286.019; Mon, 19 Oct 2015 23:55:06 +0000 From: HEMPLEMAN Matthew To: "pgsql-admin@postgresql.org" Subject: Locking: PostgreSQL Vs. Oracle Thread-Topic: Locking: PostgreSQL Vs. Oracle Thread-Index: AdEKwtruLJ8yeWztQgyKTmorMBG/tg== Date: Mon, 19 Oct 2015 23:55:06 +0000 Message-ID: <8538b4e2251545379a2e081f68f2b53d@DB4PR48MB0124.041d.mgd.msft.net> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [141.251.119.197] Content-Type: multipart/alternative; boundary="_000_8538b4e2251545379a2e081f68f2b53dDB4PR48MB0124041dmgdmsf_" MIME-Version: 1.0 X-Pg-Spam-Score: -6.9 (------) 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 --_000_8538b4e2251545379a2e081f68f2b53dDB4PR48MB0124041dmgdmsf_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi all, I'm in the process of migrating a web application from Oracle to PostgreSQL= and, after quite a few headaches, it's almost there. The last step (I hop= e) is getting to the bottom of an intermittent locking issue I see when I r= un integration tests. Most of the time, all tests are passing, but occasio= nally a test 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 cu= rsory look at the internal PG tables, he said it looked like too many table= s were locked due locks propagating out via foreign keys. I'm not sure how= this could be possible. I've been looking into the differences between PostgreSQL and Oracle lockin= g behavior, but nothing has jumped out at me so far. I'm not a dba so hope= fully I'm missing something obvious. Any advice would be greatly appreciat= ed! Thanks. Additional information; Isolation Level: read committed for both dbs JBoss 6.1 eap PostgreSQL 9.4 Hibernate 3.64 ________________________________ 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 a= ny purpose or store or copy the information in any medium. --_000_8538b4e2251545379a2e081f68f2b53dDB4PR48MB0124041dmgdmsf_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi all,

 

I’m in the process of migrating a web applicat= ion from Oracle to PostgreSQL and, after quite a few headaches, it’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 t= est will fail with a stale state exception.  Another developer ran a f= ew IT tests and said one run stalled and never finished.  Based on a c= ursory look at the internal PG tables, he said it looked like too many tables were locked due locks propagating out via f= oreign keys.  I’m not sure how this could be possible.

 

I’ve been looking into the differences between= PostgreSQL and Oracle locking behavior, but nothing has jumped out at me s= o far.  I’m not a dba so hopefully I’m missing something o= bvious.  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


CONFIDENTIALITY : This e-ma= il and any attachments are confidential and may be privileged. If you are n= ot a named recipient, please notify the sender immediately and do not discl= ose the contents to another person, use it for any purpose or store or copy the information in any medium.
 --_000_8538b4e2251545379a2e081f68f2b53dDB4PR48MB0124041dmgdmsf_--