SQLite Forum

INSERT INTO SELECT upgrade to IMMEDIATE/EXCLUSIVE
Login

INSERT INTO SELECT upgrade to IMMEDIATE/EXCLUSIVE

(1) By anonymous on 2022-01-22 13:15:16 [source]

Hi,

I’m wondering at what point a transaction is upgraded to IMMEDIATE/EXCLUSIVE when using INSERT INTO SELECT.

Is it before or after the SELECT?

Presumably if it’s before I can rely on this to provide me with a consistent read.

Thanks for any help,

Ben

(2) By Keith Medcalf (kmedcalf) on 2022-01-22 21:18:11 in reply to 1 [link] [source]

Like every other statement, if the statement is executed outside an explicit transaction, then a transaction appropriate to the statement being executed is commenced as the first operation of statement execution, and (assuming no errors occurred) the transaction is committed as the last operation of statement execution.

Statements which only read data from the database occur inside an automatic read transaction. Statements which write/update data to the database occur inside a write (immediate) transaction.

Transaction states may be "upgraded" when required. For example, if a "read" transaction has been started already (whether explicitly or implicitly) and the statement being processed requires a write (immediate) trasaction then an attempt is made to upgrade the transaction level (with all the nasty implications that process entails).

If a transaction is in process when a statement commences execution, then the transaction state is not terminated at the end of that statement. The transaction state persists until it is terminated by your command. The transaction state will be the maximum level (read/immediate) that was required to process all the statements executed within the transaction context.

(3) By anonymous on 2022-01-23 09:35:55 in reply to 2 [link] [source]

Thanks for the reply. I think I understand what you're saying, but I'm not certain in my mind on what constitutes a statement for the purposes of determining a write operation.

Let me add an example (apologies for having not done so before)...

Schema:

CREATE TABLE blah (a INTEGER PRIMARY KEY NOT NULL);

Query:

BEGIN DEFERRED

INSERT INTO blah SELECT COALESCE(MAX(a), 0) + 1 FROM blah;

COMMIT

INSERT INTO... is obviously a statement, so from your answer I can assume that it is recognised as a write operation and the deferred transaction is upgraded to immediate. That part I get :-)

However, what I'm wondering is whether the SELECT expression inside the INSERT INTO statement runs before the upgrade to IMMEDIATE or after? i.e. can I rely on the fact that when I perform the SELECT that no other operation can be writing and that I will never get a PK violation?

Thanks,

Ben

(4) By MBL (UserMBL) on 2022-01-23 11:10:09 in reply to 3 [link] [source]

My understanding of what an insert statement is comes from the syntax explanations. The insert-stmt contains a select-stmt as one of the possible paths. As such I expect the upgrade to an IMMEDIATE transaction for the whole and not separate for the insert and the contained sub-statement to happen for the whole. As an easy identification I suggest to count the semi-colon. Each statement is terminated by just one.

If you want to do you own tests I suggest to use two instances of the CLI and use in of of them a long lasting function like the edit('text','editor'), which will keep the transaction open while you can try in the 2nd instance what you want to get proved.

>CREATE TABLE blah (a INTEGER PRIMARY KEY NOT NULL);
>INSERT INTO blah(a) values(0);
>BEGIN DEFERRED TRANSACTION; INSERT INTO blah SELECT edit(a,'notepad.exe') from blah; COMMIT TRANSACTION;

The editor opens and the transaction is pending before the last line finishes; the editor shows a 0 and I change it into a 1 but wait with saving and closing the windows until the following 2nd instance has finished:

>sqlite3.exe -box -header -echo test.dbf
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .tab
.tab
blah
sqlite> select * from blah;
select * from blah;
┌───┐
│ a │
├───┤
│ 0 │
└───┘
sqlite> insert into blah(a) values(1);
insert into blah(a) values(1);
Error: stepping, database is locked (5)
sqlite>

This example shows that a 2nd transaction is locked from writing anything while the deferred tranaction executes the select sub-statement.

My conclusion is that your PK assumption is correct, it will never fail as 1 on top of the maximum will always not yet been used - and no other transaction can change that before your deferred on has been finished.

(5) By anonymous on 2022-01-23 15:03:38 in reply to 4 [link] [source]

Thanks such a comprehensive reply.

I suspected as much given the documentation talks about statements and not expressions, but I often find behaviour like this has it's quirks and just wanted to be sure. Your answer gives me confidence it does, thanks.

I had no idea you could do something like edit(a, 'notepad.exe')! That's brilliant and I'll remember it for next time.

(7) By David Raymond (dvdraymond) on 2022-01-24 13:56:31 in reply to 5 [link] [source]

I had no idea you could do something like edit(a, 'notepad.exe')! That's brilliant and I'll remember it for next time.

Just as a reminder, I believe the edit() function is added in by the CLI, and is not in the normal library.

https://www.sqlite.org/cli.html#the_edit_sql_function

(6) By Gunter Hick (gunter_hick) on 2022-01-24 08:46:56 in reply to 1 [link] [source]

In SQLite 3.24 there is a TRANSACTION opcode in the prolog of the generated SQLite byte code. Executing the TRANSACTION bytecode will result in there being a write transaction active (started or upgraded), or the statement failing if it is not possible to do so. The code returned will reflect if the cause is a timeout or a deadlock or some kind of resource failure.

Note that without a transaction, any data read by a previous SELECT statement may already be stale.