On 18 Nov 2019, at 7:37pm, Amirouche Boubekki <amirouche.boube...@gmail.com> wrote:
> I am looking for use-cases for nested transactions. When are nested > transactions useful in a single writer context? SQLite doesn't support nested transactions. If you think you want them, you probably want savepoints instead: <https://sqlite.org/lang_savepoint.html> But are you asking when you should use BEGIN as opposed to just issuing a single command like INSERT by itself ? Well, given the 'single writer' you put in the question, you're not worried about two connections interfering with one-another. So the answer is about cancelling changes, backing out changes, or about what happens if the system crashes or loses power. Suppose I issue two commands which, together, change a row of data. I delete an old row and insert a new one to replace it. If these are two separate commands, SQLite helpfully handles both commands as separate transactions. And it's possible that a power failure or software crash will happen between the two, so you may end up with no row at all: the old one was deleted and the new one was not inserted. However, if you do this: BEGIN DELETE FROM ... INSERT INTO ... END Then no kind of corruption, crash or power loss should be able to do that. Either neither of the commands will have an effect, or they both will. So no matter how the data is found or recovered, it will have either the old or the new data in. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
