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 <clem...@ladisch.de> 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
