Richard Hipp wrote: > On Sat, May 29, 2010 at 5:42 PM, Darren Duncan <dar...@darrenduncan.net>wrote: >> "3. Transactions that involve changes against multiple ATTACHed >> databases are atomic for each individual database, but are not atomic across >> all >> databases as a set." >> >> I greatly hope that this limitation could go away. I consider that >> SQLite's >> ability to make multiple databases subject to a common transaction is very >> powerful, and I would even argue, essential. > > See http://www.julianbrowne.com/article/viewer/brewers-cap-theorem
Thanks for the url; that was an interesting read. > The semantics of ATTACH imply Partition. In the new WAL design, readers > never block, which is the same as Accessible. Hence, we must forgo > cross-database atomic commits (what the CAP theorem calls "Consistent"). I believe that in SQLite's case, unless perhaps when some of the attached databases are on different physical machines from each other or the main one, that CAP (you can have at most 2 of 3) isn't applicable, or that there are some common situations where it isn't applicable. (From my reading, CAP mainly speaks to the situations where the database is split across multiple physical servers.) Primarily, I speak to the simplified situation where all SQLite databases that are open or attached by a SQLite process are all on the same machine, and that all simultaneous SQLite processes using any of the same databases at once are on the same machine. In this situation, the semantics of ATTACH either do *not* imply Partition, because everything is on the same machine, or it might be reason to tweak the semantics of ATTACH such that they would not imply Partition. I note from the SQLite documentation that one can not detach a database in the middle of an active transaction; this is a good thing and would contribute towards ATTACH not implying Partition. I don't know if ATTACH is similarly restricted or not, as the documentation doesn't say, though arguably this is less important; for consistency I might restrict ATTACH to not be possible in a transaction either, unless there is a reason to do otherwise. In the common scenario that I mention, with everything on one machine, would it not be unreasonable to support transactions atomic across all attached databases when WAL is in use? If it is reasonable, then perhaps SQLite could have a partial guarantee, such that transactions when WAL is in use are only guaranteed atomic across the subset of attached databases that are on the same machine as each other or as the SQLite process performing said transaction? I think that this matter may correspond somewhat to the limits of the wal-cache, a same-machine limit, though I wouldn't go so far as assume they are connected. On a tangential matter, where WAL isn't necessarily in use, the documentation for ATTACH seems to say that a cross-database transaction wasn't even possible before if the main database was ":memory:". If so, and while I can understand why this might have been the case, such that the extra super-journal file that marks the collection of journals/databases that are linked, I wonder if this can be changed somehow. For example, could SQLite be updated to be able to create this extra file even if there is no on-disk main database directly associated with it, when there are on-disk databases attached? It would be nice for the availability of cross-database transactions to be orthogonal to which database is ":memory:", and only a multiplicity of on-disk databases need synchronizing. Since journal or WAL files are based on the file names of the on-disk databases, could it not be possible that when the main database is ":memory:", that the user can specify a file name to use some other way? For example, the syntax for creating/opening a database could be generalized such that one would always give a file name, but that one would then provide an extra parameter to say whether the database is temporary or not? A TRUE value for this parameter would cause the database to just be in memory in general, and FALSE means on disk. Then a possible filename is always provided. That structure might also have other benefits. Thank you. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
