File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Strange JDBC transaction problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Strange JDBC transaction problem" Watch "Strange JDBC transaction problem" New topic

Strange JDBC transaction problem

Tom Chen
Ranch Hand

Joined: Dec 13, 2000
Posts: 46
Two JDBC transcations are runned concurrently. They update the same table, but different records. Since they update different records, they should not affect each other. But the result shows that the 2nd transaction cannot continue until the 1st transaction finished.
Isolation level is set to Read Commited. I'm using weblogic 6.1 + db2 7.1
Transaction 1
1)transcation begin
2)update record1
3)(let the thread wait for 60 seconds on purpose....)
4)update rec2
5)transcation end
Transaction 2
1)transcation begin
2)update rec3
3)update rec4
4)transcation end
Can anyone explain why would this happened? How to fix this problem?
Thank you

Sun Certified Java Programmer
frankie huang

Joined: Dec 17, 2002
Posts: 18
There are 2 types of LOCK in dbms: table-locked and row-locked. maybe you should consult the db2 documentation.
I don't think these 2 transcation can run concurrently. for example:
the table "table1" like this:
field1 field2
'aa' 12
'bb' 20
'cc' 30
update1 is "update table1 set field2=22 where field1='aa'"
update3 is "update table1 set field1='dd' where field2>=20"
the result of the these 2 transaction is not sure if they run concurrently.
Tom Chen
Ranch Hand

Joined: Dec 13, 2000
Posts: 46
Hi Frankie,
I finally found that it is because Primary Key is not specifed for the table. I think Primary Key is a must for the row level locking.
Thank you very much,
[ January 15, 2003: Message edited by: Tom Chen ]
I agree. Here's the link:
subject: Strange JDBC transaction problem
It's not a secret anymore!