SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Olivier Mascia
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Olivier Mascia
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Keith Medcalf

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 xxxxx -- save this spot in the transaction
... do some more stuff ...
ROLLBACK to xxxxx -- 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Simon Slavin-3
In reply to this post by Olivier Mascia

On 13 Apr 2017, at 2:39pm, Olivier Mascia <[hidden email]> 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 <whatever>
    <commands go here>
    SAVEPOINT <name>
        <commands go here>
    RELEASE SAVEPOINT <name>
    <commands go here>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Keith Medcalf
In reply to this post by Olivier Mascia
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 xxxxxx;
else:
  BEGIN IMMEDIATE
... do stuff ...
if outer_transaction:
  RELEASE xxxxxx;
else:
  COMMIT;

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Olivier Mascia
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Keith Medcalf

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:[hidden email]]
> 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Simon Slavin-3

On 13 Apr 2017, at 6:51pm, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...