It turns out that POSIX has the following ugly misfeature: When a file descriptor is closed, all locks on the file that the file descriptor points to that are owned by the current process are dropped. Even locks created by completely separate and independent file descriptors. Who knew?
Consider what this means for SQLite. Suppose you have a process that opens a database and starts making changes. Like this:
db = sqlite_open("example1.db", 0, 0);
sqlite_exec(db, "BEGIN", 0, 0, 0);
sqlite_exec(db, "INSERT INTO whatever VALUES(1,2,3)", 0, 0, 0);We are in the middle of a transaction, so the file is open and it has a write lock. But before continuing, some obscure subroutine in your code does this:
db2 = sqlite_open("example1.db", 0, 0);
In second sqlite_open creates a new file descriptor on the database. Then it attempts to open the database for reading, so that it can load the database schema. It finds, however, that there is a write lock on the file, so it cannot read from it, so it immediately closes the file descriptor again. But in closing the file descriptor from this second sqlite_open(), POSIX also clears the write lock that was created by the first sqlite_open() above.
Thus, the database is left sitting there, half-way updated, with no lock on it. Any other unsuspecting process can come along and update the database, leading to unimagined corruption.
It will probably take some time to fix this problem. Until it is fixed, if you are using SQLite on unix, you should take care to never open the same database file more than once in the same same process. This also applies to multithreaded processes. You should never open the same database more than once in the same process - even in two independent threads.
Until I have had a chance to investigate this situation futher, you should also avoid having two or more virtual machines (created by the sqlite_compile() API) active at once. Finalize one VM before creating the next one.
At some point, I'll figure out a way to code around this horribly ugly wart in the POSIX advisory file locking. Until then, you risk database corruption if you try to do more than one thing at a time with the same database in the same process.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
