SQLite in a multithreaded java application

Question

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?

Answer 1

This is an issue with the core SQLite library - not with any Java wrapper. SQLite uses filesystem-based locks for concurrent access synchronization among processes, since as an embedded database it does not have a dedicated process (server) to schedule operations. Since each thread in your code creates its own connection to the database, it is treated as a separate process, with synchronization happening via file-based locks, which are significantly slower than any other synchronization method.

In addition, SQLite does not support per-row locking (yet?). Essentially the whole database file becomes locked for each operation. If you are lucky and your filesystem supports byte-range locks, it may be possible for multiple readers to access your database simultaneously, but you should not assume that kind of behavior.

The core SQLite library by default allows multiple threads to use the same connection concurrently with no problem. I presume that any sane JDBC wrapper will allow that behavior in Java programs as well, although I have not actually tried it.

Therefore you have two solutions:

• Share the same JDBC connection among all threads.

• Since the SQLite developers seem to think that threads are evil, you would be better off having one thread handle all your database operations and serialize DB tasks on your own using Java code...

You might want to have a look at this old question of mine - it seems to have accumulated several tips on improving update performance in SQLite over time.