`sqlite3` 忽略 `sqlite3_busy_timeout`? [英] `sqlite3` ignores `sqlite3_busy_timeout`?

问题描述

我在多线程应用程序中使用 sqlite3(它是用 SQLITE_THREADSAFE=2 编译的).在监视窗口中，我看到 sqlite->busyTimeout == 600000，我.e.它应该有 10 分钟的超时时间.然而，sqlite3_step 返回 SQLITE_BUSY 显然比 10 分钟后更快(实际上它立即返回，就像我从未调用过 sqlite3_busy_timeout 一样).sqlite3 忽略超时并立即返回错误的原因是什么?

I use sqlite3 in multiple threads application (it is compiled with SQLITE_THREADSAFE=2). In watch window I see that sqlite->busyTimeout == 600000, i. e. that it is supposed to have 10 minutes timeout. However, sqlite3_step returns SQLITE_BUSY obvoiusly faster than after 10 minutes (it returns instantly actually, like if I never called sqlite3_busy_timeout). What is the reason that sqlite3 ignores timeout and return error instantly?

推荐答案

一种可能性:SQLite 在检测到死锁时忽略超时.

One possibility: SQLite ignores the timeout when it detects a deadlock.

场景如下.事务 A 作为读取器开始，然后尝试执行写入.事务 B 是一个写入者(要么以这种方式开始，要么以读取者身份开始并首先提升为写入者).B 持有一个 RESERVED 锁，等待读者清除以便开始写入.A 持有一个 SHARED 锁(它是一个读取器)并尝试获取 RESERVED 锁(以便它可以开始写入).各种锁类型的说明，参见http://sqlite.org/lockingv3.html

The scenario is as follows. Transaction A starts as a reader, and later attempts to perform a write. Transaction B is a writer (either started that way, or started as a reader and promoted to a writer first). B holds a RESERVED lock, waiting for readers to clear so it can start writing. A holds a SHARED lock (it's a reader) and tries to acquire RESERVED lock (so it can start writing). For description of various lock types, see http://sqlite.org/lockingv3.html

在这种情况下取​​得进展的唯一方法是回滚其中一个事务.再多的等待也无济于事，所以当 SQLite 检测到这种情况时，它不会响应忙碌超时.

The only way to make progress in this situation is for one of the transactions to roll back. No amount of waiting will help, so when SQLite detects this situation, it doesn't honor the busy timeout.

有两种方法可以避免死锁的可能性:

There are two ways to avoid the possibility of a deadlock:

1. 切换到WAL 模式 - 它允许一个作者与多个读者共存.
2. 使用 BEGIN IMMEDIATE 来启动一个最终可能需要写入的事务 - 这样，它会立即作为写入器启动.这当然会降低系统中潜在的并发性，这是避免死锁的代价.

1. Switch to WAL mode - it allows one writer to co-exist with multiple readers.
2. Use BEGIN IMMEDIATE to start a transaction that may eventually need to write - this way, it starts as a writer right away. This of course reduces the potential concurrency in the system, as the price of avoiding deadlocks.

这篇关于`sqlite3` 忽略 `sqlite3_busy_timeout`?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！