JDBC transactions in multithreaded programs.

[singam...@my-dejanews.com]

I am having problems with transactions in multiple threads interfering with
each other. Each thread uses its own database connection. I have included a
sample test program to illustrate the problem

import java.sql.*;

public class MultiThreadDbTest {
public MultiThreadDbTest() {
// Make two connections
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn1 =
DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:", "scott",
"tiger");
conn2 =
DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:", "scott",
"tiger");
// Class.forName("symantec.itools.db.jdbc.Driver");
// conn1 =
DriverManager.getConnection("jdbc:dbaw://localhost:8889/ODBC_DSN/ORCL/ORCL",
"scott", "tiger");
// conn2 =
DriverManager.getConnection("jdbc:dbaw://localhost:8889/ODBC_DSN/ORCL/ORCL",
"scott", "tiger");
stmt1 = conn1.createStatement();
stmt2 = conn2.createStatement();

ResultSet rs = stmt1.executeQuery("select sal from emp where ename =
'SMITH'"); if (rs.next()) { System.out.println("Salary of Smith is
"+rs.getInt(1)); } rs.close();

thread1 = new Thread1();
thread2 = new Thread2();
thread1.start();
thread2.start();
thread1.join();
thread2.join();

rs = stmt1.executeQuery("select sal from emp where ename =
'SMITH'");
if (rs.next()) {
System.out.println("Salary of Smith is "+rs.getInt(1));
}
rs.close();
stmt1.close();
stmt2.close();
conn1.close();
conn2.close();
} catch (Exception e) {
System.out.println ("Error "+e);
System.exit(1);
}
}

class Thread1 extends Thread { public void run() { for (int i=0; i < 10;
i++) { try { conn1.setAutoCommit(false); stmt1.executeUpdate("update emp
set sal = sal+1 where ename = 'SMITH'"); try { sleep(1000); } catch
(InterruptedException j) {} conn1.rollback(); } catch (Exception e) {
System.out.println("Error Thread1 "+e); } } } }

class Thread2 extends Thread {
public void run() {
for (int i=0; i < 10; i++) {
try {
conn2.setAutoCommit(false);
ResultSet rs = stmt2.executeQuery("select sysdate from
dual");
if (rs.next()) {
Timestamp ct = rs.getTimestamp(1);
}
try {
sleep(1000);
} catch (InterruptedException j) {}
conn2.commit();
} catch (Exception e) {
System.out.println("Error Thread2 "+e);
}
}
}
}

public static void main(String[] args) {
MultiThreadDbTest tst = new MultiThreadDbTest();
System.exit(0);
}

Connection conn1;
Connection conn2;
Statement stmt1;
Statement stmt2;
Thread1 thread1;
Thread2 thread2;
}

The test program opens two connections and starts two threads. Thread1
performs an update, but always rollsback the transaction. Thread2 selects the
current system time and commits after each select. The database should be
left unchanged after running the program, but a typical run produces

Salary of Smith is 800
Salary of Smith is 808

This shows some of the updates (not all) got committed. Are there any
guidelines on using multiple connections? Is this a problem with the JDBC
driver?

I would appreciate any help. Could you send any replies to
ssu...@hotmail.com. I don't have access to news group servers.

Thanks,
Singam

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[Bernard Van Haecke]

Hi,

singam...@my-dejanews.com wrote:
>
> I am having problems with transactions in multiple threads interfering with
> each other. Each thread uses its own database connection. I have included a
> sample test program to illustrate the problem

[...]

> This shows some of the updates (not all) got committed. Are there any
> guidelines on using multiple connections? Is this a problem with the JDBC
> driver?

Yes, definitely. It is officially multithread-safe? What you can also
do is
set-up some kind of semaphore yourself to lock a resource such as a
connection
(a unique connection, in this case) or put some methods as
'synchronized'.

I don't see where do you set the transaction isolation level in your
code.
Choosing the appropriate level, i.e. locking strategy, may help...

Bernard
--
--------------------------------------------------------------------------------
JDBC: Java Database Connectivity, by Bernard Van Haecke, IDG Books
Worldwide,
ISBN: 0-7645-3144-1, 400pp, CD-ROM incl. Also available in Japanese and
Russian.

Incl: - intro to Java and SQL - tutorial - database integration
techniques
- complete JDBC API ref - dynamic SQL - stored procedures -
transactions mgt
- security - load balancing, failover - BLOBs and multimedia - 3-tiers
with
RMI - powerful examples - graphical database explorer - JDBC vendor
listing...

For more info: http://www.geocities.com/RodeoDrive/1620/books/jdbc.html
--------------------------------------------------------------------------------