Re: [sqlite] Handling ROLLBACK
Actually, in current versions of SQLite3 the error "(516) SQLITE_ABORT_ROLLBACK" is returned by "other commands" and not by the rollback command. If the ROLLBACK command completed successfully (that is, it returned YaHoo! SQLITE_OK) then the transaction changes have been rolled back. If "some other command" returns an error indicating that it was ABORTed then that other command was aborted and that has no bearing on whether or not changes to the database were rolled back. They were. I presume that if a SELECT was in progress during the time that a ROLLBACK was processed on the SAME CONNECTION and that the SELECT is allowed to proceed, that it will maintain the REPEATABLE READ isolation that it was commenced with and that when that statement is finalized, the transaction context will be released. Whether the connection is still "inside" the transaction or not can be determined by using the sqlite3_autocommit() API. Since this is kind of an undocumented grey area, I would suggest that you do not make the programming errors which would result in this situation (a Schrodinger Transaction state, for lack of a better descriptive). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Saturday, 2 March, 2019 19:32 >To: SQLite mailing list >Subject: Re: [sqlite] Handling ROLLBACK > > >>Suppose ROLLBACK does not cancel the BEGIN, can a programmer >reliably >>issue more SQL commands, including another ROLLBACK ? Will SQLite >>continue to react correctly to other ROLLBACKs, and to SQL commands >>which result in "(516) SQLITE_ABORT_ROLLBACK". > >A successful ROLLBACK on a transaction discards all changes that were >made during the transaction (rolls back to the state at the time the >transaction began), and ends the transaction. It does not "cancel" >the "BEGIN", it merely exits the transaction and discards changes >made during the transaction. Since the BEGIN occurred in the past, >it cannot be "cancelled" ... > >Full Stop End of Line. > >However, a ROLLBACK command which produces the error "(516) >SQLITE_ABORT_ROLLBACK" would indicate that the ROLLBACK was NOT >PERFORMED due to an error. Therefore the transaction is STILL IN >EFFECT and has neither been COMMITed nor ROLLBACKed. Once the >programming error which prevented the rollback is cleared the >ROLLBACK command can be retried. Once a transaction is opened it >remains in effect until ROLLBACK or COMMIT complete successfuly (or >the heat death of the universe, whichever shall come first). > >Note that this discussion does not include "fatal errors" which may >occur within the context of a transaction that abort the transaction >itself. > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > > >___ >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
Re: [sqlite] Handling ROLLBACK
>Suppose ROLLBACK does not cancel the BEGIN, can a programmer reliably >issue more SQL commands, including another ROLLBACK ? Will SQLite >continue to react correctly to other ROLLBACKs, and to SQL commands >which result in "(516) SQLITE_ABORT_ROLLBACK". A successful ROLLBACK on a transaction discards all changes that were made during the transaction (rolls back to the state at the time the transaction began), and ends the transaction. It does not "cancel" the "BEGIN", it merely exits the transaction and discards changes made during the transaction. Since the BEGIN occurred in the past, it cannot be "cancelled" ... Full Stop End of Line. However, a ROLLBACK command which produces the error "(516) SQLITE_ABORT_ROLLBACK" would indicate that the ROLLBACK was NOT PERFORMED due to an error. Therefore the transaction is STILL IN EFFECT and has neither been COMMITed nor ROLLBACKed. Once the programming error which prevented the rollback is cleared the ROLLBACK command can be retried. Once a transaction is opened it remains in effect until ROLLBACK or COMMIT complete successfuly (or the heat death of the universe, whichever shall come first). Note that this discussion does not include "fatal errors" which may occur within the context of a transaction that abort the transaction itself. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling ROLLBACK
BEGIN TRANSACTION ... issued commands inside transaction COMMIT or ROLLBACK -- transaction is now closed That is, BEGIN "opens" a transaction COMMIT "closes" the transaction in progress and saves the changes made during that transaction. ROLLBACK "closes" the transaction in progress and discards the changes made during that transaction. While a transaction is "in effect", the "view" of the database is stable relative to all other CONNECTIONS to the database. Changes made to the database in transactions on OTHER CONNECTIONS are not visible, and *unless you specify otherwise* you have REPEATABLE READ isolation. Within a transaction you may nest SAVEPOINTs. Savepoints are transactions within transactions and can be viewed as a stack. If no TRANSACTION is in process then BEGIN TRANSACTION and SAVEPOINT are identical -- that is, a SAVEPOINT can only exist within a transaction and a SAVEPOINT where no transaction is in progress will start an "implicit" transaction if necessary. SAVEPOINT -- create a "context" named "x" (and an "implicit" transaction if one is not already in progress) ROLLBACK TO -- rollback the "context" to the state that it was in when the context was created. Do not pop the context off the stack but pop any contexts off the stack that were created "after" context was created. You are still in a transaction and the context still exists. RELEASE -- commit all the changes that occurred after the specified context to the outer context. Pop from the context stack and also pop any contexts created after context was declared (that is, all changes made after the declaration of the context are "moved into" the next higher context and all references to the named context and contained contexts are removed. If you have "RELEASED" the last SAVEPOINT (ie, the first on the stack) and the containing transaction was created implicitly (rather than explicitly with BEGIN), then that implicit outer transaction is also committed and no transaction is any longer in effect. If the out transaction was explicitly commenced (with BEGIN) then that transaction is still in effect and must be COMMITTED or ROLLBACKed in order to end it. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Saturday, 2 March, 2019 16:12 >To: SQLite mailing list >Subject: [sqlite] Handling ROLLBACK > >I have run experiments in the shell tool, using different journal >modes, but I would like to know whether my observations are cannon or >just one-offs. Contributions welcome from all, especially those who >have read SQLite source code, and SQL language-lawyers whether or not >you're familiar with how other SQL implementations work. > > >Does ROLLBACK release the transaction lock on the database ? > >Does ROLLBACK cancel the BEGIN ? Or do I need to issue END ? > >Suppose ROLLBACK does not cancel the BEGIN, can a programmer reliably >issue more SQL commands, including another ROLLBACK ? Will SQLite >continue to react correctly to other ROLLBACKs, and to SQL commands >which result in "(516) SQLITE_ABORT_ROLLBACK". > > >If you think I've missed a relevant point, please don't hesitate to >bring it up. >___ >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
Re: [sqlite] Handling ROLLBACK
No On Sat, Mar 2, 2019, 18:50 D Burgess wrote: > Does ROLLBACK release the transaction lock on the database ? > > I checked scripts on this. Yes. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Cheers, Chris ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling ROLLBACK
Does ROLLBACK release the transaction lock on the database ? I checked scripts on this. Yes. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling ROLLBACK
if I understand the question: On Sun, Mar 3, 2019 at 10:12 AM Simon Slavin wrote: > Does ROLLBACK release the transaction lock on the database ? > I think so or my code would not be working. > > Does ROLLBACK cancel the BEGIN ? Or do I need to issue END ? > END is a synonym for commit . So begin transaction; stuff commit OR rollback. rollback cancels the begin > > Suppose ROLLBACK does not cancel the BEGIN, can a programmer reliably issue more SQL commands, including another ROLLBACK ? Will SQLite continue to react correctly to other ROLLBACKs, and to SQL commands which result in "(516) SQLITE_ABORT_ROLLBACK". > > > If you think I've missed a relevant point, please don't hesitate to bring > it up. > ___ > 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
[sqlite] Handling ROLLBACK
I have run experiments in the shell tool, using different journal modes, but I would like to know whether my observations are cannon or just one-offs. Contributions welcome from all, especially those who have read SQLite source code, and SQL language-lawyers whether or not you're familiar with how other SQL implementations work. Does ROLLBACK release the transaction lock on the database ? Does ROLLBACK cancel the BEGIN ? Or do I need to issue END ? Suppose ROLLBACK does not cancel the BEGIN, can a programmer reliably issue more SQL commands, including another ROLLBACK ? Will SQLite continue to react correctly to other ROLLBACKs, and to SQL commands which result in "(516) SQLITE_ABORT_ROLLBACK". If you think I've missed a relevant point, please don't hesitate to bring it up. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
