I have written a java application that sporadically logs events to an SQLite database from multiple threads. I've noticed that I can trigger SQLite's "Database Locked" errors relatively easily by spawning a small number of events at the same time. This drove me to write a test program that mimics the worst case behavior and I was surprised by how poorly it seems that SQLite performs in this use case. The code posted below simply adds five records to a database, first sequentially to get "control" values. Then the same five records are added concurrently.
import java.sql.*;
public class Main {
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people");
stat.executeUpdate("create table people (name, occupation)");
conn.close();
SqlTask tasks[] = {
new SqlTask("Gandhi", "politics"),
new SqlTask("Turing", "computers"),
new SqlTask("Picaso", "artist"),
new SqlTask("shakespeare", "writer"),
new SqlTask("tesla", "inventor"),
};
System.out.println("Sequential DB access:");
Thread threads[] = new Thread[tasks.length];
for(int i = 0; i < tasks.length; i++)
threads[i] = new Thread(tasks[i]);
for(int i = 0; i < tasks.length; i++) {
threads[i].start();
threads[i].join();
}
System.out.println("Concurrent DB access:");
for(int i = 0; i < tasks.length; i++)
threads[i] = new Thread(tasks[i]);
for(int i = 0; i < tasks.length; i++)
threads[i].start();
for(int i = 0; i < tasks.length; i++)
threads[i].join();
}
private static class SqlTask implements Runnable {
String name, occupation;
public SqlTask(String name, String occupation) {
this.name = name;
this.occupation = occupation;
}
public void run() {
Connection conn = null;
PreparedStatement prep = null;
long startTime = System.currentTimeMillis();
try {
try {
conn = DriverManager.getConnection("jdbc:sqlite:test.db");
prep = conn.prepareStatement("insert into people values (?, ?)");
prep.setString(1, name);
prep.setString(2, occupation);
prep.executeUpdate();
long duration = System.currentTimeMillis() - startTime;
System.out.println(" SQL Insert completed: " + duration);
}
finally {
if (prep != null) prep.close();
if (conn != null) conn.close();
}
}
catch(SQLException e) {
long duration = System.currentTimeMillis() - startTime;
System.out.print(" SQL Insert failed: " + duration);
System.out.println(" SQLException: " + e);
}
}
}
}
Here is the output when I run this java code:
[java] Sequential DB access:
[java] SQL Insert completed: 132
[java] SQL Insert completed: 133
[java] SQL Insert completed: 151
[java] SQL Insert completed: 134
[java] SQL Insert completed: 125
[java] Concurrent DB access:
[java] SQL Insert completed: 116
[java] SQL Insert completed: 1117
[java] SQL Insert completed: 2119
[java] SQL Insert failed: 3001 SQLException: java.sql.SQLException: database locked
[java] SQL Insert completed: 3136
Inserting 5 records sequentially takes about 750 milliseconds, I would expect the concurrent inserts to take roughly the same amount of time. But you can see that given a 3 second timeout they don't even finish. I also wrote a similar test program in C, using SQLite's native library calls and the simultaneous inserts finished in roughly the same time as the concurrent inserts did. So the problem is with my java library.
Here is the output when I run the C version:
Sequential DB access:
SQL Insert completed: 126 milliseconds
SQL Insert completed: 126 milliseconds
SQL Insert completed: 126 milliseconds
SQL Insert completed: 125 milliseconds
SQL Insert completed: 126 milliseconds
Concurrent DB access:
SQL Insert completed: 117 milliseconds
SQL Insert completed: 294 milliseconds
SQL Insert completed: 461 milliseconds
SQL Insert completed: 662 milliseconds
SQL Insert completed: 862 milliseconds
I tried this code with two different JDBC drivers( http://www.zentus.com/sqlitejdbc and http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC), and the sqlite4java wrapper. Each time the results were similar. Does anyone out there know of a SQLite library for java that doesn't have this behavior?