多线程Java应用程序中的SQLite [英] SQLite in a multithreaded java application

查看：144 发布时间：2019/9/2 12:17:10 java jdbc sqlite locked

本文介绍了多线程Java应用程序中的SQLite的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我已经编写了一个Java应用程序，该应用程序偶尔将事件从多个线程记录到SQLite数据库中.我注意到，通过同时生成少量事件，可以相对轻松地触发SQLite的数据库锁定"错误.这驱使我编写了一个模仿最坏情况行为的测试程序，令我感到惊讶的是，在此用例中，SQLite的执行情况似乎很差.下面发布的代码仅将五个记录添加到数据库中，首先按顺序获取控件"值.然后，同时添加相同的五个记录.

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);
         }
      }
   }
}

这是我运行此Java代码时的输出:

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

依次插入5条记录大约需要750毫秒，我希望并发插入大约花费相同的时间.但是您可以看到，在3秒钟的超时时间内，它们甚至还没有完成.我还使用C语言编写了一个类似的测试程序，使用SQLite的本机库调用，并且与并发插入大致相同的时间完成了同时插入.所以问题出在我的Java库上.

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.

这是我运行C版本时的输出:

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

我使用两个不同的JDBC驱动程序尝试了此代码( http://www.zentus.com/sqlitejdbc和 http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC)和sqlite4java包装器.每次结果都是相似的.有没有人知道没有这种行为的Java的SQLite库?

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?

多线程Java应用程序中的SQLite [英] SQLite in a multithreaded java application

问题描述

推荐答案

相关文章

Java开发最新文章

热门教程

热门工具

登录关闭

多线程Java应用程序中的SQLite [英] SQLite in a multithreaded java application

问题描述

推荐答案

相关文章

Java开发最新文章

热门教程

热门工具

登录 关闭

登录关闭