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]
