SQLite Forum

Draft Doc on SQLite SAVEPOINT, and its internal implementation
Login
Wout Mertens (wmertens) on 2020-09-30 10:14:39:

> Upon reading this, I'm missing an explicit pointing out of the surprising difference that is promised in the beginning, and the standard definition that other database engines are implementing instead.

I found it cool that SQLite users can decide never to use BEGIN/COMMIT\|END again, unless they want IMMEDIATE (==EXCLUSIVE in WAL mode). I suppose if I had been a SAVEPOINT user for years I would have been less surprised, but my introduction to SAVEPOINT was reading the code in src/pager.c .

By allowing all transactions to be (a) named and (b) nestable without changing syntax depending on context, then SQLite is enabling an improvement in SQL code that does not exist elsewhere and I think that is good.

MS SQL Server does allow BEGIN/END to be nested (as SQLite easily could, but chooses not to). All other databases I tried or whose documentation I read implement nested transactions according to the SQL standard, which means only by means of SAVEPOINT within a BEGIN block. That means the SQL developer has a context-dependent change in syntax, and cannot name the outer transaction.

Dan Shearer