Обсуждение: Postgres 8.3.1 RowExclusiveLock With JDBC XA
I am having a problem where row exclusive locks are causing applications and vacuums to hang. I shutdown all applications, but the locks remain. I stop and start postgres, but the locks are still there. A pid is not listed. This problem started showing up after switching to the XA datasource with the Postgres JDBC driver. Is this causing problems?
psql nafis
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation;
SNIP:
--------------------------------------------------------------------------------------------------------------------------------
pid mode virtualtransaction granted locktype database relation
(null) AccessShareLock -1/309809 true relation 16384 29478
(null) AccessShareLock -1/309809 true relation 16384 29497
(null) AccessShareLock -1/317897 true relation 16384 29145
(null) RowShareLock -1/317897 true relation 16384 29239
(null) AccessShareLock -1/317897 true relation 16384 29136
(null) AccessShareLock -1/317931 true relation 16384 29647
(null) RowExclusiveLock -1/317931 true relation 16384 29647
(null) AccessShareLock -1/309809 true relation 16384 29494
(null) RowShareLock -1/317931 true relation 16384 29347
(null) RowShareLock -1/317931 true relation 16384 29225
(null) AccessShareLock -1/317931 true relation 16384 29147
(null) AccessShareLock -1/317931 true relation 16384 29392
(null) RowExclusiveLock -1/317931 true relation 16384 29392
(null) RowShareLock -1/317931 true relation 16384 29239
11209 AccessShareLock 3/7 true relation 16384 10969
(null) AccessShareLock -1/317931 true relation 16384 29136
(null) AccessShareLock -1/317931 true relation 16384 29145
(null) AccessShareLock -1/309809 true relation 16384 29446
(null) RowExclusiveLock -1/309809 true relation 16384 29446
11209 AccessShareLock 3/7 true relation 16384 2663
(null) AccessShareLock -1/309809 true relation 16384 29447
(null) RowExclusiveLock -1/309809 true relation 16384 29447
(null) AccessShareLock -1/309809 true relation 16384 29466
(null) AccessShareLock -1/317897 true relation 16384 29392
(null) RowExclusiveLock -1/317897 true relation 16384 29392
(null) RowShareLock -1/317897 true relation 16384 29225
(null) AccessShareLock -1/317897 true relation 16384 29147
(null) AccessShareLock -1/309809 true relation 16384 29582
"Urciolo, Kevin" <Kevin.Urciolo@ngc.com> writes: > I am having a problem where row exclusive locks are causing applications > and vacuums to hang. I shutdown all applications, but the locks remain. > I stop and start postgres, but the locks are still there. A pid is not > listed. This problem started showing up after switching to the XA > datasource with the Postgres JDBC driver. Is this causing problems? The null PID fields indicate that these locks are being held by uncommitted prepared transactions, so the pg_prepared_xacts view might give you useful info. I concur that this suggests an XA problem. You might have better luck asking about that in the pgsql-jdbc list. regards, tom lane