Thread: Runaway Locks

Runaway Locks

From
Kamcheung Sham
Date:
I was connecting to Postgres 8.3 through JDBC. During my unit tests, something when wrong and now leaving with the
followinglocks in the db: 

arc_dev=# select locktype, mode, relname, virtualtransaction, pid from pg_locks l join pg_class c on l.relation =
c.oid;                                            
locktype |       mode       |          relname           | virtualtransaction | pid
----------+------------------+----------------------------+--------------------+------
relation | AccessShareLock  | pg_locks                   | 1/38               | 1816
relation | RowShareLock     | hibernate_sequences        | -1/2091555         |
relation | RowExclusiveLock | hibernate_sequences        | -1/2091555         |
relation | AccessShareLock  | pg_class_oid_index         | 1/38               | 1816
relation | AccessShareLock  | pg_class_relname_nsp_index | 1/38               | 1816
relation | AccessShareLock  | pg_class                   | 1/38               | 1816
(6 rows)arc_dev=#

The locks on 'hibernate_sequences' is causing any update to the locked row to hang. There is currently no running
databaseclient process anymore (as I've restarted by server a few times). 

My question is how do i kill the virtual transaction and have the locks released?

Thanks,
kam

Re: Runaway Locks

From
Bob Lunney
Date:
If you're using Tomcat or some other Java container that does connection management restart it and the lock should go
away.

Bob Lunney

--- On Fri, 4/30/10, Kamcheung Sham <csham@computer.org> wrote:

> From: Kamcheung Sham <csham@computer.org>
> Subject: [ADMIN] Runaway Locks
> To: pgsql-admin@postgresql.org
> Date: Friday, April 30, 2010, 9:03 AM
>
> I was connecting to Postgres 8.3 through JDBC. During my
> unit tests, something when wrong and now leaving with the
> following locks in the db:
>
> arc_dev=# select locktype, mode, relname,
> virtualtransaction, pid from pg_locks l join pg_class c on
> l.relation = c.oid;           
>                
>                
> locktype |       mode   
>    |         
> relname           |
> virtualtransaction | pid 
> ----------+------------------+----------------------------+--------------------+------
>
> relation | AccessShareLock  | pg_locks   
>            
>    | 1/38         
>      | 1816
> relation | RowShareLock     |
> hibernate_sequences        |
> -1/2091555         | 
>    
> relation | RowExclusiveLock | hibernate_sequences 
>       | -1/2091555     
>    |     
> relation | AccessShareLock  | pg_class_oid_index 
>        | 1/38     
>          | 1816
> relation | AccessShareLock  |
> pg_class_relname_nsp_index | 1/38       
>        | 1816
> relation | AccessShareLock  | pg_class   
>            
>    | 1/38         
>      | 1816
> (6 rows)arc_dev=#
>
> The locks on 'hibernate_sequences' is causing any update to
> the locked row to hang. There is currently no running
> database client process anymore (as I've restarted by server
> a few times).
>
> My question is how do i kill the virtual transaction and
> have the locks released?
>
> Thanks,
> kam
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>