On 5 Feb 2011, at 11:00pm, David M. Cotter wrote: > i may not have been clear > > i want to begin transactions on different threads at once > in each thread > begin a transaction > insert lots of data, this may take a long time > commit transaction
Okay, here's some background. 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. > 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. > 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.". The answer is that even the process of inserting new data needs to look at existing data. There's the problem of dealing with columns marked as UNIQUE: each thread needs to be stopped if it tries to insert a new row that would violate database restrictions, and it has to be stopped on that instruction, not long after it has forgotten what it did that caused the problem. And for the system to decide which thread should get the rejection it has to consider the operations in a particular order. Which is why it only considers one transaction at a time. For a fuller explanation of what causes and is blocked by locking see <http://www.sqlite.org/lang_transaction.html> Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
