On Mon, 21 Nov 2005, Vit Timchishin wrote:
> I am not using one connection and at the time of block there is only one
> active query (that is locked) at the whole database (in my test case).
> But for one transaction it may be used by different java threads (e.g.
> main thread and finalizer) and it seems that this is producing problems
> because exclusive lock is held after statement have finished. It is
> possible that I am still having open resultsets (did not check), but
> they are all forward only and not updateable.
>
Locks are always held until transaction commit, not the end of an
individual statement. So, while you may only have one statement executing
you have more than one transaction in progress and this is causing your
deadlocks. Consider a table with a primary key:
CREATE TABLE t(a int primary key);
Connection 1:
BEGIN;
INSERT INTO t VALUES (1);
Connection 2:
BEGIN;
INSERT INTO t VALUES (1);
Connection 2 must wait for connection 1 to either commit or rollback
before it knows whether it can legally insert its value. This is true
even if connection 1 performed its insert a week ago, the transaction is
still in doubt even if the statement has completed running.
Kris Jurka