Menu
I met with a strange problem about sqlite3. I obtained different connections of the same database file using open() method. connection 1 begins a transaction, and connection 2 begins another transaction, which is to update several records of a table. Then connection 1 commit the transaction, followed by connection 2 commit its transaction. But I found that the update command of connection 2 is never actually update the record in the database. There is no exception throwed during this procedure. I don't know why the problem occurs. Can anyone explain the reason to me?
947
The current version of SQLite handles multiple connections through its thread-mode options: single-thread, multi-thread, and serialized. Single-thread is the original SQLite processing mode, handling one transaction at a time, either a read or a write from one and only one connection.
High Concurrency 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. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds.
Usually, SQLite allows at most one writer to proceed concurrently. The BEGIN CONCURRENT enhancement allows multiple writers to process write transactions simultanously if the database is in "wal" or "wal2" mode, although the system still serializes COMMIT commands.
SQLite Version 3.0. 0 introduced a new locking and journaling mechanism designed to improve concurrency over SQLite version 2 and to reduce the writer starvation problem. The new mechanism also allows atomic commits of transactions involving multiple database files.
If you read the SQLite documentation, you will see that it supports multiple connections for reading only, you cannot write to the database from mulitple connections, because it's not designed for that.
http://www.sqlite.org/faq.html#q5
76
Unless you use BEGIN IMMEDIATE to initiate your transactions, you run the risk of having to rollback and retry them. A BEGIN does not do any locking; subsequent UPDATE or INSERT gets the lock, and you need to check the result code to see if they fail. See this page on transactions and this one on locks.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
