Thread: 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