SQLite Forum

Timeline
Login

11 forum posts by user bjmgeek

2021-09-29
19:22 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 198d027b88 user: bjmgeek)

You are correct that I should have been talking about sqlite connection objects, not processes. However, in this specific case, I think there is a one-to-one mapping between the two, since each invocation of the sqlite shell is associated with a single connection object.

19:10 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 2dd97a4afc user: bjmgeek)
I've tried this with multi-line statements as well:

window 1:
for n in `seq 1000000`; do echo -e "begin transaction;\ninsert into foo values($n);\nend;" ;done|sqlite3 test.db 

Window 2:
while ! sqlite3 test.db 'begin;select min(bar) from foo;end;'; do true ; done

The result is 

Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
[...]
1

on the select side,
and on the insert side:
or n in `seq 1000000`; do echo -e "begin transaction;\ninsert into foo values($n);\nend;" ;done|sqlite3 test.db 

Error: near line 2640: database is locked
Error: near line 2641: cannot start a transaction within a transaction
Error: near line 2871: database is locked
Error: near line 2872: cannot start a transaction within a transaction
Error: near line 2874: database is locked
Error: near line 2875: cannot start a transaction within a transaction
Error: near line 2937: database is locked
Error: near line 2938: cannot start a transaction within a transaction
Error: near line 2940: database is locked
Error: near line 2941: cannot start a transaction within a transaction
Error: near line 2964: database is locked
Error: near line 2965: cannot start a transaction within a transaction
Error: near line 3114: database is locked
[...]

Even after the select transaction finishes, all further insert transactions fail.
2021-09-22
22:21 Reply: sqlite_master -> sqlite_schema rename breaks DB dump compatibility (artifact: 72199d1a12 user: bjmgeek)

Is there a way to make the new version of the sqlite shell generate compatible dumps?

19:46 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 7ddeab6f88 user: bjmgeek)

I agree that that is what is happening, but I don't think that's what's supposed to happen. My understanding was that transactions are distinct to an individual process. Also, note that even with the DELETE running in an explicit transaction, the same thing happens.

Looking at the documentation https://www.sqlite.org/lang_transaction.html I see the following:

SQLite supports multiple simultaneous read transactions coming from separate database connections, possibly in separate threads or processes, but only one simultaneous write transaction.

19:36 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 9c31b3e096 user: bjmgeek)

I have not checked the exit status, but it's all one sqlite process.

19:36 Edit reply: cannot start a transaction within a transaction using sqlite shell (artifact: 8cd13ecec5 user: bjmgeek)

By the way, this is for version 3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1.

19:34 Edit: cannot start a transaction within a transaction using sqlite shell (artifact: 058cb360c1 user: bjmgeek)

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

19:31 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 2132eed644 user: bjmgeek)

I also noticed that if I leave the database in the default journal mode of delete, I can do a read-only transaction and cause the same error:

while ! sqlite3 test.db 'begin;select count(*) from foo;end;'; do true ; done

which produces the following output: Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked Error: database is locked 36943

and the other window starts showing: Error: near line 871305: cannot start a transaction within a transaction Error: near line 871306: cannot start a transaction within a transaction Error: near line 871307: cannot start a transaction within a transaction Error: near line 871308: cannot start a transaction within a transaction Error: near line 871309: cannot start a transaction within a transaction Error: near line 871310: cannot start a transaction within a transaction Error: near line 871311: cannot start a transaction within a transaction Error: near line 871312: cannot start a transaction within a transaction Error: near line 871313: cannot start a transaction within a transaction Error: near line 871314: cannot start a transaction within a transaction Error: near line 871315: cannot start a transaction within a transaction Error: near line 871316: cannot start a transaction within a transaction Error: near line 871317: cannot start a transaction within a transaction

In WAL mode, read-only transactions work just fine, but read-write transactions still cause the error.

19:23 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 5946e673c1 user: bjmgeek)

By the way, this is for version 3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1. I have not checked the exit status, but as you said, it's all one sqlite process.

17:21 Post: cannot start a transaction within a transaction using sqlite shell (artifact: bd1022c904 user: bjmgeek)
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
2020-04-23
13:19 Post: CREATE TABLE allowing empty column name (artifact: c39e80aeaf user: bjmgeek)
https://www.sqlite.org/syntax/column-def.html shows column-name as required.  However, the following does not give an error.:

$ sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table foo(integer primary key);
sqlite> insert into foo values (100);
sqlite> select * from foo;
100
sqlite> 

Is this an undocumented feature, or a bug?  Or is this normal SQL syntax that I'm just misunderstanding?  I see that it's not just the rowid column:

sqlite> select rowid from foo;
1

I was trying create a table with only one column, rowid.  This is the intended behavior:

sqlite> drop table foo;
sqlite> create table foo(rowid integer primary key);
sqlite> insert into foo values (100);
sqlite> select rowid from foo;
100
sqlite> select * from foo;
100

Update: After further testing I discovered that the first example made a column called `integer`.