pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Locking: PostgreSQL Vs. Oracle 3+ messages / 3 participants [nested] [flat]
* Locking: PostgreSQL Vs. Oracle @ 2015-10-19 23:55 HEMPLEMAN Matthew <matthew.hempleman@alstom.com> 2015-10-20 00:27 ` Re: Locking: PostgreSQL Vs. Oracle Scott Mead <scottm@openscg.com> 2015-10-20 09:45 ` Re: Locking: PostgreSQL Vs. Oracle Albe Laurenz <laurenz.albe@wien.gv.at> 0 siblings, 2 replies; 3+ messages in thread From: HEMPLEMAN Matthew @ 2015-10-19 23:55 UTC (permalink / raw) To: pgsql-admin 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 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 developer ran a few IT tests and said one run stalled and never finished. Based on a cursory look at the internal PG tables, he said it looked like too many tables 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 locking behavior, but nothing has jumped out at me so far. I'm not a dba so hopefully I'm missing something 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 ________________________________ CONFIDENTIALITY : This e-mail 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Locking: PostgreSQL Vs. Oracle 2015-10-19 23:55 Locking: PostgreSQL Vs. Oracle HEMPLEMAN Matthew <matthew.hempleman@alstom.com> @ 2015-10-20 00:27 ` Scott Mead <scottm@openscg.com> 1 sibling, 0 replies; 3+ messages in thread From: Scott Mead @ 2015-10-20 00:27 UTC (permalink / raw) To: HEMPLEMAN Matthew <matthew.hempleman@alstom.com>; +Cc: pgsql-admin > On Oct 19, 2015, at 19:55, HEMPLEMAN Matthew <matthew.hempleman@alstom.com> wrote: > > 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 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 developer ran a few IT tests and said one run stalled and never finished. Based on a cursory look at the internal PG tables, he said it looked like too many tables 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 locking behavior, but nothing has jumped out at me so far. I’m not a dba so hopefully I’m missing something 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 commonly, you'll run in to a state know as 'idle in transaction'. (Select pid,datname,waiting,state,query from pg_stat_activity;) 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 possibly cause lock contention. You if you look at pg_stat_activity where waiting ='t' you can correlate this (via pid) to pg_locks. You can also look at pg_locks where granted='f', look at the type of lock (say accessexclusive on a relation) then select * from pg_locks where relation='xyz' and see who has been granted the lock, then, go back to pg_stat_activity to find the offender. Just a note: if you see 'idle in transaction in 'pg_stat_activity', the query column contains the query that was previously executed before the application stopped issuing commands. Good luck! -- Scott Mead OpenSCG PostgreSQL, Linux and Java experts www.openscg.com scottm@openscg.com ( via mobile ) > > CONFIDENTIALITY : This e-mail 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Locking: PostgreSQL Vs. Oracle 2015-10-19 23:55 Locking: PostgreSQL Vs. Oracle HEMPLEMAN Matthew <matthew.hempleman@alstom.com> @ 2015-10-20 09:45 ` Albe Laurenz <laurenz.albe@wien.gv.at> 1 sibling, 0 replies; 3+ messages in thread From: Albe Laurenz @ 2015-10-20 09:45 UTC (permalink / raw) To: 'HEMPLEMAN Matthew *EXTERN*' <matthew.hempleman@alstom.com>; pgsql-admin HEMPLEMAN Matthew wrote: > 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 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 developer ran a few IT tests and > said one run stalled and never finished. Based on a cursory look at the internal PG tables, he said > it looked like too many tables were locked due locks propagating out via foreign keys. I’m not sure > how this could be possible. Assume you have two tables referencing each other, e.g.: CREATE TABLE parent ( parent_id integer PRIMARY KEY, parent_val text ); CREATE TABLE child ( child_id integer PRIMARY KEY, parent_id integer REFERENCES parent(parent_id), child_val text ); and an entry in "parent", like INSERT INTO parent (parent_id, parent_val) VALUES (1, 'something'); Then if you insert something in "child", like: BEGIN; INSERT INTO child (child_id, parent_id, child_val) VALUES (42, 1, 'other'); you will have a lock on the corresponding row of the "parent" table as long as the transaction lasts. This is necessary because if another concurrent session were allowed to e.g. delete the entry in "parent", the foreign key constraint would be violated. Similar locks are taken for UPDATE or DELETE. > I’ve been looking into the differences between PostgreSQL and Oracle locking behavior, but nothing has > jumped out at me so far. I’m not a dba so hopefully I’m missing something obvious. Any advice would > be greatly appreciated! Thanks. Oracle and PostgreSQL do roughly the same thing in this case. Which version of PostgreSQL is this? Before 9.3 the locks taken on parent tables were unnecessarily strong, so you would encounter problems like the one you describe more easily. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0ac5ad5134f2769ccbaefec73844f8504c4d61... Yours, Laurenz Albe -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-10-20 09:45 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2015-10-19 23:55 Locking: PostgreSQL Vs. Oracle HEMPLEMAN Matthew <matthew.hempleman@alstom.com> 2015-10-20 00:27 ` Scott Mead <scottm@openscg.com> 2015-10-20 09:45 ` Albe Laurenz <laurenz.albe@wien.gv.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox