>There are two possibilities: >1. Transactions do work, but I'm misusing them and must learn how to >be more careful. In this case, I will update documentation to >properly explain their use to others.
>2. Transactions don't work, at least not for my task. In this case, >I will do my best to investigate the problem and suggest a >correction. >Either way, the right thing for me to do is continue digging in. >Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I >wrote, but my application is still broken, and I don't know what's >different about it. I'm working on instrumenting the fcntl() calls >to log the sequence of operations. Transactions do work provided you use them properly. The rules you need to obey are: Make sure you are doing nothing foolish like using "shared cache". Doing so changes how things work considerably and open a completely different kettle of fish. Make sure that you are using the default SERIALIZED or FULLMUTEX thread management on all connections whether you think you know better or do not need it or whatever. A few microseconds of CPU might save you many MONTHS of trying to locate a defect in your code. If you think that you need to use either "shared cache" or some threading mode other than the default serialized, then do that ONLY AFTER your application is working using the defaults. Do not fall into the premature optimization trap. Place all read and write statements which require a consistent database context in the same transaction. Context may change BETWEEN transactions but will not change WITHIN a transaction. That is, if you are executing multiple SELECT statements which require a consistent view of the database then they should all be located in the same read transaction. If you are selecting data and then performing updates based on the retrieved data, those operations need to occur within the same write transaction. SQLite3 does not have cursors and does not do UPDATE ... WHERE CURRENT OF CURSOR nor does it do SELECT ... FOR UPDATE OF ... which means that processing an update may mutate what you are reading and therefore you should complete your read before performing the update. IF THE TRANSACTION WILL OR MAY WRITE TO THE DATABASE AT SOME POINT BEFORE COMMIT/ROLLBACK then start the transaction with BEGIN IMMEDIATE. IF THE TRANSACTION WILL ONLY READ AND CANNOT EVER WRITE TO THE DATABASE BEFORE COMMIT/ROLLBACK then start the transaction with BEGIN DEFERRED (this is the default for a naked BEGIN). Transactions are an attribute of the DATABASE CONNECTION and not of a STATEMENT (a STATEMENT is an attribute of a CONNECTION). Multiple STATEMENTs may execute concurrently sharing a single CONNECTION (and hence transaction) context. In DELETE or TRUNCATE (that is, all modes except WAL) a READ transaction in progress blocks a WRITE transaction and a WRITE transaction in progress blocks all other attempts to commence a transaction of any type on any other connection. WAL mode permits READ and WRITE to proceed independantly provided that the WRITE transaction must only be performed against the "current" (head) database context and will fail if the context is not the head context at the time the attempt is made to obtain the WRITE lock (and since there is only one "current" head, there can only be one WRITE in progress at a time). An attempt to UPGRADE a READ transaction to a WRITE transaction will DEADLOCK (and immediately return an error) if another WRITE TRANSACTION is pending. (Hence always signal your intent when commencing a transaction by using the appropriate BEGIN syntax in order to avoid this state of affairs). WAL journalling only works where all database user processes are local to each other (ie, between connections all originating from the same computer -- where "same computer" means all processes have access to the same shared memory, so that you may determine whether a SYSPLEX or NUMA architecture constitutes "same computer" or not, by whether or not they can all access the same "shared memory" region). File locking only works reliably on local filesystems. It is unreliable for *ALL* remote filesystems, even if the target "remote" filesystem is local. A local filesystem is defined as one wherein the filesystem code resides and is executed "on the same computer" as the application. See the requirement 2 above to determine the meaning of "same computer". The cable between the "computer running the filesystem code" and the "block storage device" may have a length varying from inches to many thousands of miles. The command language the "filesystem" uses to access the underlying block storage is irrelevant (it may be ATA, SATA, SCSI, SAS, iSCSI, SAC (SCSI over Avian Carriers) or what ever your heart may desire). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
