多线程 Java 应用程序中的 SQLite [英] SQLite in a multithreaded java application
问题描述
我编写了一个 java 应用程序，它偶尔会从多个线程将事件记录到 SQLite 数据库中.我注意到我可以通过同时产生少量事件相对容易地触发 SQLite 的数据库锁定"错误.这促使我编写了一个模拟最坏情况行为的测试程序，我对 SQLite 在这个用例中的表现如此糟糕感到惊讶.下面发布的代码只是将五个记录添加到数据库中，首先依次获取控制"值.然后同时添加相同的五个记录.
import java.sql.*;公共课主要{public static void main(String[] args) 抛出异常 {Class.forName("org.sqlite.JDBC");Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");语句 stat = conn.createStatement();stat.executeUpdate("如果有人存在则删除表");stat.executeUpdate("创建表人(姓名，职业)");连接.关闭()；SqlTask 任务[] = {new SqlTask("Gandhi", "politics"),new SqlTask("图灵", "计算机"),new SqlTask("毕加索", "艺术家"),new SqlTask("shakespeare", "writer"),new SqlTask("特斯拉", "发明者"),};System.out.println("顺序数据库访问:");线程线程[] = 新线程[tasks.length];for(int i = 0; i 这是我运行此 java 代码时的输出:
[java] 顺序数据库访问:[java] SQL 插入完成:132[java] SQL 插入完成:133[java] SQL 插入完成:151[java] SQL 插入完成:134[java] SQL 插入完成:125[java] 并发数据库访问:[java] SQL 插入完成:116[java] SQL 插入完成:1117[java] SQL 插入完成:2119[java] SQL 插入失败:3001 SQLException:java.sql.SQLException:数据库被锁定[java] SQL 插入完成:3136按顺序插入 5 条记录大约需要 750 毫秒，我预计并发插入花费的时间大致相同.但是你可以看到，如果有 3 秒的超时时间，它们甚至没有完成.我还用 C 编写了一个类似的测试程序，使用 SQLite 的本机库调用，同时插入的完成时间与并发插入大致相同.所以问题出在我的 java 库上.
这是我运行 C 版本时的输出:
顺序数据库访问:SQL 插入完成:126 毫秒SQL 插入完成:126 毫秒SQL 插入完成:126 毫秒SQL 插入完成:125 毫秒SQL 插入完成:126 毫秒并发数据库访问:SQL 插入完成:117 毫秒SQL 插入完成:294 毫秒SQL 插入完成:461 毫秒SQL 插入完成:662 毫秒SQL 插入完成:862 毫秒我用两个不同的 JDBC 驱动程序尝试了这段代码(http://www.zentus.com/sqlitejdbc 和 http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC) 和 sqlite4java 包装器.每次的结果都是相似的.有没有人知道没有这种行为的用于 Java 的 SQLite 库?
的问题a> - 不使用任何 Java 包装器.SQLite 使用基于文件系统的锁来实现进程间的并发访问同步，因为作为嵌入式数据库，它没有专门的进程(服务器)来调度操作.由于代码中的每个线程都会创建自己的数据库连接，因此它被视为一个单独的进程，通过基于文件的锁进行同步，这比任何其他同步方法都要慢得多.
此外，SQLite 不支持每行锁定(还没有?).对于每个操作，基本上整个数据库文件都被锁定.如果你很幸运并且你的文件系统支持字节范围锁，那么多个读者可能同时访问你的数据库，但你不应该假设这种行为.
核心 SQLite 库默认允许多个线程同时使用同一个连接没有问题.我认为任何健全的 JDBC 包装器也将允许在 Java 程序中出现这种行为，尽管我还没有真正尝试过.
因此您有两种解决方案:
在所有线程之间共享相同的 JDBC 连接.
由于 SQLite 开发人员似乎认为 线程是邪恶的，您让一个线程处理所有数据库操作并使用Java代码自行序列化数据库任务会更好...
您可能想看看我的这个老问题 - 随着时间的推移，它似乎已经积累了一些提高 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);
}
}
}
}
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?
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.
这篇关于多线程 Java 应用程序中的 SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
