SQLite in multithreaded Java applications

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];< br /> for(int i = 0; i  threads[i] = new Thread(tasks[i]) ;

 for(int i = 0; i  threads[i].start();
 threads[i].join() ;
 }

 System.out.println("Concurrent DB access:");

 for(int i = 0; i  threads[i] = new Thread(tasks[i]);

 for(int i = 0; i  threads[i] .start();

 for(int i = 0; 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;< br /> 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;< br /> 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;< br /> System.out.print(" SQL Insert failed: "+ duration);
 System.out.println(" SQLException:" + e);
 }
 }
 }
}

[java] Sequential DB acces s:
 [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

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 wrote a Java application that occasionally logs events from multiple threads to a SQLite database. I noticed that I can trigger SQLite’s “database lock” error relatively easily by generating a small number of events at the same time. This led me to write a test program that simulates the worst-case behavior, and my performance in this use case is surprising. The following code simply adds five records to the database, first obtaining the “control” value in sequence. Then add the same five records at the same time.

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];< br /> for(int i = 0; i  threads[i] = new Thread(tasks[i]);< br />
 for(int i = 0; i  threads[i].start();
 threads[i].join();< br /> }

 System.out.println("Concurrent DB access:");

 for(int i = 0; i  threads[i] = new Thread(tasks[i]);

 for(int i = 0; i  threads[i].start ();

 for(int i = 0; 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;< br /> 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;< br /> System.out.print(" SQL Insert failed: "+ duration);
 System.out.println(" SQLException:" + e);
 }
 }
 }
}

This 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

Sequentially insert 5 Recording takes about 750 milliseconds, and I expect concurrent inserts to take about the same time. But you can see that given a timeout of 3 seconds, it didn’t even complete. I also wrote a similar test program in C, using SQLite’s native library call, and completed the simultaneous insertion most of the time. So the problem is my java library.

This is the output when running 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

Me Try to use two different JDBC drivers (http://www.zentus.com/sqlitejdbc and http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC) and the sqlite4java wrapper. The results are similar each time. Does anyone know of java’s SQLite library that does not have this behavior?

This is a core SQLite library problem, not any Java wrapper. SQLite uses file system-based locks for concurrent access synchronization between processes, because as an embedded database, it does not have a dedicated process (server) to schedule operations. Since each thread in the code creates its own connection to the database, it is treated as a separate process, synchronized through file-based locks, which is much slower than any other synchronization method.

In addition, SQLite does not support per-row locking (and?). Basically, the entire database file for each operation becomes locked. If you are lucky, and the file system supports byte range locking, multiple readers may access your database at the same time, but you should not assume this behavior.

The core SQLite library by default allows multiple threads to use the same connection concurrently. I think any sane JDBC wrapper will also allow behavior in Java programs, although I haven’t actually tried it.

So you have two solutions:

>Share the same JDBC connection between all threads.
>Since SQLite developers seem to think threads are evil, you’d better have a thread to handle all database operations, and use Java code to serialize DB tasks by yourself…

You may want to take a look this old question of mine-seems to have accumulated several tips to improve the update performance in SQLite.