On 6 Feb 2011, at 1:30am, David M. Cotter 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?
An alternative point of view is that the transactions hit the database immediately, and that either ROLLBACK or failure to do a COMMIT backs them out. It depends what you think 'the database' is. Remember that it's possible to run SQLite without any journaling at all: even uncommitted changes go to the database file. If a journal file exists, then I feel 'the database' includes both the database file and the journal file. >>> i understand that one commit will block the other >>> >>> but does inserting data during a transaction actually block too? >> >> It is each transaction which blocks each other transaction, but the default >> behaviour is not to lock the file until the first command that makes a >> change. So once one thread has executed an INSERT, all other threads will >> be blocked at their own BEGINs and will never even get to do an INSERT until >> the first thread has done its COMMIT. > > 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 after the BEGIN starts the blocking. Unless you specifically override the behaviour by doing a BEGIN IMMEDIATE (which you've previously said you weren't doing). >>> is inserting considered a "writing transaction" if there is a "begin" >>> before it? cuz it's not actually writing to the DB proper, it's writing to >>> it's journal file, saving things up until the "commit" or "rollback". >> >> The natural question you're asking is something like "Well, if each thread >> is just inserting new rows, why do they need to block each-other ? They >> don't care what data the other threads are inserting." > > well, i would phrase it this way: > > "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? it's > only when all the rows are gathered that i actually commit the change. Since > the database isn't being written to during the gathering phase, they don't > care what other threads are gathering". See my previous answer. If a program tries to add a row to the database that conflicts with one that's already there, it needs to get an error immediately. Not later when it has forgotten the context of the row that's causing the problem and can no longer figure out how to handle the problem. So a thread that's trying to do an INSERT must be blocked until all the changes that will be done before it have been completed. Also there's the problem that some applications need to know the rowid of a freshly-inserted row. If you don't need this behaviour because you're confident you'll never get a clash, then you could accumulate your INSERTs in memory, then blast through them when you would previously have just done the COMMIT. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
