David M. Cotter <m...@davecotter.com> wrote: >> In SQLite every write is in a transaction whether you declare one with BEGIN >> or not. If you don't declare a transaction, SQLite >> invisibly surrounds each individual INSERT or UPDATE with a BEGIN and >> COMMIT. > sure, that's fine. but if you do your own BEGIN, then any INSERT you do > after that, *before* you do the COMMIT isn't actually > writing anything into the database, right? > > it's really only the COMMIT that actually writes to the database, right?
Wrong. There is a limited in-memory cache, but once the amount of data grows large enough, intermediate changes need to be spilled to disk and, indeed, written to the database file. > but you're not addressing the case where i have manually started a BEGIN. > after i manually do BEGIN, does the next INSERT need > to block, or is it the COMMIT that actually does the blocking? The first INSERT acquires a reserved lock on the database. No other connection will then be able to acquite a reserved lock, so there would be no other writer. For details, see http://www.sqlite.org/lockingv3.html > "if each thread is collecting several rows that it will insert in one fell > swoop, why should the mere gathering of rows (without > actually committing them yet) block another thread from gathering it's own > set of rows? You are free to "gather" the data that would go into the database before starting the write transaction. > it's only when all the rows are gathered > that i actually commit the change. There's no mechanism in SQLite to control when precisely intermediate data is written to the database file. It may happen before COMMIT. > am i making sense? Yes, but you are laboring under incorrect assumptions, and thus reaching incorrect conclusions. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
