多线程 Java 应用程序中的 SQLite
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10707434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite in a multithreaded java application
提问by jlunavtgrad
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.
我编写了一个 java 应用程序，它偶尔将事件从多个线程记录到 SQLite 数据库中。我注意到我可以通过同时产生少量事件相对容易地触发 SQLite 的“数据库锁定”错误。这促使我编写了一个模拟最坏情况行为的测试程序，我对 SQLite 在这个用例中的表现如此糟糕感到惊讶。下面发布的代码只是将五个记录添加到数据库中，首先依次获取“控制”值。然后同时添加相同的五个记录。
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 代码时的输出：
[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.
顺序插入 5 条记录需要大约 750 毫秒，我预计并发插入需要大致相同的时间。但是你可以看到，如果有 3 秒的超时时间，它们甚至没有完成。我还用 C 编写了一个类似的测试程序，使用 SQLite 的本机库调用，同时插入的完成时间与并发插入大致相同。所以问题出在我的java库上。
Here is the output when I run the C version:
这是我运行 C 版本时的输出：
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/sqlitejdbcand 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?
我用两个不同的 JDBC 驱动程序（http://www.zentus.com/sqlitejdbc和http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC）和 sqlite4java 包装器尝试了这段代码。每次的结果都是相似的。有没有人知道没有这种行为的用于 Java 的 SQLite 库？
回答by thkala
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.
这是核心 SQLite 库的问题- 与任何 Java 包装器无关。SQLite 使用基于文件系统的锁来实现进程间的并发访问同步，因为作为嵌入式数据库，它没有专门的进程（服务器）来调度操作。由于代码中的每个线程都会创建自己的数据库连接，因此它被视为一个单独的进程，通过基于文件的锁进行同步，这比任何其他同步方法都要慢得多。
In addition, SQLite does not support per-row locking (yet?). Essentially the whole database file becomes lockedfor 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.
此外，SQLite 不支持每行锁定（还没有？）。本质上，每个操作都会锁定整个数据库文件。如果你很幸运并且你的文件系统支持字节范围锁，那么多个读者可能同时访问你的数据库，但你不应该假设这种行为。
The core SQLite library by default allows multiple threads to use the same connection concurrentlywith 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.
默认情况下，核心 SQLite 库允许多个线程同时使用同一个连接，没有问题。我认为任何健全的 JDBC 包装器也将允许在 Java 程序中出现这种行为，尽管我还没有真正尝试过。
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 onethread handle all your database operations and serialize DB tasks on your own using Java code...
在所有线程之间共享相同的 JDBC 连接。
由于 SQLite 开发人员似乎认为线程是邪恶的，因此最好让一个线程处理所有数据库操作并使用 Java 代码自行序列化数据库任务......
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.
您可能想看看我的这个老问题- 随着时间的推移，它似乎已经积累了一些关于提高 SQLite 更新性能的技巧。
回答by ksbn
I use the same connection for multiple threads. in addition I had to make db-write methods synchronized, otherwise I still get bussy error
我对多个线程使用相同的连接。另外我必须使db-write方法同步，否则我仍然会遇到bussy错误
