Re: Locking

I'm running into a problem where an "AccessShareLock" is not being
released after a select statement unless a connection is closed. This
is leading me to a deadlock issue with deletes. I'm using JDBC to
interact with the database. I'm using postgresQL 8.0. I was under
the impression that resources (does this include locks) are released
when the prepared statement is closed. The code snippet looks like the
following (though it does more prosessing then in this example. Is
there something that I'm missing to release the locks?

String sql = "select * from testtable";

PreparedStatement prest = con.prepareStatement(sql);

ResultSet rs = prest.executeQuery();

rs.next();

rs.close();

prest.close();

con.close();

Doh. Forgot to copy list.

On Fri, Apr 18, 2008 at 8:33 PM, Jan de Visser <jdevisser(at)commsolv(dot)com> wrote:
> On Fri, Apr 18, 2008 at 8:09 PM, Sacauskis, Mike
> <Mike(dot)Sacauskis(at)gdit(dot)com> wrote:
> >
> > I'm running into a problem where an "AccessShareLock" is not being released
> > after a select statement unless a connection is closed. This is leading me
> > to a deadlock issue with deletes. I'm using JDBC to interact with the
> > database. I'm using postgresQL 8.0. I was under the impression that
> > resources (does this include locks) are released when the prepared statement
> > is closed. The code snippet looks like the following (though it does more
> > prosessing then in this example. Is there something that I'm missing to
> > release the locks?
>
> Locks get released when the transaction which holds them commits/rolls back.
>
> I assume you are running with autocommit off. If you want your locks
> to be relinquished, you need to call con.commit() at the time you want
> them relinquished.
>
> Or you can just turn autocommit on. In that case you get more or less
> the behaviour you expected, since every statement will run in its own
> transaction which commits on completion of the statement (which is
> even before you start reading from the resultset).
>
> jan
>

I am having the same issue. Unless we close the connection or do a
connection.commit(), the AccessShareLock is not getting released.
ResultSet.close() and PreparedStatement.close() is not having any effect on this
behavior. Is this the expected behavior ?

vinu <vinu(dot)rm(at)gmail(dot)com> wrote:

> I am having the same issue.

For the benefit of those who don't remember the post:

http://archives.postgresql.org/pgsql-jdbc/2008-04/msg00094.php

> Unless we close the connection or do a connection.commit(), the
> AccessShareLock is not getting released. ResultSet.close() and
> PreparedStatement.close() is not having any effect on this
> behavior. Is this the expected behavior ?

Yes. Did you look at the existing reply?

http://archives.postgresql.org/pgsql-jdbc/2008-04/msg00095.php

-Kevin