Thread: jdbc spec violation for autocommit=true & addbatch/executeBatch

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. 

Forgot some details:

JDK 1.6
postgres driver postgresql-9.0-801.jdbc4.jar
postgres server 9.0.2, on windoze XP 64bit and linux 64 bits.

--- On Tue, 1/18/11, Quartz <quartz12h@yahoo.com> wrote:

> From: Quartz <quartz12h@yahoo.com>
> Subject: [JDBC] jdbc spec violation for autocommit=true & addbatch/executeBatch
> To: pgsql-jdbc@postgresql.org
> Received: Tuesday, January 18, 2011, 12:28 PM
> 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.
> Could have 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
> wrapped by 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
> avoid deadlock risks completely, besides an expensive
> (cpu-wise and design-wise) sort of intended statements to
> produce a predictable 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 entire duration of the 1st instance, showing about
> 60 seconds of duration for the statement on row id=1. This
> is unacceptable concurrency.
>
>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

From a cursory glance at the code, the driver appears to do the right
thing with batches when autocommit is on. Can you provide a
self-contained test case? Or can you set loglevel to DEBUG (loglevel=2
in the connection string), configure logging with
DriverManager.setLogWriter(), and show us the log output of your test
case? Also, the output of "SELECT * FROM pg_locks" when you hit a
deadlock could be helpful.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
msakrejda@truviso.com
www.truviso.com

Can't give the test code for now. But here is the traces and locks.

I have loop from 1..19 and 19..1 instead of 1..20 and 20..1, because I tried mysql and I hoped to have 2 threads on the
samerow, which is easier if they meet in the middle ;-) 

I also have a multiple field PK=(id,t), which I didn't reveal before.
I stdout the batch as I build them.

I hope yahoo is not wrapping line the hard way, because this is going to be unreadable otherwise. I attach txt file,
butthe mailing list may not show it; only the direct recipient would get it. 



======================= Thread 1 =======================

14:27:32.683 (1) PostgreSQL 9.0 JDBC4 (build 801)
14:27:32.683 (1) Trying to establish a protocol version 3 connection to localhost:5432
14:27:32.699 (1)  FE=> StartupPacket(user=appliance, database=foobar, client_encoding=UNICODE, DateStyle=ISO,
extra_float_digits=2)
14:27:32.715 (1)  <=BE AuthenticationReqMD5(salt=78a15df4)
14:27:32.715 (1)  FE=> Password(md5digest=.....)
14:27:32.715 (1)  <=BE AuthenticationOk
14:27:32.730 (1)  <=BE ParameterStatus(application_name = )
14:27:32.730 (1)  <=BE ParameterStatus(client_encoding = UNICODE)
14:27:32.730 (1)  <=BE ParameterStatus(DateStyle = ISO, MDY)
14:27:32.730 (1)  <=BE ParameterStatus(integer_datetimes = on)
14:27:32.730 (1)  <=BE ParameterStatus(IntervalStyle = postgres)
14:27:32.730 (1)  <=BE ParameterStatus(is_superuser = on)
14:27:32.730 (1)  <=BE ParameterStatus(server_encoding = UTF8)
14:27:32.730 (1)  <=BE ParameterStatus(server_version = 9.0.1)
14:27:32.730 (1)  <=BE ParameterStatus(session_authorization = appliance)
14:27:32.730 (1)  <=BE ParameterStatus(standard_conforming_strings = off)
14:27:32.730 (1)  <=BE ParameterStatus(TimeZone = US/Eastern)
14:27:32.730 (1)  <=BE BackendKeyData(pid=4696,ckey=462995738)
14:27:32.730 (1)  <=BE ReadyForQuery(I)
14:27:32.730 (1)     compatible = 9.0
14:27:32.730 (1)     loglevel = 2
14:27:32.730 (1)     prepare threshold = 5
14:27:32.746 (1) batch execute 19 queries,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler@30f7f540,maxRows=0, fetchSize=0, flags=21 
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_1_abcdefghij=if(exists(select * from pg_sleep(3)),
1,0) where id=1 and t=1000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_2_abcdefghij=if(exists(select * from pg_sleep(3)),
2,0) where id=2 and t=2000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_3_abcdefghij=if(exists(select * from pg_sleep(3)),
3,0) where id=3 and t=3000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_4_abcdefghij=if(exists(select * from pg_sleep(3)),
4,0) where id=4 and t=4000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_5_abcdefghij=if(exists(select * from pg_sleep(3)),
5,0) where id=5 and t=5000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_6_abcdefghij=if(exists(select * from pg_sleep(3)),
6,0) where id=6 and t=6000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_7_abcdefghij=if(exists(select * from pg_sleep(3)),
7,0) where id=7 and t=7000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_8_abcdefghij=if(exists(select * from pg_sleep(3)),
8,0) where id=8 and t=8000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_9_abcdefghij=if(exists(select * from pg_sleep(3)),
9,0) where id=9 and t=9000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_10_abcdefghij=if(exists(select * from pg_sleep(3)),
10,0) where id=10 and t=10000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_11_abcdefghij=if(exists(select * from pg_sleep(3)),
11,0) where id=11 and t=11000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_12_abcdefghij=if(exists(select * from pg_sleep(3)),
12,0) where id=12 and t=12000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_13_abcdefghij=if(exists(select * from pg_sleep(3)),
13,0) where id=13 and t=13000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_14_abcdefghij=if(exists(select * from pg_sleep(3)),
14,0) where id=14 and t=14000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_15_abcdefghij=if(exists(select * from pg_sleep(3)),
15,0) where id=15 and t=15000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_16_abcdefghij=if(exists(select * from pg_sleep(3)),
16,0) where id=16 and t=16000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_17_abcdefghij=if(exists(select * from pg_sleep(3)),
17,0) where id=17 and t=17000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_18_abcdefghij=if(exists(select * from pg_sleep(3)),
18,0) where id=18 and t=18000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Parse(stmt=null,query="update data set field_19_abcdefghij=if(exists(select * from pg_sleep(3)),
19,0) where id=19 and t=19000",oids={}) 
14:27:32.746 (1)  FE=> Bind(stmt=null,portal=null)
14:27:32.746 (1)  FE=> Describe(portal=null)
14:27:32.746 (1)  FE=> Execute(portal=null,limit=1)
14:27:32.746 (1)  FE=> Sync
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ParseComplete [null]
14:28:44.351 (1)  <=BE BindComplete [null]
14:28:44.351 (1)  <=BE NoData
14:28:44.351 (1)  <=BE CommandStatus(UPDATE 1)
14:28:44.351 (1)  <=BE ReadyForQuery(I)
14:28:44.351 (1)  FE=> Terminate


======================= Thread 2 =======================

14:27:35.043 (1) PostgreSQL 9.0 JDBC4 (build 801)
14:27:35.043 (1) Trying to establish a protocol version 3 connection to localhost:5432
14:27:35.074 (1)  FE=> StartupPacket(user=appliance, database=foobar, client_encoding=UNICODE, DateStyle=ISO,
extra_float_digits=2)
14:27:35.074 (1)  <=BE AuthenticationReqMD5(salt=962d1ce9)
14:27:35.090 (1)  FE=> Password(md5digest=.....)
14:27:35.090 (1)  <=BE AuthenticationOk
14:27:35.090 (1)  <=BE ParameterStatus(application_name = )
14:27:35.090 (1)  <=BE ParameterStatus(client_encoding = UNICODE)
14:27:35.090 (1)  <=BE ParameterStatus(DateStyle = ISO, MDY)
14:27:35.090 (1)  <=BE ParameterStatus(integer_datetimes = on)
14:27:35.105 (1)  <=BE ParameterStatus(IntervalStyle = postgres)
14:27:35.105 (1)  <=BE ParameterStatus(is_superuser = on)
14:27:35.105 (1)  <=BE ParameterStatus(server_encoding = UTF8)
14:27:35.105 (1)  <=BE ParameterStatus(server_version = 9.0.1)
14:27:35.105 (1)  <=BE ParameterStatus(session_authorization = appliance)
14:27:35.105 (1)  <=BE ParameterStatus(standard_conforming_strings = off)
14:27:35.105 (1)  <=BE ParameterStatus(TimeZone = US/Eastern)
14:27:35.105 (1)  <=BE BackendKeyData(pid=3504,ckey=523714351)
14:27:35.105 (1)  <=BE ReadyForQuery(I)
14:27:35.105 (1)     compatible = 9.0
14:27:35.105 (1)     loglevel = 2
14:27:35.105 (1)     prepare threshold = 5
14:27:35.121 (1) batch execute 19 queries,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler@5dccce3c,maxRows=0, fetchSize=0, flags=21 
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_19_abcdefghij=if(exists(select * from pg_sleep(3)),
19,0) where id=19 and t=19000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_18_abcdefghij=if(exists(select * from pg_sleep(3)),
18,0) where id=18 and t=18000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_17_abcdefghij=if(exists(select * from pg_sleep(3)),
17,0) where id=17 and t=17000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_16_abcdefghij=if(exists(select * from pg_sleep(3)),
16,0) where id=16 and t=16000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_15_abcdefghij=if(exists(select * from pg_sleep(3)),
15,0) where id=15 and t=15000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_14_abcdefghij=if(exists(select * from pg_sleep(3)),
14,0) where id=14 and t=14000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_13_abcdefghij=if(exists(select * from pg_sleep(3)),
13,0) where id=13 and t=13000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_12_abcdefghij=if(exists(select * from pg_sleep(3)),
12,0) where id=12 and t=12000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_11_abcdefghij=if(exists(select * from pg_sleep(3)),
11,0) where id=11 and t=11000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_10_abcdefghij=if(exists(select * from pg_sleep(3)),
10,0) where id=10 and t=10000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_9_abcdefghij=if(exists(select * from pg_sleep(3)),
9,0) where id=9 and t=9000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_8_abcdefghij=if(exists(select * from pg_sleep(3)),
8,0) where id=8 and t=8000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_7_abcdefghij=if(exists(select * from pg_sleep(3)),
7,0) where id=7 and t=7000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_6_abcdefghij=if(exists(select * from pg_sleep(3)),
6,0) where id=6 and t=6000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_5_abcdefghij=if(exists(select * from pg_sleep(3)),
5,0) where id=5 and t=5000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_4_abcdefghij=if(exists(select * from pg_sleep(3)),
4,0) where id=4 and t=4000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_3_abcdefghij=if(exists(select * from pg_sleep(3)),
3,0) where id=3 and t=3000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_2_abcdefghij=if(exists(select * from pg_sleep(3)),
2,0) where id=2 and t=2000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Parse(stmt=null,query="update data set field_1_abcdefghij=if(exists(select * from pg_sleep(3)),
1,0) where id=1 and t=1000",oids={}) 
14:27:35.121 (1)  FE=> Bind(stmt=null,portal=null)
14:27:35.121 (1)  FE=> Describe(portal=null)
14:27:35.121 (1)  FE=> Execute(portal=null,limit=1)
14:27:35.121 (1)  FE=> Sync
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE CommandStatus(UPDATE 1)
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE CommandStatus(UPDATE 1)
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE CommandStatus(UPDATE 1)
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE CommandStatus(UPDATE 1)
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE CommandStatus(UPDATE 1)
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE CommandStatus(UPDATE 1)
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE CommandStatus(UPDATE 1)
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE CommandStatus(UPDATE 1)
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE CommandStatus(UPDATE 1)
14:28:20.165 (1)  <=BE ParseComplete [null]
14:28:20.165 (1)  <=BE BindComplete [null]
14:28:20.165 (1)  <=BE NoData
14:28:20.165 (1)  <=BE ErrorMessage(ERROR: deadlock detected
  Detail: Process 3504 waits for ShareLock on transaction 388016; blocked by process 4696.
Process 4696 waits for ShareLock on transaction 388017; blocked by process 3504.
  Hint: See server log for query details.)
org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 3504 waits for ShareLock on transaction 388016; blocked by process 4696.
Process 4696 waits for ShareLock on transaction 388017; blocked by process 3504.
  Hint: See server log for query details.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2737)
    at stress.TestSQLConcurrentBatches.queryIt(TestSQLConcurrentBatches.java:136)
    at stress.TestSQLConcurrentBatches.main(TestSQLConcurrentBatches.java:90)
SQLException: SQLState(40P01)
java.sql.BatchUpdateException: Batch entry 9 update data set field_10_abcdefghij=if(exists(select * from pg_sleep(3)),
10,0) where id=10 and t=10000 was aborted.  Call getNextException to see the cause. 
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2598)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2737)
    at stress.TestSQLConcurrentBatches.queryIt(TestSQLConcurrentBatches.java:136)
    at stress.TestSQLConcurrentBatches.main(TestSQLConcurrentBatches.java:90)
SQLException: SQLState(40P01)
14:28:20.165 (1)  <=BE ReadyForQuery(I)
java.sql.BatchUpdateException: Batch entry 9 update data set field_10_abcdefghij=if(exists(select * from pg_sleep(3)),
10,0) where id=10 and t=10000 was aborted.  Call getNextException to see the cause. 
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2598)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2737)
    at stress.TestSQLConcurrentBatches.queryIt(TestSQLConcurrentBatches.java:136)
    at stress.TestSQLConcurrentBatches.main(TestSQLConcurrentBatches.java:90)
org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 3504 waits for ShareLock on transaction 388016; blocked by process 4696.
Process 4696 waits for ShareLock on transaction 388017; blocked by process 3504.
  Hint: See server log for query details.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2737)
    at stress.TestSQLConcurrentBatches.queryIt(TestSQLConcurrentBatches.java:136)
    at stress.TestSQLConcurrentBatches.main(TestSQLConcurrentBatches.java:90)
14:28:20.165 (1)  FE=> Terminate




------------------


select * from pg_locks;


locktype    database    relation    page    tuple    virtualxid    transactionid    classid    objid    objsubid
virtualtransaction   pid    mode    granted 
relation    75847    77776                                6/6    3504    RowExclusiveLock    true
tuple    75847    77776    8740    5                        6/6    3504    ExclusiveLock    true
relation    75847    77805                                6/6    3504    RowExclusiveLock    true
transactionid                        388016                6/6    3504    ShareLock    false
relation    75847    77776                                2/779    4696    RowExclusiveLock    true
transactionid                        388016                2/779    4696    ExclusiveLock    true
tuple    75847    77776    8740    6                        2/779    4696    ExclusiveLock    true
relation    75847    77805                                2/779    4696    RowExclusiveLock    true
virtualxid                    2/779                    2/779    4696    ExclusiveLock    true
virtualxid                    3/254                    3/254    3740    ExclusiveLock    true
transactionid                        388017                2/779    4696    ShareLock    false
relation    75847    10985                                3/254    3740    AccessShareLock    true
virtualxid                    6/6                    6/6    3504    ExclusiveLock    true
transactionid                        388017                6/6    3504    ExclusiveLock    true




-----------------------


SELECT l.locktype, c.relname, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid,
    l.virtualtransaction, l.mode, l.granted,
    waiting, EXTRACT(epoch FROM (NOW() - query_start)) AS duration, procpid, substr(current_query,1,50), usename
 FROM pg_locks AS l
  LEFT JOIN pg_stat_activity  as a ON pid = procpid
  LEFT JOIN pg_class AS c ON l.relation = c.oid
 WHERE procpid != pg_backend_pid ()
 ORDER BY procpid;



locktype    relname    page    tuple    virtualxid    transactionid    classid    objid    objsubid
virtualtransaction   mode    granted    waiting    duration    procpid    substr    usename 
relation    data                                6/6    RowExclusiveLock    true    true    6.86    3504    update data
setfield_10_abcdefghij=if(exists(sele    appliance 
tuple    data    8740    5                        6/6    ExclusiveLock    true    true    6.86    3504    update data
setfield_10_abcdefghij=if(exists(sele    appliance 
relation    data_pkey                                6/6    RowExclusiveLock    true    true    6.86    3504    update
dataset field_10_abcdefghij=if(exists(sele    appliance 
transactionid                    388016                6/6    ShareLock    false    true    6.86    3504    update data
setfield_10_abcdefghij=if(exists(sele    appliance 
virtualxid                6/6                    6/6    ExclusiveLock    true    true    6.86    3504    update data
setfield_10_abcdefghij=if(exists(sele    appliance 
transactionid                    388017                6/6    ExclusiveLock    true    true    6.86    3504    update
dataset field_10_abcdefghij=if(exists(sele    appliance 
tuple    data    8740    6                        2/779    ExclusiveLock    true    true    6.23    4696    update data
setfield_11_abcdefghij=if(exists(sele    appliance 
relation    data_pkey                                2/779    RowExclusiveLock    true    true    6.23    4696
updatedata set field_11_abcdefghij=if(exists(sele    appliance 
virtualxid                2/779                    2/779    ExclusiveLock    true    true    6.23    4696    update
dataset field_11_abcdefghij=if(exists(sele    appliance 
transactionid                    388017                2/779    ShareLock    false    true    6.23    4696    update
dataset field_11_abcdefghij=if(exists(sele    appliance 
relation    data                                2/779    RowExclusiveLock    true    true    6.23    4696    update
dataset field_11_abcdefghij=if(exists(sele    appliance 
transactionid                    388016                2/779    ExclusiveLock    true    true    6.23    4696    update
dataset field_11_abcdefghij=if(exists(sele    appliance 







--- On Tue, 1/18/11, Maciek Sakrejda <msakrejda@truviso.com> wrote:

> From: Maciek Sakrejda <msakrejda@truviso.com>
> Subject: Re: [JDBC] jdbc spec violation for autocommit=true & addbatch/executeBatch
> To: "Quartz" <quartz12h@yahoo.com>
> Cc: pgsql-jdbc@postgresql.org
> Received: Tuesday, January 18, 2011, 1:20 PM
> From a cursory glance at the code,
> the driver appears to do the right
> thing with batches when autocommit is on. Can you provide
> a
> self-contained test case? Or can you set loglevel to DEBUG
> (loglevel=2
> in the connection string), configure logging with
> DriverManager.setLogWriter(), and show us the log output of
> your test
> case? Also, the output of "SELECT * FROM pg_locks" when you
> hit a
> deadlock could be helpful.
>
> ---
> Maciek Sakrejda | System Architect | Truviso
>
> 1065 E. Hillsdale Blvd., Suite 215
> Foster City, CA 94404
> (650) 242-3500 Main
> msakrejda@truviso.com
> www.truviso.com
>

Quartz wrote:

> 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."

It's worth noting that the javadoc changes without warning between
releases - there have been many "clarifications" over the years that are
actually unannounced behavioral changes - have you checked historical
javadoc to see what it says? Maybe we implemented against a spec that
said something different.

That said - yes, all statements within addBatch() are run in a single
transaction. (Autocommit is triggered by the final Sync message, which
is only sent at the end of the batch)

A related gotcha is that if you execute a multiple statement query (i.e.
execute("select 1; select 2; select 3") then, again, there is only one
enclosing autocommit transaction. (This was deliberate, as it mirrored
the behavior of protocol-version-2 drivers back in the 7.x days)

I'm not sure if we can implement this according to the current spec
without losing any performance benefit of addBatch()/executeBatch() -
we'd have to send a Sync after every individual query. As a workaround I
suggest you just run the queries individually, not in a batch - there
won't be much performance difference. (And if you really don't care
about ordering or atomicity as you imply, you could parallelize it
across multiple connections if latency is a problem)

Oliver

--- On Tue, 1/18/11, Oliver Jowett <oliver@opencloud.com> wrote:

> From: Oliver Jowett <oliver@opencloud.com>
> Subject: Re: [JDBC] jdbc spec violation for autocommit=true & addbatch/executeBatch
> To: "Quartz" <quartz12h@yahoo.com>
> Cc: pgsql-jdbc@postgresql.org
> Received: Tuesday, January 18, 2011, 4:31 PM
> Quartz wrote:
>
> > 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."
>
> It's worth noting that the javadoc changes without warning
> between releases - there have been many "clarifications"
> over the years that are actually unannounced behavioral
> changes - have you checked historical javadoc to see what it
> says? Maybe we implemented against a spec that said
> something different.
>
> That said - yes, all statements within addBatch() are run
> in a single transaction. (Autocommit is triggered by the
> final Sync message, which is only sent at the end of the
> batch)
>
> A related gotcha is that if you execute a multiple
> statement query (i.e. execute("select 1; select 2; select
> 3") then, again, there is only one enclosing autocommit
> transaction. (This was deliberate, as it mirrored the
> behavior of protocol-version-2 drivers back in the 7.x
> days)
>
> I'm not sure if we can implement this according to the
> current spec without losing any performance benefit of
> addBatch()/executeBatch() - we'd have to send a Sync after
> every individual query. As a workaround I suggest you just
> run the queries individually, not in a batch - there won't
> be much performance difference. (And if you really don't
> care about ordering or atomicity as you imply, you could
> parallelize it across multiple connections if latency is a
> problem)
>
> Oliver
>

This is very sad. The batch never mean transaction. It happens to be the supporting fixture for the transaction, but
alsoto avoid roundtrips with connection pools and such overhead when not using transaction as autocommit=true suggest. 

Is is expected that your said 'sync' at every statement would be less performant than a transaction, but it is still
moreperformant than separated statements/connections, especially with prepared statements. 

IMHO, you should avoid breaking the spec even if it means some performance loss, which "might"* be recovered another
time.The main issue here is to impose a transaction that MAY fail when the calling code isn't designed to handle
retriesbecause it didn't need to in the first place. 

*For attempting to preserve performance, I guess the sync is too aggressive, so there should be an agreement to make a
lighternew protocol directive to denote the intent of performing the statement alone rather than in a transaction,
althoughthe server can buffer these statement. Some kind of 'enqueue' directive, sort of. 

Quartz wrote:

> Is is expected that your said 'sync' at every statement would be less performant than a transaction, but it is still
moreperformant than separated statements/connections, especially with prepared statements. 

I don't know without implementing and benchmarking it (which,
unfortunately, I have no time to do).

> IMHO, you should avoid breaking the spec even if it means some performance loss, which "might"* be recovered another
time.The main issue here is to impose a transaction that MAY fail when the calling code isn't designed to handle
retriesbecause it didn't need to in the first place. 

We certainly do avoid breaking the spec whereever possible, but please
remember that much of the driver's implementation dates back to the days
of JDBC2, and back then the JDBC spec was a terrible spec in terms of
precisely describing the required behaviour. (It's somewhat better now,
but still leaves a lot to be desired)

Your particular case is a bit of an edge case too - I don't remember
hearing of anyone else using batch updates with autocommit=on, from
memory. Typically you are using batch updates because you have a lot of
data to stream in, and if you have a lot of data to stream in you want
it in one big transaction to avoid repeated transaction costs - so
having autocommit=on somewhat defeats the purpose..

> *For attempting to preserve performance, I guess the sync is too aggressive, so there should be an agreement to make
alighter new protocol directive to denote the intent of performing the statement alone rather than in a transaction,
althoughthe server can buffer these statement. Some kind of 'enqueue' directive, sort of. 

I think it's quite unlikely that the FE/BE protocol is going to change
merely because of a quirk in the JDBC spec that can be handled by the
driver. That protocol has been stable for a number of years now - dating
back to 7.4, IIRC.

You might want to read up on the query protocol at
http://www.postgresql.org/docs/9.0/static/protocol-flow.html as a
starting point. (The JDBC driver uses the "extended query" flow)

The simplest fix I can see is to just have a special case at the
Statement level that falls back to executing each batch entry
individually when autocommit=on. That would match the spec better and be
no worse than having the caller execute statements individually.

Wiring different logic into the query executor itself to handle this
case would be more complicated, and would only be a win if the server
roundtrip latency was a bigger factor than the cost of transaction
setup/teardown (which pretty much means "server on the other end of a WAN")

Oliver

Quartz, 18.01.2011 22:55:
> This is very sad. The batch never mean transaction. It happens to be
> the supporting fixture for the transaction, but also to avoid
> roundtrips with connection pools and such overhead when not using
> transaction as autocommit=true suggest.

The JDBC Specification (jdbc-4.0-fr-spec.pdf, Chapter 14.1.1) has a little note on this topic:

"auto-commit should always be turned off when batch updates are done.
The commit behavior of executeBatch is always implementation-defined when an error
occurs and auto-commit is true"


I cannot find any clear definition whether the batch should be treated as a single transaction or multiple transactions
dependingon the state of autocommit. 

There is one hint though:

Chapter 10.1

"The Connection attribute auto-commit specifies when to end transactions. Enabling
auto-commit causes a transaction commit after each individual SQL statement as
soon as that statement is complete. The point at which a statement is considered to
be “complete” depends on the type of SQL statement as well as what the application
does after executing it:"

   For Data Manipulation Language (DML) statements such as Insert, Update,
   Delete, and DDL statements, the statement is complete as soon as it has finished
   executing.

   ....

   For CallableStatement objects or for statements that return multiple results,
   the statement is complete when all of the associated result sets have been closed,
   and all update counts and output parameters have been retrieved.


I think the second item (actually the third because I left out one) is interesting. A prepared statement that is using
batchesmight be considered as one that returns multiple results (as it can return multiple update counts). 

So according to that note, I would actually say that batched statements _can_ be treated as a single transaction.

Pretty vague though I have to admit.

Regards
Thomas

18.01.11 23:31, Oliver Jowett написав(ла):
>
> I'm not sure if we can implement this according to the current spec
> without losing any performance benefit of addBatch()/executeBatch() -
> we'd have to send a Sync after every individual query.
But is roundtrip needed for each sync? Can't we send all the data + as
much syncs as needed and then collect responses. The only thing that
bothers me is famous bug when receive and then send buffer overflows.
> As a workaround I suggest you just run the queries individually, not
> in a batch - there won't be much performance difference. (And if you
> really don't care about ordering or atomicity as you imply, you could
> parallelize it across multiple connections if latency is a problem)

How about addBatch("commit") after each addBatch("update")? This should
work in very similar way to sending Sync after each query.

Best regards, Vitalii Tymchyshyn

>> I'm not sure if we can implement this according to the current spec
>> without losing any performance benefit of addBatch()/executeBatch() - we'd
>> have to send a Sync after every individual query.
>
> But is roundtrip needed for each sync? Can't we send all the data + as much
> syncs as needed and then collect responses. The only thing that bothers me
> is famous bug when receive and then send buffer overflows.

I was thinking about something like that, too. It sort of abuses the
intent (or at least half the intent) of the Sync message, but maybe
that's acceptable.

> How about addBatch("commit") after each addBatch("update")? This should work
> in very similar way to sending Sync after each query.

This could potentially be used under the covers as a solution (instead
of just a workaround), no? I.e., when a user does an addBatch("some
SQL"), we could prepend that with a BEGIN, and follow it with a COMMIT
under the covers. If BEGIN / COMMIT have to follow the extended query
protocol, that's going to add some overhead, since each one will need
a Parse, Bind, Describe, Execute, and Close message. Conceptually,
this is similar to the async-Sync you suggested above. Unfortunately,
this is probably even uglier.

As Oliver mentioned, though, this is all pointless if the network
latency is negligible compared to the transaction overhead of the
individual statements, which is typically likely.
---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com