Re: [sqlite] SAVEPOINT with multiple databases
Thank you, David. Now it totally makes sense to me. I realize this is SQL not SQLite question. Thank you, Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of David Raymond [david.raym...@tomtom.com] Sent: Wednesday, May 17, 2017 12:38 PM To: SQLite mailing list Subject: Re: [sqlite] SAVEPOINT with multiple databases One other minor thing of note is that attaching and detaching can only happen outside of a transaction. So you can't add or remove attached databases in the middle of a transaction, and transaction commit/rollback, or savepoint release/rollback will never leave you with a different set of attached databases than before that statement. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Roman Fleysher Sent: Wednesday, May 17, 2017 12:25 AM To: SQLite mailing list Subject: Re: [sqlite] SAVEPOINT with multiple databases Oh, now i understand. Thank you, Simon. Roman Original message From: Simon Slavin Date: 5/16/17 5:35 PM (GMT-05:00) To: SQLite mailing list Subject: Re: [sqlite] SAVEPOINT with multiple databases On 16 May 2017, at 10:09pm, Roman Fleysher wrote: > I think I came to a point where I need to learn SAVEPOINTs. > > I am trying to understand documentation if creation and release of save > points covers all presently attached databases, that is those before save > point is created? Is attaching a database just a command that will sit on the > transaction stack as any other, INSERT/UPDATE/DELETE? It doesn’t matter when a database is attached to a connection. A savepoint is something which affects the current transaction for a connection, which is comprised of the INSERT/UPDATE/DELETE operations you mentioned. One SQLite connection: Has access to at least one database, maybe more. Has none or one current transaction. One transaction: Has zero or more savepoints. Simon. ___ 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-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] SAVEPOINT with multiple databases
One other minor thing of note is that attaching and detaching can only happen outside of a transaction. So you can't add or remove attached databases in the middle of a transaction, and transaction commit/rollback, or savepoint release/rollback will never leave you with a different set of attached databases than before that statement. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Roman Fleysher Sent: Wednesday, May 17, 2017 12:25 AM To: SQLite mailing list Subject: Re: [sqlite] SAVEPOINT with multiple databases Oh, now i understand. Thank you, Simon. Roman Original message From: Simon Slavin Date: 5/16/17 5:35 PM (GMT-05:00) To: SQLite mailing list Subject: Re: [sqlite] SAVEPOINT with multiple databases On 16 May 2017, at 10:09pm, Roman Fleysher wrote: > I think I came to a point where I need to learn SAVEPOINTs. > > I am trying to understand documentation if creation and release of save > points covers all presently attached databases, that is those before save > point is created? Is attaching a database just a command that will sit on the > transaction stack as any other, INSERT/UPDATE/DELETE? It doesn’t matter when a database is attached to a connection. A savepoint is something which affects the current transaction for a connection, which is comprised of the INSERT/UPDATE/DELETE operations you mentioned. One SQLite connection: Has access to at least one database, maybe more. Has none or one current transaction. One transaction: Has zero or more savepoints. Simon. ___ 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT with multiple databases
Oh, now i understand. Thank you, Simon. Roman Original message From: Simon Slavin Date: 5/16/17 5:35 PM (GMT-05:00) To: SQLite mailing list Subject: Re: [sqlite] SAVEPOINT with multiple databases On 16 May 2017, at 10:09pm, Roman Fleysher wrote: > I think I came to a point where I need to learn SAVEPOINTs. > > I am trying to understand documentation if creation and release of save > points covers all presently attached databases, that is those before save > point is created? Is attaching a database just a command that will sit on the > transaction stack as any other, INSERT/UPDATE/DELETE? It doesn’t matter when a database is attached to a connection. A savepoint is something which affects the current transaction for a connection, which is comprised of the INSERT/UPDATE/DELETE operations you mentioned. One SQLite connection: Has access to at least one database, maybe more. Has none or one current transaction. One transaction: Has zero or more savepoints. Simon. ___ 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] SAVEPOINT with multiple databases
On 16 May 2017, at 10:09pm, Roman Fleysher wrote: > I think I came to a point where I need to learn SAVEPOINTs. > > I am trying to understand documentation if creation and release of save > points covers all presently attached databases, that is those before save > point is created? Is attaching a database just a command that will sit on the > transaction stack as any other, INSERT/UPDATE/DELETE? It doesn’t matter when a database is attached to a connection. A savepoint is something which affects the current transaction for a connection, which is comprised of the INSERT/UPDATE/DELETE operations you mentioned. One SQLite connection: Has access to at least one database, maybe more. Has none or one current transaction. One transaction: Has zero or more savepoints. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SAVEPOINT with multiple databases
Dear SQLiters, I think I came to a point where I need to learn SAVEPOINTs. I am trying to understand documentation if creation and release of save points covers all presently attached databases, that is those before save point is created? Is attaching a database just a command that will sit on the transaction stack as any other, INSERT/UPDATE/DELETE? Thank you, Roman ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?
On 13 Apr 2017, at 6:51pm, Keith Medcalf wrote: > 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. A question on this list a few months ago prompted me to look up how transactions were originally intended to work. Originally, the specs said that if you issued a SQL command and hadn’t already executed BEGIN, you’d get an error back complaining that you’d tried to execute a command outside a transaction. Early implementations ignored this and if you executed a command outside a transaction they’d issue a BEGIN for you. However, they would not execute a COMMIT ! So if you just issued a stream of SQL commands then closed the connection, nothing would be committed. Documentation on this was spotty, since everyone assumed that you would be issuing a BEGIN, and the problem should hardly ever occur. Instead it mostly appeared as a FAQ: "My commands are accepted without errors, but nothing in the database changes. They’re obviously being processed because if I get entity names wrong I get an error message. What am I doing wrong ?". SQLite goes further: as well as doing the BEGIN it does the COMMIT. So instead of a FAQ we get a bunch of users who have never issued BEGIN or COMMIT and have no idea what they’re for. My conclusion is that in all those implementations issuing SAVEPOINT outside a transaction is always wrong. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?
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 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
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 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
Re: [sqlite] SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?
In a very large program, made of a lot of 'subsystems', some methods might have to do some read and update work, sometimes being called from contexts where there already is an OUTER transaction open, and sometimes being called with no OUTER transactions open. If I could be satisfied with a DEFERRED transaction, programming those sub-parts so that they use SAVEPOINT ... RELEASE would be OK (right wether or not there already is an OUTER transaction (BEGIN ... COMMIT) setup by the caller). But when the job made by such sub-parts need a BEGIN IMMEDIATE before doing its reads and updates, SAVEPOINT can't elegantly be used for that. So the caller has to pass on to the sub-parts wether it already has a transaction or not, such that the subparts know wether to do their own BEGIN ... COMMIT or not. There is nothing wrong in having to do that and that works fine of course. It just pollutes some interfaces and being able to rely on an extended SAVEPOINT would keep it simpler. You use the sqlite3_autocommit() to determine if you are in "magical" mode or inside a transaction. 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). I would also point out that if you do not know whether you are inside a transaction or not, then you have other problems. How do you know when you are "done" if you don't know when to BEGIN? So I suppose you could use normal method that you would have to do with every other SQL Database enginer on the planet: outer_transaction = False if sqlite3_autocommit() == 0: outer_transaction = True if outer_transaction: SAVEPOINT xx; else: BEGIN IMMEDIATE ... do stuff ... if outer_transaction: RELEASE xx; else: COMMIT; -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?
On 13 Apr 2017, at 2:39pm, Olivier Mascia wrote: > Known, but thanks. :) > The question was why SAVEPOINT, when there is no outer transaction, is > limited to the behavior of simple BEGIN (which is BEGIN DEFERRED) and does > not offer provision for IMMEDIATE or EXCLUSIVE mode (I'm only interested in > IMMEDIATE for the use-case I have in mind)? Just as with any other SQL command which accesses data, you’re not meant to start a transaction by declaring a savepoint. SAVEPOINT should not be acquiring locks. You should have already done a BEGIN to acquire the locks you want. SQLite expects you to do BEGIN SAVEPOINT RELEASE SAVEPOINT COMMIT Just as with any other SQL command, if you forget to do your BEGIN, SQLite wraps your command in BEGIN DEFERRED […] END , but that’s a convenience, not something . If you prefer your lock to be EXCLUSIVE or IMMEDIATE you can specify it in the BEGIN command. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?
The traditional use of SAVEPOINT is to save a spot "inside" a transaction. Almost all other implementations of SQL REQUIRE a transaction to already be in progress before you can create a savepoint (which makes total sense -- how can you save the state of something which does not exist?). SQLite also requires a transaction to be in progress to create a savepoint. However, when in autocommit mode (that is, you have not issued an explicit "BEGIN" statement) each statement is executed inside a magical transaction, so the SAVEPOINT statement is processed within a transaction (albeit a magical one) and it is the state of the magical transaction which is "saved". BEGIN ... ... do some stuff .. SAVEPOINT x -- save this spot in the transaction ... do some more stuff ... ROLLBACK to x -- ooops, undo that stuff and do this instead ... alternate stuff ... COMMIT; As someone else commented, if you do not know whether or not you are inside a transaction you have much bigger, more deeply seated, issues. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?
Clemens, Friendly said, you might have missed my point, which probably simply demonstrate I failed stating it correctly. > Le 13 avr. 2017 à 14:14, Clemens Ladisch a écrit : > > Olivier Mascia wrote: >> "When a SAVEPOINT is the outer-most savepoint and it is not within >> a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED >> TRANSACTION." >> >> What are the specific reasons for SAVEPOINT to be limited to BEGIN >> DEFERRED in that case? > > A BEGIN without a type is DEFERRED by default. Known, but thanks. :) The question was why SAVEPOINT, when there is no outer transaction, is limited to the behavior of simple BEGIN (which is BEGIN DEFERRED) and does not offer provision for IMMEDIATE or EXCLUSIVE mode (I'm only interested in IMMEDIATE for the use-case I have in mind)? >> Could an optional syntax extension allow to specify IMMEDIATE (and >> maybe EXCLUSIVE)? > > That syntax extension already exists. To specify the transaction type, > write if _before_ the "SAVEPOINT", and surround it with "BEGIN" and ";": > > BEGIN IMMEDIATE; > SAVEPOINT name; This is unrelated to my question. It would start a new outer transaction, doomed to fail if there is already another one. >> When a "SAVEPOINT name [DEFERRED|IMMEDIATE|EXCLUSIVE]" is the outer- >> most savepoint and it is not within a BEGIN...COMMIT then the behavior >> is the same as BEGIN DEFERRED|IMMEDIATE|EXCLUSIVE TRANSACTION. >> >> In all other cases, the behavior will keep or upgrade the locks when >> required and permitted, or fail (SQLITE_BUSY I think). > > This would be inconsistent if the outer transaction is already open and > has a different type. Not necessarily. OUTER INNER OUTCOME NONE DEFERRED Fine, this is current behavior. NONE IMMEDIATE Fine, would be equivalent to BEGIN IMMEDIATE. NONE EXCLUSIVE Fine, would be equivalent to BEGIN EXCLUSIVE. DEFERRED DEFERRED The state 'NONE'-locks or 'SHARED'-locks stay as they are. DEFERRED IMMEDIATE Fine, as long as locks can be upgraded to RESERVED. (This is the same thing as a DEFERRED transaction attempting an update at some point). DEFERRED EXCLUSIVE Attempts to update 'NONE' or 'SHARED' locks to EXCLUSIVE. Might have to fail with SQLITE_BUSY. IMMEDIATE DEFERRED The RESERVED locks are already in effect. IMMEDIATE IMMEDIATE The RESERVED locks are already in effect. IMMEDIATE EXCLUSIVE Attempts to upgrade the RESERVED locks to EXCLUSIVE. Might have to fail with SQLITE_BUSY. EXCLUSIVE ANY OF 3 We're already in an EXCLUSIVE context, which can be preserved. The idea is to keep at least the current transaction locks state, and upgrade it, if requested and succeeds. Never to downgrade an existing transaction. > (I don't think that relying on the automatic BEGIN would be a good idea. > If your program does not know whether there is an active transaction, it > already has problems.) In a very large program, made of a lot of 'subsystems', some methods might have to do some read and update work, sometimes being called from contexts where there already is an OUTER transaction open, and sometimes being called with no OUTER transactions open. If I could be satisfied with a DEFERRED transaction, programming those sub-parts so that they use SAVEPOINT ... RELEASE would be OK (right wether or not there already is an OUTER transaction (BEGIN ... COMMIT) setup by the caller). But when the job made by such sub-parts need a BEGIN IMMEDIATE before doing its reads and updates, SAVEPOINT can't elegantly be used for that. So the caller has to pass on to the sub-parts wether it already has a transaction or not, such that the subparts know wether to do their own BEGIN ... COMMIT or not. There is nothing wrong in having to do that and that works fine of course. It just pollutes some interfaces and being able to rely on an extended SAVEPOINT would keep it simpler. Obviously this extension of SAVEPOINT renders BEGIN redundant, a program might as well use only SAVEPOINT for any level of transaction (OUTER or INNER): that isn't a goal in itself of my idea/question, just a side-effect. I think, and hope I'm not mistaken, it would have no impact on any existing SQL code ignorant of the IMMEDIATE/EXCLUSIVE keyword extension to SAVEPOINT. Though code using the possibly new syntax extension, wouldn't obviously run on previous versions. PS: I live in a programming world where SQLite WAL journal-mode is the only conceivable and used mode. My thinking might be biased by not working with non-WAL SQLite databases. -- 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
Re: [sqlite] SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?
Olivier Mascia wrote: > "When a SAVEPOINT is the outer-most savepoint and it is not within > a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED > TRANSACTION." > > What are the specific reasons for SAVEPOINT to be limited to BEGIN > DEFERRED in that case? A BEGIN without a type is DEFERRED by default. > Could an optional syntax extension allow to specify IMMEDIATE (and > maybe EXCLUSIVE)? That syntax extension already exists. To specify the transaction type, write if _before_ the "SAVEPOINT", and surround it with "BEGIN" and ";": BEGIN IMMEDIATE; SAVEPOINT name; > When a "SAVEPOINT name [DEFERRED|IMMEDIATE|EXCLUSIVE]" is the outer- > most savepoint and it is not within a BEGIN...COMMIT then the behavior > is the same as BEGIN DEFERRED|IMMEDIATE|EXCLUSIVE TRANSACTION. > > In all other cases, the behavior will keep or upgrade the locks when > required and permitted, or fail (SQLITE_BUSY I think). This would be inconsistent if the outer transaction is already open and has a different type. (I don't think that relying on the automatic BEGIN would be a good idea. If your program does not know whether there is an active transaction, it already has problems.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?
Dear all and SQLite Developers, About: https://www.sqlite.org/lang_savepoint.html "The SAVEPOINT command starts a new transaction with a name. The transaction names need not be unique. A SAVEPOINT can be started either within or outside of a BEGIN...COMMIT. When a SAVEPOINT is the outer-most savepoint and it is not within a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED TRANSACTION." What are the specific reasons for SAVEPOINT to be limited to BEGIN DEFERRED in that case? Could an optional syntax extension allow to specify IMMEDIATE (and maybe EXCLUSIVE)? SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE|EXCLUSIVE] (with DEFERRED the default)? When a "SAVEPOINT name [DEFERRED|IMMEDIATE|EXCLUSIVE]" is the outer-most savepoint and it is not within a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED|IMMEDIATE|EXCLUSIVE TRANSACTION. In all other cases, the behavior will keep or upgrade the locks when required and permitted, or fail (SQLITE_BUSY I think). I suppose if it wasn't part of SAVEPOINT behaviors at first, it's because there are (unsuspected by me) complex/challenging hidden issues (code size/complexity) or maybe it slips too far away from whatever standard? -- 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
Re: [sqlite] Savepoint Questions
On 5/27/16, Paul Medynski wrote: > > begin transaction; > ... Work A ... > savepoint Foo; > ... Work B ... > savepoint Foo;<--- Allowed by #1 > ... Work C ... > rollback to savepoint Foo;<--- Rolls back Work C, but leaves the >innermost savepoint Foo active due >to #2 > > rollback to savepoint Foo;<--- Does nothing - I can no longer >rollback past the innermost Foo >without rolling back the entire >transaction. Foo is still on the stack twice. If you want to go back to the first one, you have to "RELEASE" the second one: RELEASE foo; -- equivalent to "ROLLBACK foo" since there have been no changes ROLLBACK TO foo; -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Savepoint Questions
Hello all, I have some questions and comments about savepoints, documented here: https://www.sqlite.org/lang_savepoint.html Here are some excerpts from that documentation that are relevant to my questions/comments: 1. "The SAVEPOINT command starts a new transaction with a name. The transaction names need not be unique." 2. "The ROLLBACK command with a TO clause rolls back transactions going backwards in time back to the most recent SAVEPOINT with a matching name. The SAVEPOINT with the matching name remains on the transaction stack, but all database changes that occurred after that SAVEPOINT was created are rolled back." Also (not documented on that page): 3. Savepoint names cannot be parameterized in statements, which means you cannot prepare a savepoint statement ahead of time, and supply the savepoint name at execution time. Through some experimentation (using v3.11.0), I have found that #1 and #2 together don't work very well: begin transaction; ... Work A ... savepoint Foo; ... Work B ... savepoint Foo;<--- Allowed by #1 ... Work C ... rollback to savepoint Foo;<--- Rolls back Work C, but leaves the innermost savepoint Foo active due to #2 rollback to savepoint Foo;<--- Does nothing - I can no longer rollback past the innermost Foo without rolling back the entire transaction. Questions: A) If #2 is desirable, then why allow non-unique savepoint names within the same transaction? It is impossible to rollback the innermost savepoint with a non-unique name, and later rollback past that savepoint using the same name. B) If #1 is desirable, then why doesn't ROLLBACK TO pop the matched savepoint off the stack? Even without considering #1, I'm having trouble coming up with a scenario in which I would want the matched savepoint to remain active. C) If #1 and #2 are desirable, then why can't I parameterize savepoint names? This would allow me to prepare my savepoint statements once, and execute them with different names, gaining the efficiencies of prepared statements for these oft-used actions. Currently, I am forced to generate unique savepoint names _and_ prepare new statements every time I want to create a new savepoint, which is doubly inefficient. Thoughts? -Paul ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SAVEPOINT name
On 21 May 2015, at 8:51pm, Baruch Burstein wrote: > Does that mean that I can't prepare this: "SAVEPOINT :name"? What would be > the recommended method of preventing SQL injection for this? Is there really a need to use ad-hoc savepoint names ? You can keep reusing the same name during successive savepoints, or you can use several different names, but would you want to make up names inside your program ? Simon.
Re: [sqlite] Savepoint and Begin Transaction, performance-wise
On Fri, May 17, 2013 at 9:06 AM, Paolo Bolzoni < paolo.bolzoni.br...@gmail.com> wrote: > In my programs I often use savepoints so in case > of errors I can rollback leaving the db untouched. > > I use savepoint instead of begin transaction because > it fits more naturally in the nested structure of a C > program. > When I write a function I do not need to recall if the > caller already opened the transaction. > When the last savepoint is released the data is safely > written in the db. > > First of all, did I understand correctly? > yes. > Secondly, is there a significant difference of performance > to use nested savepoints instead of a single transaction? > Yes. SQLite must open a new rollback journal for each nested savepoint. These secondary rollback journals do not need to be synced like the primary journal (or WAL file) so they are not nearly as expensive. But neither are they free. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Savepoint and Begin Transaction, performance-wise
In my programs I often use savepoints so in case of errors I can rollback leaving the db untouched. I use savepoint instead of begin transaction because it fits more naturally in the nested structure of a C program. When I write a function I do not need to recall if the caller already opened the transaction. When the last savepoint is released the data is safely written in the db. First of all, did I understand correctly? Secondly, is there a significant difference of performance to use nested savepoints instead of a single transaction? Thanks Paolo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] savepoint
Many thanks, it would help. Csaba 2012/1/16 Vivien Malerba > 2012/1/16 Csaba Jeney > > > Is there any way to query the valid savepoints? At least their names? > > Many thanks. > > > > > AFAIK, the only way to do this is to keep track of each begin, rollback, > add savepoint, ... executed, to always know where you are. > (Anyway this is what's done in Libgda). > > Regards, > > Vivien > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Yours sincerely Csaba Jeney Head of R&D GenoID Ltd. GenoID Molekulárbiológiai Laboratórium 1528, Budapest, Szanatórium utca 19. Phone.: +36 1 465 0124 Mobil: +36 70 454 3223 Fax: +36 1 465 0127 Email: csje...@genoid.hu http://www.genoid.hu , http://www.hpvteszt.hu This message is intended to be confidential and may be privileged. If you are not the intended recipient, please delete this e-mail from your system immediately and notify us of the erroneous transmission to you. Any unauthorized disclosure, use, copying, or distribution of this E-mail is prohibited. Please consider your environmental responsibility before printing this e-mail. Ez az üzenet és bármely melléklete kizárólag a címzettnek szól. Tartalmazhat bizalmas, szellemi tulajdonnak minősülő illetve más jogvédett információkat vagy egyéb módon lehet védve jogszabályok által. Ha nem Ön a szándékozott címzettje, nem használhatja fel,nem hozhatja nyilvánosságra és nem másolhatja ezt az üzenetet, annak mellékletét vagy bármely részét, és nem cselekedhet az üzenet tartalmában bízva. Ebben az esetben kérjük, értesítse a feladót haladéktalanul, és törölje ezt az üzenetet és minden mellékletét rendszerébõl. A levél és tartalmának, felhatalmazás nélküli, másolása, arhíválása jogellenes. A levél nyomtatásakor, kérjük, vegye figyelembe a környezetünk védelmét. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] savepoint
2012/1/16 Csaba Jeney > Is there any way to query the valid savepoints? At least their names? > Many thanks. > > AFAIK, the only way to do this is to keep track of each begin, rollback, add savepoint, ... executed, to always know where you are. (Anyway this is what's done in Libgda). Regards, Vivien ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] savepoint
On 01/16/2012 03:31 PM, Csaba Jeney wrote: Is there any way to query the valid savepoints? At least their names? No way to do that at present. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] savepoint
Is there any way to query the valid savepoints? At least their names? Many thanks. Csaba This message is intended to be confidential and may be privileged. If you are not the intended recipient, please delete this e-mail from your system immediately and notify us of the erroneous transmission to you. Any unauthorized disclosure, use, copying, or distribution of this E-mail is prohibited. Please consider your environmental responsibility before printing this e-mail. Ez az üzenet és bármely melléklete kizárólag a címzettnek szól. Tartalmazhat bizalmas, szellemi tulajdonnak minősülő illetve más jogvédett információkat vagy egyéb módon lehet védve jogszabályok által. Ha nem Ön a szándékozott címzettje, nem használhatja fel,nem hozhatja nyilvánosságra és nem másolhatja ezt az üzenetet, annak mellékletét vagy bármely részét, és nem cselekedhet az üzenet tartalmában bízva. Ebben az esetben kérjük, értesítse a feladót haladéktalanul, és törölje ezt az üzenetet és minden mellékletét rendszerébõl. A levél és tartalmának, felhatalmazás nélküli, másolása, arhíválása jogellenes. A levél nyomtatásakor, kérjük, vegye figyelembe a környezetünk védelmét. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] savepoint error
I have been experiencing a strange issue with savepoint error for the library version 3.6.22. I have the following code: char* sql = "SAVEPOINT sp;"; char* err; slim_int ret = sqlite3_exec(sqlitehandle, sql, NULL, NULL, &err); The ret is 1 which is SQLITE_ERROR and err = "near \"sp\": syntax error" I reckon this issue has been raised before by somebody too and the upgrade to the latest version has resolved this problem. Yet, I was surprised to see this issue with 3.6.22 with threadsafe enabled May be someone throw some more light on it. Regards, -Akbar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT : Seems don't work : SOLVED (version problem)
Very good answer : That was the problem; I had 3.6.4 version : It works with the last version Thank's a lot Dan MaxMax14 -Message d'origine- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]de la part de Dan Envoye : mercredi 25 fevrier 2009 05:28 A : General Discussion of SQLite Database Objet : Re: [sqlite] SAVEPOINT : Seems don't work On Feb 25, 2009, at 2:31 AM, REPKA_Maxime_NeufBox wrote: > I try for test as follow : > > on DOS : sqlite3.exe Database > > then type : SAVEPOINT spoint; > Error message is : 'SQL error :near "savepoint": syntax error' Probably you need to upgrade. SAVEPOINT first appeared in 3.6.8. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT : Seems don't work
On Feb 25, 2009, at 2:31 AM, REPKA_Maxime_NeufBox wrote: > I try for test as follow : > > on DOS : sqlite3.exe Database > > then type : SAVEPOINT spoint; > Error message is : 'SQL error :near "savepoint": syntax error' Probably you need to upgrade. SAVEPOINT first appeared in 3.6.8. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT : Seems don't work
I try for test as follow :
on DOS : sqlite3.exe Database
then type : SAVEPOINT spoint;
Error message is : 'SQL error :near "savepoint": syntax error'
I tried 'spoint', "spoint", (spoint), ('spoint'), ("spoint") : no syntax
works
With same test it works with Begin, commit and rollback
-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]de la part de Dan
Envoye : mardi 24 fevrier 2009 05:23
A : General Discussion of SQLite Database
Objet : Re: [sqlite] SAVEPOINT : Seems don't work
On Feb 24, 2009, at 3:02 AM, REPKA_Maxime_NeufBox wrote:
> Hello,
>
> I am quite new to use SQLite
>
> I tried to use SAVEPOINT command but didn't succeed
>
> Does this command work ? how ?
We hope so. What happened to indicate it did not succeed? In what
way did the SAVEPOINT command malfunction?
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT : Seems don't work
On Feb 24, 2009, at 3:02 AM, REPKA_Maxime_NeufBox wrote: > Hello, > > I am quite new to use SQLite > > I tried to use SAVEPOINT command but didn't succeed > > Does this command work ? how ? We hope so. What happened to indicate it did not succeed? In what way did the SAVEPOINT command malfunction? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT : Seems don't work
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 REPKA_Maxime_NeufBox wrote: > I tried to use SAVEPOINT command but didn't succeed > > Does this command work ? how ? It certainly works in my testing and works in the SQLite team testing - http://sqlite.org/testing.html Chances are you haven't got your code correct, haven't called sqlite3_backup_finish or aren't checking error returns. There is complete documentation including example code at: http://www.sqlite.org/c3ref/backup_finish.html http://www.sqlite.org/backup.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkmjE/EACgkQmOOfHg372QSK8ACdGutrAfFeJiiDCrW36rluhpgj HroAoJfPsFICIZTZase+x96RNcNVvw8T =YRlb -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SAVEPOINT : Seems don't work
Hello, I am quite new to use SQLite I tried to use SAVEPOINT command but didn't succeed Does this command work ? how ? MaxMax14 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
