Re: LOCK TABLE oddness in PLpgSQL function called via 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