难度运行在C＃中的SQLite数据库并发插入 [英] Difficulty running concurrent INSERTS on SQLite database in C#

问题描述

我运行多个线程的每次尝试执行插入一个SQLite数据库其中。每个线程创建它自己的连接到数据库。他们每个人都创建一个命令，打开事务执行一些插入和然后关闭交易。看来第二个线程尝试任何得到以下SQLiteException：数据库文件被锁定。我曾尝试展开从交易刀片以及缩小包含在每个刀片的范围，没有实际效果提交;随后到数据库文件的访问引发相同的异常。

I'm running a number of threads which each attempt to perform INSERTS to one SQLite database. Each thread creates it's own connection to the DB. They each create a command, open a Transaction perform some INSERTS and then close the transaction. It seems that the second thread to attempt anything gets the following SQLiteException: The database file is locked. I have tried unwrapping the INSERTS from the transaction as well as narrowing the scope of INSERTS contained within each commit with no real effect; subsequent access to the db file raises the same exception.

有什么想法？我很为难，我不知道去哪里找下一个...

Any thoughts? I'm stumped and I'm not sure where to look next...

推荐答案

更新您插入的代码，这样，如果遇到指示数据库锁定一个例外，它等待了一下，再次尝试。通过随机增量各时间（随机退避算法）增加的等待时间。这应该允许线程每次抢全局写锁。性能会很差，但代码应该没有显著的改装工作。

Update your insertion code so that if it encounters an exception indicating database lock, it waits a bit and tries again. Increase the wait time by random increments each time (the "random backoff" algorithm). This should allow the threads to each grab the global write lock. Performance will be poor, but the code should work without significant modification.

然而，SQLite是不适合高度并发修改。你有两个永久解决方案：

However, SQLite is not appropriate for highly-concurrent modification. You have two permanent solutions:

• 将一个真正的数据库，如PostgreSQL的或MySQL


• 通过一个线程序列化所有数据库的修改，以避免SQLite的的修改。

这篇关于难度运行在C＃中的SQLite数据库并发插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！