On 04/26/2012 03:44 PM, Richard Hipp wrote:
On Thu, Apr 26, 2012 at 3:34 PM, Brad House<b...@monetra.com> wrote:I've got 2 threads with different connections to the same database. This is approximately what I am seeing: Thread 1: SELECT bar,baz FROM foo WHERE ...; Thread 2: BEGIN IMMEDIATE TRANSACTION; INSERT INTO foo VALUES (...); **sqlite3_step returns 6 (SQLITE_LOCKED) ROLLBACK TRANSACTION Thread 1: **sqlite3_step returns 4 (SQLITE_ABORT): abort due to ROLLBACK So why, if Thread 2 rolls back does Thread 1 get aborted?A rollback deletes content out from under other queries. So if you have a query pending in thread 1 and thread 2 tries to rollback, there are two options: (A) The rollback fails (B) The pending query is aborted. It used to be that we did (A). (If you had checked the return codes from your "ROLLBACK TRANSACTION" statement you would have been seeing it fail.) The latest code does (B) instead. Your software depends upon behavior (A). Other users prefer behavior (B). I don't know how to make everyone happy....
I just ran another test, ensuring I check return codes specifically on the ROLLBACK (And I was), and from what I can tell, I'm not getting a failure on either either 3.7.11 or 3.7.10, it is returning SQLITE_OK. NOTE: I'm using sqlite3_exec() to send the ROLLBACK... Rollback is actually the only time we don't use sqlite3_prepare/_step/etc. So I'm not exactly sure what you mean by I would see it failing. What logic should we be using if we receive an SQLITE_LOCKED and we should not ROLLBACK? Do we simply perform an sqlite3_reset() then retry the sqlite3_step() ... and keep doing that until it succeeds after the SELECT has released its locks (I'm assuming if we do that the select WILL succeed, right?)? The sqlite3_step() documentation provides no guidance on what to do if SQLITE_LOCKED is returned ... we had assumed the same guidance as SQLITE_BUSY applied which says explicitly to perform a ROLLBACK. That said, I'm still struggling to see the merit of a rollback in one thread causing an abort in another thread. We always took rollback to mean we are conceding execution to the other thread, not the other way around. We use MySQL, Oracle, Microsoft SQL Server, PostgreSQL ... never seen such a behavior. Sorry if I'm being dense here. Thanks! -Brad _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
