Re: [sqlite] A question about transactions
jeff archer wrote: > I am using SQLite from C++ and I have implemented a class to manager nested > transactions using savepoints. I have currently implemented as a stack of > transactions such that the first Begin uses BEGIN IMMEDIATE, while subsequent > levels use SAVEPOINT T where is a sequentially increasing number > starting at 0001. Commit does RELEASE on the latest SAVEPOINT or a real COMMIT > once savepoint stack is cleared. Rollback does ROLLBACK TO or a real ROLLBACK > once savepoint stack is cleared. > > Is this OK to mix savepoints with transactions like this? Yes. That's precisely the intended use case. Remember though that the transaction is not really committed until COMMIT statement runs: if your application crashes or machine loses power, all changes to the beginning of the transaction are rolled back, not just those since last "committed" checkpoint. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A question about transactions
> Yes. That's precisely the intended use case. Remember though that the > transaction is not really committed until COMMIT statement runs: if your > application crashes or machine loses power, all changes to the beginning of > the transaction are rolled back, not just those since last "committed" > checkpoint. Does my memory fool me or there are some error conditions in SQLite when it automatically (without explicit user request) rollbacks the whole transaction disregarding any savepoints? If it's indeed the case then OP should handle these situations in his class (if they are possible in his SQLite usage pattern). Pavel On Wed, Nov 3, 2010 at 2:02 PM, Igor Tandetnik wrote: > jeff archer wrote: >> I am using SQLite from C++ and I have implemented a class to manager nested >> transactions using savepoints. I have currently implemented as a stack of >> transactions such that the first Begin uses BEGIN IMMEDIATE, while subsequent >> levels use SAVEPOINT T where is a sequentially increasing number >> starting at 0001. Commit does RELEASE on the latest SAVEPOINT or a real >> COMMIT >> once savepoint stack is cleared. Rollback does ROLLBACK TO or a real ROLLBACK >> once savepoint stack is cleared. >> >> Is this OK to mix savepoints with transactions like this? > > Yes. That's precisely the intended use case. Remember though that the > transaction is not really committed until COMMIT statement runs: if your > application crashes or machine loses power, all changes to the beginning of > the transaction are rolled back, not just those since last "committed" > checkpoint. > -- > Igor Tandetnik > > > ___ > 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] A question about transactions
Pavel Ivanov wrote: >> Yes. That's precisely the intended use case. Remember though that the >> transaction is not really committed until COMMIT statement >> runs: if your application crashes or machine loses power, all changes to the >> beginning of the transaction are rolled back, not >> just those since last "committed" checkpoint. > > Does my memory fool me or there are some error conditions in > SQLite when it automatically (without explicit user request) rollbacks > the whole transaction disregarding any savepoints? I believe ON CONFLICT ROLLBACK (and its equivalents, like RAISE(ROLLBACK) ) would roll back the whole transaction. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A question about transactions
On Nov 4, 2010, at 1:30 AM, Igor Tandetnik wrote: > Pavel Ivanov wrote: >>> Yes. That's precisely the intended use case. Remember though that >>> the transaction is not really committed until COMMIT statement >>> runs: if your application crashes or machine loses power, all >>> changes to the beginning of the transaction are rolled back, not >>> just those since last "committed" checkpoint. >> >> Does my memory fool me or there are some error conditions in >> SQLite when it automatically (without explicit user request) >> rollbacks >> the whole transaction disregarding any savepoints? > > I believe ON CONFLICT ROLLBACK (and its equivalents, like > RAISE(ROLLBACK) ) would roll back the whole transaction. That's the usual cause. Also, if an OOM or IO error occurs the transaction may be rolled back automatically. Or sometimes if the file-system is full. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
