SQLite多进程访问 [英] SQLite multi process access

问题描述

我们在多进程和多线程应用程序中使用SQLite。
SQLite数据库文件使用嵌入式SQLite加密进行加密。
FAQ 指出，SQLite应该能够使用锁定机制来管理多进程访问。
我们遇到一个奇怪的问题：
当许多线程正在访问相同的数据库文件时，有时会限制发生违规，更具体地说，具有唯一约束的字段是在调用插入或替换之后获取重复的值，声明。
现在很常见，我们正在使用加密。在我们开始使用SQLite加密之前，我们没有注意到这样的行为。
有什么具体的已知问题吗？

解决方案

虽然SQLite是线程安全 t同时修改数据库：

每个线程然后继续插入一个
的记录，我们就说1000.
将遇到的问题是
以下：一个线程将通过在
上设置锁定来获取控制
的数据库。这很好，但$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $

（参考）

一次只允许一个线程修改数据库，但您可以有多个线程尝试修改数据库。

如果你想避免失败的锁定问题，你可以检查SQLITE_BUSY标志：

测试SQLITE_BUSY，我最初没有
。这里有一些伪代码
来说明一个解决方案：

  while（continueTrying） {
 retval = sqlite_exec（db，sqlQuery，callback，0，& msg）; 
 switch（retval）{
 case SQLITE_BUSY：
 Log（[％s] SQLITE_BUSY：sleep fow a while ...，threadName）; 
睡一下...（使用像sleep（）这样的东西）
 break; 
 case SQLITE_OK：
 continueTrying = NO; //我们完成
 break; 
默认值：
日志（[％s]无法执行\％s\：％s\\\
，threadName，sqlQuery，msg）; 
 continueTrying = NO; 
 break; 
} 
} 
 
 return retval; 
  

相同的参考

我的赌注是，你的约束违反与多线程无关，所以你可以发布实际您所获得的限制违规（或符合 www.sscce.org 的示例）。

We are using SQLite in a multi processes and multi threaded application. The SQLite database files are encrypted using the embedded SQLite encryption. The FAQ states that SQLite should be able to manage multi process accesses using locks mechanism. We are experiencing a strange problem: When many threads are accessing the same database file, sometime constrains violations occur, more specifically - a field with a unique constrain is getting duplicate values after calling "insert or replace" statement. It happens quite often now, that we are using the encryption. Before we started using SQLite encryption we did not notice such a behavior. Are there any specific known issues with this?

解决方案

While SQLite is "thread-safe" you still can't concurrently modify the database:

Each thread then proceeds to insert a number of records, let's say 1000. The problem you will encounter is the following: one thread will get control over the database by setting a lock on the file. This is fine, but the rest of the threads will keep on failing for each attempted INSERT while the lock is active. (reference)

Only one thread is allowed to modify the database at a time, but you can have multiple threads that attempt to modify the database.

If you want to avoid the failing-while-locked issue you can check the SQLITE_BUSY flag:

Test for SQLITE_BUSY, which I didn't do originally. Here's some pseudo-code to illustrate a solution:

  while (continueTrying) {
    retval = sqlite_exec(db, sqlQuery, callback, 0, &msg);
    switch (retval) {
      case SQLITE_BUSY:
        Log("[%s] SQLITE_BUSY: sleeping fow a while...", threadName);
        sleep a bit... (use something like sleep(), for example)
        break;
      case SQLITE_OK:
        continueTrying = NO; // We're done
        break;
      default:
        Log("[%s] Can't execute \"%s\": %s\n", threadName, sqlQuery, msg);
        continueTrying = NO;
        break;
    }
  }

  return retval;

same reference

My bet is that your constraint violation has nothing to do with multithreading, so could you please post the actual constraint violation that you're getting (or an example that complies with www.sscce.org).

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