Hi, Boris Kolpackov <bo...@codesynthesis.com> writes:
> Half of the threads executes the following transaction: > > BEGIN > INSERT > INSERT > INSERT > COMMIT > > The other half of the threads runs the following transaction: > > BEGIN > SELECT > UPDATE > COMMIT > > As expected, I periodically get deadlocks (SQLITE_LOCKED return code from > unlock_notify()) for the second transaction due to the read to write > lock upgrade. But I also get deadlocks reported for the first transaction > and this is something that I didn't expect to happen. Ok, after some debugging I figured this one out. SQLite has a peculiar shared cache locking semantics in that besides table-level locks there is also a transaction-level lock, or, more specifically, the write transaction lock. There can only be one transaction operating on the shared cache that is writing. If another transaction tries to upgrade from reading to writing, then SQLITE_LOCKED is returned. As a result, the above two transactions have the following locking protocols (pseudo-code). First transaction: trans_lock table_lock (w) Second transaction: table_lock (r) trans_lock table_lock (w) Here trans_lock indicates acquisition of the write transaction lock and table_lock indicates acquisition of the table lock, either for reading or writing. From this it is quite obvious why the first transaction also deadlocks. Needless to say I find the fact that a writing transaction needs to acquire two separate locks very unintuitive from the user's perspective. It would have been fine if it were implementation details, but as we can see from the above, it can lead to the unexpected, user-visible behavior. One way to fix this would be to "un-start" the transaction (and release the write transaction lock) in the first transaction if acquiring the write table lock failed. If you think of it, it is kind of pointless to keep the write transaction lock if we failed to acquire the (first) write table lock and therefore won't be able to write anything to the database. Not sure how easy or difficult it will be to implement. Maybe it will be easier to downgrade the transaction to reading since there is already a mechanism for upgrading it to writing. Just some ideas... Boris -- Boris Kolpackov, Code Synthesis http://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
