SEO

#1 (**permalink**) 04-19-2008, 06:20 AM

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();

#2 (**permalink**) 04-19-2008, 06:20 AM

Doh. Forgot to copy list.

On Fri, Apr 18, 2008 at 8:33 PM, Jan de Visser <jdevisser@commsolv.com> wrote:
> On Fri, Apr 18, 2008 at 8:09 PM, Sacauskis, Mike
> <Mike.Sacauskis@gdit.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
>

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Locking