在 C# 中的 SQLite 数据库上运行并发插入很困难
我正在运行多个线程，每个线程都尝试对一个 SQLite 数据库执行插入操作。 每个线程创建它自己的与数据库的连接。 他们各自创建一个命令，打开一个事务执行一些插入，然后关闭事务。 似乎尝试任何操作的第二个线程都会收到以下 SQLiteException：数据库文件已锁定。 我尝试从事务中解开插入，并缩小每次提交中包含的插入的范围，但没有实际效果； 对 db 文件的后续访问会引发相同的异常。
有什么想法吗？ 我很困惑，不知道下一步该看哪里......
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...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
更新您的插入代码，以便如果遇到指示数据库锁定的异常，它会等待一段时间并重试。 每次以随机增量增加等待时间（“随机退避”算法）。 这应该允许每个线程获取全局写锁。 性能会很差，但代码应该可以在不进行重大修改的情况下工作。
然而，SQLite不适合高并发修改。 您有两种永久解决方案：
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.
However, SQLite is not appropriate for highly-concurrent modification. You have two permanent solutions:
需要检查两件事：
1) 确认您的 SQLite 版本是使用 THREAD 支持编译的
2) 确认您没有以独占方式打开数据库
Two things to check:
1) Confirmed that your version of SQLite was compiled with THREAD support
2) Confirm that you are not opening the database EXCLUSIVE
我不是在 C# 中执行此操作，而是在 Android 中执行此操作，但我通过在包装类的整个生命周期内保持 sqlite 数据库始终在拥有它的包装类中打开来解决此“数据库已锁定”错误。 然后，在此类中完成的每个插入都可以在自己的线程中进行（因为，根据您的数据存储情况、SD 卡与设备内存等，数据库写入可能需要很长时间），我什至尝试对其进行限制，使大约一次有十几个插入线程，并且每个线程都处理得很好，因为插入方法不必担心打开/关闭数据库。
我不确定持久数据库生命周期是否被认为是好的风格（在大多数情况下可能被认为是不好的），但目前它运行得很好。
I was not doing this in C#, but rather in Android, but I got around this "database is locked" error by keeping the sqlite database always opened within the wrapper class that owns it, for the entire lifetime of the wrapper class. Each insert done within this class then can be in its own thread (because, depending on your data storage situation, sd card versus device memory etc., db writing could take a long time), and I even tried throttling it, making about a dozen insert threads at once, and each one was handled very well because the insert method didn't have to worry about opening/closing a DB.
I'm not sure if persistent DB life-cycles is considered good style, though (it may be considered bad in most cases), but for now it's working pretty well.