SQLite Forum

cannot start a transaction within a transaction using sqlite shell
> I agree that that is what is happening, but I don't think that's what's supposed to happen.

Supposing is a funny thing, kind of like beauty being in the eye of the beholder. I suppose, (and I'm pretty sure Richard supposes), that what you are seeing is exactly what has been designed to happen. I believe your supposition needs to adapt to what Richard has said is likely happening, which is occurring in detail for the reason Keith says (and I confirm.)

> My understanding was that transactions are distinct to an individual process.

Processes have nothing to do with it, except as something which affects implementation of the documented behavior (which is what you are seeing.) SQLite "connections" (aka pointers to opaque sqlite3 objects) are what map one-to-(zero or one) to transactions.

> Also, note that even with the DELETE running in an explicit transaction, the same thing happens.

Whether the other connection interfering with your insert sequence is working under explicit or automatic implicit transaction has no bearing on the problem. The same thing is happening with that connection's access to the DB either way.

> Looking at the documentation \[about transaction concurrency\]

This has no bearing on the problem you see. The problem here is relatively simple, with the main complication arising from how the shell processes statement groups.