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
>