Re: [sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

[ext-Esko.Hujanen]

>On 9 May 2012, at 10:33am,  wrote:
 
>> 1: SAVEPOINT 1
>> 2: insert A
>> 3: RELEASE SAVEPOINT 1
 
>> 4: SAVEPOINT 2
>> 5: insert B
>> 6: ROLLBACK TO SAVEPOINT 2 
>> 
>> ROLLBACK in line 6 cancels also line 2 in addition to line 5.
>> 
>> I want to cancel just line 5.
 
>What it does depends on whether there's a 'BEGIN' before your step one,
but see
 
>
 
>"The RELEASE of an inner transaction does not cause any changes to be
written 
>to the database file; it merely removes savepoints from the transaction
stack 
>such that it is no longer possible to ROLLBACK TO those savepoints."
 
>I think you want transactions, not rollbacks:
 
>1: BEGIN TRANSACTION
>2: insert A
>3: COMMIT TRANSACTION
 
>4: BEGIN TRANSACTION
>5: insert B
>6: ROLLBACK TRANSACTION
 
>Simon.w

 

Hi All,

 

Found a solution, so case is closed.

Solution was, that sequence needed a COMMIT at the end.

 

Real situation were a tad complex, but in principle, this works:

 

1: SAVEPOINT 1
2: insert A
3: RELEASE SAVEPOINT 1
4: SAVEPOINT 2
5: insert B
6: ROLLBACK TO SAVEPOINT 2
7: COMMIT
8: Dump the db to disk for inspecting with sqlitebrowser
 
Previously I was dumping in the db too early in the code,
and that caused confusion when inspecting.
 
But thanks Simon and Richard for your comments,
they showed that I was in the right track
(I'm a newbie in SQLite).

 

br, Esko




Please note: This e-mail may contain confidential information
intended solely for the addressee. If you have received this
e-mail in error, please do not disclose it to anyone, notify
the sender promptly, and delete the message from your system.
Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

[Simon Slavin]

On 9 May 2012, at 10:33am,  wrote:

> 1: SAVEPOINT 1
> 2: insert A
> 3: RELEASE SAVEPOINT 1

> 4: SAVEPOINT 2
> 5: insert B
> 6: ROLLBACK TO SAVEPOINT 2 
> 
> ROLLBACK in line 6 cancels also line 2 in addition to line 5.
> 
> I want to cancel just line 5.

What it does depends on whether there's a 'BEGIN' before your step one, but see



"The RELEASE of an inner transaction does not cause any changes to be written 
to the database file; it merely removes savepoints from the transaction stack 
such that it is no longer possible to ROLLBACK TO those savepoints."

I think you want transactions, not rollbacks:

1: BEGIN TRANSACTION
2: insert A
3: COMMIT TRANSACTION

4: BEGIN TRANSACTION
5: insert B
6: ROLLBACK TRANSACTION

Simon.w
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

[Richard Hipp]

On Wed, May 9, 2012 at 5:33 AM,  wrote:

> Hi All,
>
>
>
> 1: SAVEPOINT 1
>
> 2: insert A
>
> 3: RELEASE SAVEPOINT 1
>
> 4: SAVEPOINT 2
>
> 5: insert B
>
> 6: ROLLBACK TO SAVEPOINT 2
>
>
>
> ROLLBACK in line 6 cancels also line 2 in addition to line 5.
>
> I want to cancel just line 5.
>

I did this:

CREATE TABLE t1(x);
INSERT INTO t1 VALUES('one');
SAVEPOINT x1;
INSERT INTO t1 VALUES('two');
RELEASE x1;
SAVEPOINT x2;
INSERT INTO t1 VALUES('three');
ROLLBACK TO SAVEPOINT x2;
SELECT * FROM t1;

And the output is "one" and "two", as you would expect.

Can you provide an actual test case for the problem you are seeing?



>
>
>
> What am I doing wrong ?
>
>
>
> br,
>
> Esko
>
>
>
> 
> Please note: This e-mail may contain confidential information
> intended solely for the addressee. If you have received this
> e-mail in error, please do not disclose it to anyone, notify
> the sender promptly, and delete the message from your system.
> Thank you.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

[Simon Davies]

On 9 May 2012 10:33,   wrote:
> Hi All,
>
> 1: SAVEPOINT 1
> 2: insert A
> 3: RELEASE SAVEPOINT 1
> 4: SAVEPOINT 2
> 5: insert B
> 6: ROLLBACK TO SAVEPOINT 2
>
> ROLLBACK in line 6 cancels also line 2 in addition to line 5.
>
> I want to cancel just line 5.
>
> What am I doing wrong ?
>
> br,
>
> Esko

On Win7 32 bit:

SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> create table t( id integer primary key, data text );
sqlite> savepoint a;
sqlite> insert into t( data ) values( '1' );
sqlite> release savepoint a;
sqlite> savepoint b;
sqlite> insert into t( data ) values( '2' );
sqlite> rollback to savepoint b;
sqlite> select * from t;
1|1
sqlite>
sqlite> commit;
sqlite> select * from t;
1|1
sqlite>

I see no problem. Perhaps you can expand on the environment/version in
which you are trying this

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

[ext-Esko.Hujanen]

Hi All,

 

1: SAVEPOINT 1

2: insert A

3: RELEASE SAVEPOINT 1

4: SAVEPOINT 2

5: insert B

6: ROLLBACK TO SAVEPOINT 2 

 

ROLLBACK in line 6 cancels also line 2 in addition to line 5.

I want to cancel just line 5.

 

What am I doing wrong ?

 

br,

Esko




Please note: This e-mail may contain confidential information
intended solely for the addressee. If you have received this
e-mail in error, please do not disclose it to anyone, notify
the sender promptly, and delete the message from your system.
Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users