如何调试锁定的sqlite3数据库
我正在 iOS 上编写一个应用程序，该应用程序使用 fmdatabase 包装的 sqlite3。我遇到的问题是，在某些时候，我的程序陷入了 FMDatabase 库内部的循环中，特别是调用 sqlite3_step 并发现数据库正忙的函数，然后一遍又一遍地重试。
我正在寻找通用的调试工具和技巧，因为在这里给出我的整个设置会太多。有些事情可能会产生后果，我打开了一个数据库句柄，该数据库已在另一个线程中拥有句柄。 sqlite3_threadsafe() 返回 2，所以我知道它已启用。我还通过制作一个非常简单的选择和更新语句来测试这个新连接。当我让程序运行并尝试在数据库上运行更新时，我陷入了困境。
我的程序本身生成的更新语句没有错误，因为当我不打开两个连接时，该查询运行良好。然而，我不知道我可能在哪里出错......
任何有关我可能出错的地方的帮助或提示将不胜感激。
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SQLite 在写入操作期间锁定整个数据库（即，当在任何表上发生写入时，不能同时对任何地方的任何表进行其他写入）。某些数据库通过表级锁（有时是行级锁）提供并发写入。与 SQLite 的实现相比，表级锁基本上意味着当您将数据写入给定表时，没有其他线程可以同时写入该表中的任何记录（但是，写入其他表可以在某些情况下同时发生）。类似地，行级锁更进一步，只允许锁定所涉及的必要行，从而允许多个线程对同一个表进行并发写入。这里的想法是最大限度地减少写入操作所需锁定的数据量，这有效地增加了跨数据库可能的并发写入量，具体取决于您的实现/使用数据库的方式，这可以显着提高吞吐量。
现在，回到你的问题......
SQLite 是线程安全的事实并不意味着多个线程可以同时写入它 - 这意味着它有一种处理来自多个线程的访问的方法 - 即（a）允许超时/retries，以及 (b) 当数据库当前持有锁时返回有用的错误 (SQLITE:Busy)。也就是说，线程安全只不过是“多个线程可以以不会因同时访问而导致数据损坏的方式访问此数据”。
基本上，在代码中的某处，一个线程试图在另一个线程释放其对数据库的锁定之前进行更新。这是 SQLite 的一个常见障碍，因为作者/文档会告诉您 SQLite 可以像冠军一样处理并发。现实情况是，SQLite 所认为的“并发支持”相当于尝试非常快，以便数据库上的锁仅保留很短的时间，因此数据库上的锁会在超时之前被释放。在很多情况下，这工作得很好并且永远不会妨碍你。但是，拥有非常短暂的锁与实际允许多个线程并发写入不同。
可以把它想象成 iOS 处理多任务的方式（至少从 iOS 5 开始，当我写这篇文章时）——实际上它所做的就是暂停其他应用程序，然后再返回它们。这样做的效果是：(a) 由于 CPU 利用率较低，电池寿命更长；(b) 您不必每次启动应用程序时都从头开始启动它。这很棒，但 iOS 中使用的实际“多任务”一词在技术上与其他环境（甚至 Mac OS X）中的“多任务”含义并不相同。
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...
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."
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.
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 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.