As various people search for application and/or SQLite bugs related to multiple threads and BEGIN, let me try to aid the effort by better describing exactly what BEGIN does and suggesting some debugging tricks.
Realize that BEGIN does not actually create any file locks or check to see if any file locks already exist, nor interact in any other way with the filesystem. File locks are only created by SELECT, UPDATE, INSERT, and DELETE statements. (OK, also CREATE and DROP statements, but let's ignore those for now for simplicity. Presumably the schema is fixed at the point where the problems are occuring.) All BEGIN does is to set a flag that says "do not automatically perform a COMMIT after each write to the database". This is the autoCommit flag that I mentioned in a prior email. autoCommit is a boolean member of the sqlite3 structure. A lock is acquired at the beginning of each UPDATE, INSERT, or DELETE if it does not already exists. After each UPDATE, INSERT, or DELETE, sqlite checks the value of the autoCommit flag, and if it is true it automatically does a COMMIT. A read-lock is acquired before each SELECT if it does not already exists, and after the SELECT is done, the read-lock is dropped if autoCommit is true. So the BEGIN instruction does not do anything with the filesystem. It does not interact in any way with the operating system or with other database connections. All BEGIN does is clear the autoCommit flag. So it is hard to imagine how having other threads could possibly effect its behavior. At any time, you can determine the value of the autoCommit flag using the sqlite3_get_autocommit() API. See http://www.sqlite.org/capi3ref.html#sqlite3_get_autocommit I suggest that people who are getting back unexpected "cannot start a transaction within a transaction" errors should use the sqlite3_get_autocommit() API in some printf()s to trace the status of the autocommit flag within their application. Prior to running BEGIN, it should always be the case that sqlite3_get_autocommit() returns TRUE. If sqlite3_get_autocommit() returns FALSE, then the BEGIN that follows will give the "cannot start..." error. I suspect what is happening is that some prior COMMIT or ROLLBACK is not setting the autoCommit flag back to TRUE. This might be because the COMMIT or ROLLBACK failed. Or, there could be some kind of bug in SQLite that is causing the autoCommit flag to not be set correctly. I suspect the former, but am open to evidence pointing to the latter. It might be useful to use the sqlite3_get_autocommit() function to print out the value of the autoCommit flag after each COMMIT and ROLLBACK is executed. This might help to isolate the problem. -- D. Richard Hipp <[EMAIL PROTECTED]>
