This discussion is archived
0 Replies Latest reply: Nov 16, 2012 9:24 AM by 974696 RSS

Berkeley DB JDBC multi-thread problem

974696 Newbie
Currently Being Moderated
Hi, I have a test multi thread app having one writer and one reader concurrently. Some simple select query can work, however, some funcy read query cannot work while updating the db. (If there is no writing thread, only read thread, all the read query can work.) Can somebody give me a hint?

Below is the code:

public class readWriteTest {
public readWriteTest() {
}

public static Connection getBerkeleyDBConnection() throws
Exception {
Connection con = null;
String url = "jdbc:sqlite:/C:\\tmp\\pstest.db";
System.out.println(System.getProperty("java.library.path"));
try {
Class.forName("SQLite.JDBCDriver");
con = DriverManager.getConnection(url, "myLogin", "myPassword");
} catch (Exception e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
return con;
}


public static void createTable(){
Connection conn = null;
Statement s = null;
try{
conn = getBerkeleyDBConnection();
s = conn.createStatement();
s.executeUpdate("CREATE TABLE test(id varchar(10), number varchar(10), PRIMARY KEY(id))");
System.out.println("creating table: ");
} catch (Throwable t) {
// do not print out if create table failed.
System.out.println(t);
} finally {
if (s != null) {
try {
s.close();
} catch (Throwable t) {}
}
if (conn != null) {
try {
conn.close();
} catch (Throwable t) {}
}
}

}

public static void update(){
Connection conn = null;
Statement s = null;
PreparedStatement _insertPs;
int n=4;
int m;
try{
conn = getBerkeleyDBConnection();
conn.setAutoCommit(false);
String insertSql = "INSERT INTO test (id,number)VALUES (?,?)";
_insertPs=conn.prepareStatement(insertSql);
while(true){
_insertPs.setString(1, ""+n);
_insertPs.setString(2, ""+1);
_insertPs.addBatch();
m=n+1;
_insertPs.setString(1, ""+m);
_insertPs.setString(2, ""+1);
_insertPs.addBatch();
_insertPs.executeBatch();
conn.commit();
_insertPs.clearBatch();
n=n+2;

}
}catch(Throwable t){
System.err.println(t);

}finally{
if(s!=null)try{s.close();}catch(Throwable t){}
if(_insertPs!=null)try{_insertPs.close();}catch(Throwable t){}
if(conn!=null)try{conn.close();}catch(Throwable t){}
}

}

public static void read(){
Connection conn = null;
Statement s=null;
String sql1="SELECT * FROM test WHERE id LIKE '%4%' ORDER BY id ASC"; //not working
String sql3="SELECT * FROM test"; //working
String sql4="SELECT * FROM test WHERE id LIKE '%4%'"; //working
String sql6="SELECT * FROM test WHERE id='4' ORDER BY id"; //working
ResultSet r;
int rowcount=0;
try{
conn = getBerkeleyDBConnection();
s = conn.createStatement();
while(true){
rowcount=0;
r = s.executeQuery(sql4);
while (r.next()) {
// String id = r.getString(1);
// String number = r.getString(2);
rowcount++;
//System.out.println("id is : " + id + ", number is: " + number);
}
System.out.println("database has total " + rowcount +" rows.");
Thread.sleep(1000L);
}

}catch(BatchUpdateException t){
System.err.println(t);
System.err.println(t.getLocalizedMessage());
t.printStackTrace();
System.err.println(t.getCause());
}catch(Throwable t){
System.err.println(t);
t.printStackTrace();
}finally{
if(s!=null)try{s.close();}catch(Throwable t){}
if(conn!=null)try{conn.close();}catch(Throwable t){}
}

}

public static void main(String args[]){
try{
createTable();

new Thread(new Runnable(){
public void run() {
update();
}
}).start();
new Thread(new Runnable(){
public void run() {                    
read();
}
}).start();
}catch(Exception t){
System.out.println("Error insert DB");
}
}

}

The error messages are:
atch failed
java.sql.BatchUpdateException: batch failed
     at SQLite.JDBC2z.JDBCPreparedStatement.executeBatch(JDBCPreparedStatement.java:488)
     at rx.server.apps.gps.readWriteTest.update(readWriteTest.java:115)
     at rx.server.apps.gps.readWriteTest$1.run(readWriteTest.java:202)
     at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: SQLite.Exception: database table is locked
     at SQLite.JDBC2z.JDBCStatement.executeQuery(JDBCStatement.java:153)
     at SQLite.JDBC2z.JDBCPreparedStatement.executeUpdate(JDBCPreparedStatement.java:153)database has total 40 rows.

     at SQLite.JDBC2z.JDBCPreparedStatement.executeBatch(JDBCPreparedStatement.java:479)
     ... 3 more
Caused by: SQLite.Exception: database table is locked
     at SQLite.Database._exec(Native Method)
     at SQLite.Database.exec(Database.java:210)
     at SQLite.JDBC2z.DatabaseX.exec(JDBCConnection.java:615)
     at SQLite.JDBC2z.JDBCStatement.executeQuery(JDBCStatement.java:124)
     ... 5 more


Can someone help me on this?

Thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points