Olivier, However, this is unique to SQLite. In "just about any other" database software you have to know whether you are in a transaction or not, because you *CANNOT* request/release/rollback a savepoint outside of a transaction. It just happens that SQLite runs each "statement" inside a transaction if you do not specify otherwise, so from the point view of the statement, it is occurring within a transaction. No other RDBMS that I am aware of on the planet will permit you to do this -- that is, you cannot start a transaction with a SAVEPOINT -- a SAVEPOINT is something you do "inside" a transaction to -- well -- save the point you are at (hence the name I would imagine).
So, in Sybase or MS SQL Server you would check the SQL Variable @@TRANCOUNT to know whether you need to explicitly start a transaction or not. In SQLite there is an API call (sqlite3_autocommit) that returns the same information. DB2, Oracle, PostGreSQL and so on all have ways for your code to know whether it is inside a transaction or not. The only real problem I see with SQLite3's implementation of SAVEPOINT is that if you issue SAVEPOINT outside of a transaction (ie, when in autocommit mode) it should throw an error, not start a transaction for you. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Olivier Mascia > Sent: Thursday, 13 April, 2017 08:53 > To: SQLite mailing list > Subject: Re: [sqlite] SAVEPOINT savepoint-name > [DEFERRED|IMMEDIATE/EXCLUSIVE]? > > Thanks Keith for your followup, I see my answer to Clemens helped focus > what I'm on. :) > > > Le 13 avr. 2017 à 16:11, Keith Medcalf <kmedc...@dessus.com> a écrit : > > > > You use the sqlite3_autocommit() to determine if you are in "magical" > mode or inside a transaction. > > Hmm. I failed to spot sqlite3_get_autocommit() as a way to detect if I > have an explicit transaction or not. Thanks for popping it up here! It > alleviates the need to pass on that information from caller to callee in > those cases where it is needed. > > > If you are in "magical" mode then you need to issue an appropriate BEGIN > to begin a transaction (with options such as IMMEDIATE if you want). > Though, how do you know when you are done (as in COMMIT). > > If some utility method is called without an explicit transaction setup and > the work to be done needs atomicity, then the utility method can do its > own BEGIN IMMEDIATE (that's what I need in those cases) _and_ COMMIT. > Clearly in that case, the caller didn't care for a greater context, else > it would have explicitly started a transaction before (and in my use case > that would have been an IMMEDIATE one). > > Merely coding a: > > SAVEPOINT svpt IMMEDIATE; > ... > RELEASE svpt; > > (if it existed) would be much more simple in those cases, instead of > detecting (by any means) if there is a proper explicit outer transaction > and locally do or not do BEGIN IMMEDIATE ... COMMIT. That was pretty much > all I was after. :) > > Thanks, > -- > Best Regards, Meilleures salutations, Met vriendelijke groeten, > Olivier Mascia, http://integral.software > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
