I wanted to test transaction concurrency using the sqlite shell. So, I created a simple database. Note that this is on debian so the sqlite shell is sqlite3.
sqlite3 test.db 'create table foo(bar);'
Then, I created a writer loop to make a bunch of tiny transactions:
for n in `seq 1000000`; do echo "begin transaction; insert into foo values($n);end;" ;done | sqlite3 test.db
Note that this is a bunch of transactions in a single session, using a single sqlite shell instance which presumably keeps the database attached.
Then, in another terminal, I tried to truncate the table. I expected that this should either fail, if the database is in a transaction, or succeed if the database is not in a transaction.
while ! sqlite3 test.db 'delete from foo;'; do true ; done
As expected, this spawns a bunch of sqlite3 processes sequentially, each one attempting to start a write transaction. Eventually one delete transaction succeeds, and the table is truncated; However, then in the other window, the rest of the statements fail with:
Error: near line 999998: cannot start a transaction within a transaction
So, my question is this: How is there an error message about starting a transaction within a transaction? There's an explicit begin and end for each transaction, and on the other side, the delete operation with truncate optimization should be an implicit transaction. I also notice that if I do each insert in a separate sqlite3 process invocation like so:
for n in `seq 1000000`; do echo "begin transaction; insert into foo values($n);end;" | sqlite3 test.db ;done
Then I don't receive the error about transactions within transactions.
thanks,
Brian Minton