jdbc spec violation for autocommit=true & addbatch/executeBatch
We are moving to postgresql. I have unexpected deadlocks on batches even though I don't use transaction (I use
autocommit=true).
The test program is simple. On a 20+ row table with primary key 'id', one thread does 20 updates added with
statement.addBatch()from id 1 to 20. Meanwhile the other thread does updates from row id 20 down to 1.
To facilitate the contention, I used a "pg_sleep(3)" call inside each update. That gives me time to start both program
(i.e.both threads).
update data set f1=if(exists(select * from pg_sleep(3)), 1, 0) where id=1
update data set f2=if(exists(select * from pg_sleep(3)), 1, 0) where id=2
and so on.
(I just made 20 fields to see the f1, f2 .. in the sql string to distinguish at which statement a connection is at.
Couldhave been the same field all the time.)
(Under mysql 5+:
update data set f1=sleep(3) where id=1
update data set f2=sleep(3) where id=2
and so on.)
In a nutshell, each program should run for about 60 seconds (20x3 seconds).
The results:
Mysql myisam (table locking) never fails, but the 2 programs are interlaced and take nearly 120s as expected.
Mysql innodb without transaction never fails, never deadlock.
Mysql innodb with transaction (autocommit=false & commit()) does deadlock as expected.
Postgres with transaction (autocommit=false & commit()) does deadlock as expected.
Postgres without transaction (autocommit=true) deadlocks UNEXPECTEDLY.
The jdbc javadoc says clearly:
http://download.oracle.com/javase/6/docs/api/java/sql/Connection.html#setAutoCommit%28boolean%29
"If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual
transactions."
This is simply not true for postgresql.
For now I cannot tell if this is a bad driver setting up the connection, of if postgresql server simply cannot be
wrappedby jdbc for this API.
All I can tell is that this is a show stopper for everyone doing simple transaction-less batches, as there is no way to
avoiddeadlock risks completely, besides an expensive (cpu-wise and design-wise) sort of intended statements to produce
apredictable row locking order.
Yet, I ran many threads doing the id 1..20 updates concurrently, and guess what? The 2nd instance was blocked for the
entireduration of the 1st instance, showing about 60 seconds of duration for the statement on row id=1. This is
unacceptableconcurrency.