You can use BEGIN IMMEDIATE or BEGIN EXCLUSIVE depending on the type of lock you'd like.
SQLite BEGIN syntax: http://sqlite.org/lang_transaction.html SQLite locks: http://sqlite.org/lockingv3.html SQLite Busy Handler: http://sqlite.org/capi3ref.html#sqlite3_busy_handler Pat -----Original Message----- From: Russell Leighton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 8:24 AM To: sqlite-users@sqlite.org Subject: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with multiple threads?] So, this was very enlightening...I have a simple backup function that I now question is correct. It does: - execute "begin" // lock from writes -copy db file to new file byte by byte - execute "commit" // unlock ...I was thinking that "begin" would lock the file. If I use an flock() [or should it be lockf() ?? ] to bracket the file copy will I be safe? I was hoping to have something more efficient than a table by table copy for backup. Thx [EMAIL PROTECTED] wrote: >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]> > > >
