如何在 Windows 共享驱动器上使 sqlite 健壮 [英] How to make sqlite robust over a Windows shared drive

问题描述

我知道不建议在网络驱动器上使用 Sqlite，原因有两个:性能和由于网络文件系统实施不当而导致数据损坏.

I know it is not recommend to use Sqlite over a network drive, for two reasons: performance, and data corruption due to bad implementation of network file systems.

不过，我想探索使用 Sqlite 来支持具有中央数据库的多用户应用程序的可能性.我们已经在单用户机器和多个数据库服务器(Oracle、PostgreSQL)上支持 Sqlite，用于多用户设置.使用 Sqlite 进行多用户设置可以方便地在现有 IT 基础架构上使用 Windows 共享驱动器 (SMB) 进行轻量级安装.

Still, I'd like to explore the possibility of using Sqlite to support a multi-user application with a central database. We already support Sqlite on a single-user machine and several database servers (Oracle, PostgreSQL) for multi-user setups. Using Sqlite for multi-user setup would be convenient to allow lightweight installations on existing IT infrastructures with Windows shared drives (SMB).

我们不太关心性能，因此我们准备为避免腐败付出代价.引用 http://www.sqlite.org/atomiccommit.html，第 9.1 节:

We don't care too much about performance, so we are ready to pay the price needed to avoid corruption. Quoting http://www.sqlite.org/atomiccommit.html, section 9.1:

但是如果你必须使用网络文件系统来存储 SQLite 数据库文件，考虑使用二级锁定机制来防止即使是本机文件系统，也可以同时写入同一个数据库锁定机构故障.

But if you must use a network filesystem to store SQLite database files, consider using a secondary locking mechanism to prevent simultaneous writes to the same database even if the native filesystem locking mechanism malfunctions.

我正在寻找实现这种机制的提示和建议，或者更一般地说，寻找避免 Sqlite 在 Windows 共享驱动器上出现问题的技巧.

I'm looking on hints and advices to implement such a mechanism, or, more generally, on tips to avoid problems with Sqlite over a Windows shared drive.

例如，我正在考虑使用以下脑残方法来锁定:在任何(非只读)SQLite 查询之前，尝试在同一个共享文件夹中创建一个空文件并保持打开状态直到结束查询；通常，任何其他 Windows 进程试图在它仍然打开时创建具有相同名称的文件都会阻塞或失败.那行得通吗?有什么更好的吗?

For instance, I'm considering the following brain-dead approach to locking: before any (non read-only) SQLite query, try to create an empty file on the same shared folder and keep it open until the end of the query; normally, any other Windows process trying to create a file with the same name while it's still open would block or fail. Would that work? Anything better?

推荐答案

另一个建议是编写一个 API，该 API 位于 SQLite 文件之上并为您锁定.

Another suggestion would be to write an API that sits over the SQLite file and does the locking for you.

这篇关于如何在 Windows 共享驱动器上使 sqlite 健壮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！