如何调试锁定的sqlite3数据库 [英] how to debug locked sqlite3 database

问题描述

我正在iOS上编写一个使用fmdatabase包装的sqlite3的应用程序。我遇到的问题是，在某些时候，我的程序卡在FMDatabase库中的一个循环中，特别是一个调用sqlite3_step并发现数据库正忙的函数，然后一遍又一遍地重试。

I am writing an application on iOS that uses sqlite3 wrapped by fmdatabase. I am running into the problem that at some point, my program is stuck in a loop inside of FMDatabase libraries, particularly, a function that calls sqlite3_step and finds out that the database is busy, then retries over and over again.

我正在寻找一般的调试工具和技巧，因为在这里完成整个设置太过分了。有些事情可能会产生影响，我打开了一个数据库句柄，该数据库句柄已经在另一个线程中有一个句柄。 sqlite3_threadsafe（）返回2，所以我知道它已启用。我还通过一个非常简单的select和update语句测试了这个新连接。当我让我的程序运行，并且当它试图在数据库上运行更新时，我卡住了。

I am looking for general debugging tools and tips, as it will be too much to give my whole setup here. Some things that might be of consequence, I opened a database handle to a database that already has a handle in another thread. sqlite3_threadsafe() returns 2, so I know its enabled. I have also tested this new connection by making a very simple select and update statement. When I let my program run, and when it tries to run an update on the database, i get stuck.

我的程序自己的更新语句没有错，因为当我不打开两个连接时，此查询运行正常。然而，我没有看到我可能出错的地方......

The update statement that my program makes itself is not at fault, because this query runs fine when I do NOT open up two connections. Yet, I fail to see where I could be going wrong...

我将非常感谢任何有关我可能出错的帮助或提示。

Any help or tips on where I might be wrong would be greatly appreciated.

推荐答案

SQLite在写入操作期间锁定整个数据库（即在任何表上发生写入时，没有其他写入，任何地方的任何表都可以同时发生）。某些数据库通过表级锁或有时行级锁提供并发写入。为了与SQLite的实现形成对比，表级锁基本上意味着当您将数据写入给定表时，没有其他线程可以同时写入该表中的任何记录（但是，写入其他表可以在某些情况下同时发生）。类似地，行级锁甚至更进一步，并且仅允许锁定所涉及的必要行，从而允许从多个线程发生对同一表的并发写入。这里的想法是最小化锁定写入操作所需的数据量，这有效地增加了数据库中跨的并发写入量，并且取决于您的实现/如何使用数据库，这可以显着提高吞吐量。

SQLite locks the entire database during a write operation (i.e. when a write is happening on any table, no other write, to any table anywhere can happen at the same time). Some databases provide concurrent writes via table-level locks, or sometimes row-level locks. To contrast this to SQLite's implementation, a table-level lock basically means that when you're writing data to a given table, no other thread can write to any record in that table, at the same time (however, writes to other tables can occur simultaneously, in some circumstances). Similarly, row-level locks take it even further, and allow only the necessary rows involved to be locked, allowing concurrent writes to the same table to happen from multiple threads. The idea here is to minimize the amount of data you need to lock for a write operation, which effectively increases the amount of concurrent writes possible across the database, and depending on your implementation/how you use your database, this can significantly increase throughput.

现在，回到你的问题......

Now, back to your question...

SQLite的事实线程安全并不意味着多个线程可以同时写入它 - 这意味着它有一种处理来自多个线程的访问的方式 - 这是（a）允许超时/重试，以及（b）返回有用的错误（ SQLITE：Busy）当前数据库上有一个锁。也就是说， threadsafe 仅仅意​​味着多个线程可以以不会因同时访问而导致数据损坏的方式访问此数据。

The fact that SQLite is threadsafe doesn't mean that multiple threads can concurrently write to it - it means that it has a way of handling access from multiple threads - which is to (a) allow timeouts/retries, and (b) to return a useful error (SQLITE:Busy) when a lock is currently held on the database. That is, threadsafe means nothing more than, "Multiple threads can access this data in a way that won't result in data corruption due to simultaneous access."

基本上，在代码中的某个地方，一个线程试图在另一个线程释放其对数据库的锁之前进行更新。这是SQLite的常见障碍，因为作者/文档会告诉您SQLite可以像冠军一样处理并发。实际情况是，SQLite认为并发支持相当于试图非常快，因此数据库上的锁只能保持很短的时间，因此在超时之前释放对数据库的锁定。在很多情况下，这很好用，永远不会妨碍你。但是，拥有非常短暂的锁与实际允许来自多个线程的并发写入不一样

Basically, somewhere in the code, one thread is trying to do its update before another thread has released its lock on the database. This is a common hurdle with SQLite, because the authors/documentation will tell you that SQLite can handle concurrency like a champ. The reality is that what SQLite considers "concurrency support" amounts to trying to be very fast so that locks on the database are only held for a very short time, and therefore locks on the database are released before timeouts are hit. In a lot of cases, this works just fine and never gets in your way. However, having very short-lived locks is not the same as actually allowing concurrent writes from multiple threads.

想想它就像那样iOS执行多任务处理（至少从iOS 5开始，当我写这篇文章时） - 它真正做的是暂停其他应用程序并回归它们。这样做的结果是：（a）由于CPU利用率较低，电池寿命更长;（b）每次启动时都不必从头开始启动应用程序。这很棒，但iOS中使用的实际多任务处理在技术上并不意味着与其他环境（甚至Mac OS X）中的多任务处理相同。

Think of it like the way that iOS does multitasking (at least as of iOS 5, when I'm writing this) - really what it's doing is putting other apps on pause, and coming back to them. This has the effect that (a) battery life is much better due to lower CPU utilization, and (b) you don't have to start an app from scratch every time you launch it. This is great, but the actual word "multitasking" as used in iOS doesn't technically mean the same thing as "multitasking" in other environments (even Mac OS X).

SQLite是一样的。他们有并发支持吗？好吧，但他们定义并发这个词的方式并不是DB世界的其他部分定义并发的方式。

SQLite is the same way. Do they have "concurrency" support? Well sort of, but the way they define the word "concurrency" isn't the way the rest of the DB world defines "concurrency".

没有人真的错了，但在这种情况下，它会增加实施的混乱。

No one is really wrong, but in cases like these, it adds to implementation confusion.

这篇关于如何调试锁定的sqlite3数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！