Dave Harkness <daveh@MEconomy.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