SQLite 和并发 [英] SQLite and concurrency

问题描述

我需要在我们的一个产品中集成一个数据库，我想知道哪个更适合我们的需求(简单的自动部署、无需管理、性能良好)，而 sqlite 似乎是一个很好的解决方案.问题在于数据库可能会面临高并发问题:每次客户端连接到运行数据库的服务器时，都会通过 PHP (Apache) 访问它.一个客户端大约每 10 秒连接(并执行一次 INSERT 查询)到服务器，并且可能有 100 个以上的客户端在运行.

I need to integrate a database in one of our products and I wonder which one would be more suited to our needs (easy automatic deployment, no administration, good performance), and sqlite seems to be a good solution. The problem is that the database could potentially face high concurrency issues: it is accessed through PHP (Apache) each time a client connects to the server the database is running on. One client connects (and execute an INSERT query) approximatively every 10 seconds to the server, and it could possibly have more than 100 clients running.

在执行 INSERT 查询时，sqlite 在特定时间锁定整个数据库一段时间.有没有办法计算这个持续时间?如果这不可能，你认为sqlite(v3.3.7)是否仍然适应上述条件?

When executing an INSERT query, sqlite locks the entire database at a certain time for a certain duration. Is there a way to compute that duration? If this is not possible, do you think sqlite (v3.3.7) is still adapted with the above conditions?

推荐答案

我认为 SQLite 不是满足这些需求的好解决方案.SQLite 专为本地和轻量级使用而设计，不能处理数百个请求.

I don't think that SQLite would be a good solution for those requirements. SQLite is designed for local and lightweight use only, not to serve hundreds of requests.

我会推荐一些其他解决方案，例如 MySQL 或 PostgreSQL，两者都可以很好地编写脚本.所以，如果我是你，我会努力编写设置脚本.

I would recommend some other solution, for example MySQL or PostgreSQL, both can be scripted quite well. So, if I were you, I would put my efforts into the setup scriptings.

为了避免 SQLite 的信徒和仇恨者之间的火焰战争，让我提请您注意经常提到的 SQLite何时使用 文档(我相信它被认为是可靠的来源).他们在此声明如下:

To avoid the flame war between SQLite believers and haters, let me draw draw your attention to the often referred SQLite When-To-Use document (I believe it is considered as a credible source). Here they state the following:

客户端/服务器 RDBMS 可以更好地工作的情况

高并发

SQLite 支持无限数量的并发读取器，但它在任何时刻只允许一个写入器.在许多情况下，这不是问题.作家排队.每个应用程序都快速完成其数据库工作并继续前进，并且没有锁定持续超过几十毫秒.但是有些应用程序需要更多的并发性，这些应用程序可能需要寻求不同的解决方案.

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

我认为在提到的问题中涉及很多写入，如果 OP 选择 SQLite，它将导致不可扩展的解决方案.

I think that in the referred question involves many writes and if the OP would go for SQLite, it would result a non-scalable solution.

这篇关于SQLite 和并发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！