Skip site navigation (1) Skip section navigation (2)

Re: LOCK TABLE oddness in PLpgSQL function called via JDBC

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dave Harkness <daveh(at)MEconomy(dot)com>
Cc: Barry Lind <barry(at)xythos(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Date: 2001-10-02 22:29:12
Message-ID: 19799.1002061752@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackerspgsql-jdbc
Dave Harkness <daveh(at)MEconomy(dot)com> writes:
> Running in serializable mode, I'm getting a Postgres exception:
>      ERROR:  Can't serialize access due to concurrent update

Well, in that case my theory about it all being one transaction is
wrong; you couldn't get that error without a cross-transaction conflict.

> It seems to me that the table locks grabbed in the PLpgSQL function aren't 
> actually locking the tables. They check to make sure they can *get* the 
> lock, but don't actually hold the lock. Same with the select for update. It 
> makes sure it can get the lock, but still lets others get the same lock.

Once a lock has been grabbed, the *only* way it can be let go is to
end the transaction.  So my new theory is that the JDBC driver is
issuing an auto-commit at points where you're not expecting it.

I'm not familiar enough with the behavior of "setAutoCommit" and friends
to be sure what's happening; but if you turn on query logging in the
server you'll probably see the evidence soon enough.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: manieqDate: 2001-10-02 22:38:44
Subject: RFD: access to remore databases: altername suggestion
Previous:From: Peter EisentrautDate: 2001-10-02 22:14:36
Subject: Re: [HACKERS] CVS changes

pgsql-jdbc by date

Next:From: Dave HarknessDate: 2001-10-02 22:41:13
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via
Previous:From: Tom LaneDate: 2001-10-02 22:16:07
Subject: Re: driver fails to handle strings in query statements properly

pgsql-general by date

Next:From: Dave HarknessDate: 2001-10-02 22:41:13
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via
Previous:From: Dave HarknessDate: 2001-10-02 22:03:56
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group