> > >and then it fails - will the database insert / update / delete first 10 or > >will it return to initial state before any inserts? >
>The default behavior is ABORT, which means the statement that caused the error is rolled back, but the transaction stays open with any prior changes still in place. If you COMMIT at this point, those changes will get committed. That doesn't make sense because if I start transaction I get SQLITE_OK, then I do several "INSERT INTO" queries, they also pass as SQLITE_OK. Finally on COMMIT - I get error. By the docs I should be able to put ROLLBACK - before the COMMIT. But apparently it doesn't work like that. Now, looking at the database - it seems that it already did automatic ROLLBACK because it does so on SQLITE_FULL, SQLITE_IOERR, SQLITE_BUSY, SQLITE_NOMEM, SQLITE_INTERRUPT. This isn't a problem for single INSERT or UPDATE statement as I can always do INSERT OR ROLLBACK INTO [tablename] but for BEGIN/COMMIT blocks it treats the entire block as it seems as a single statement. Furthermore doing something like: BEGIN EXCLUSIVE INSERT OR ROLLBACK INTO table INSERT OR ROLLBACK INTO table INSERT OR ROLLBACK INTO table COMMIT also doesn't make sense - because if first insert is successful and second is not it would keep first? Or not as it is within BEGIN/COMMIT block? In my tests it never did partial inserts in BEGIN/COMMIT block but the docs do specify to issue ROLLBACK on error. So I'm puzzled by all this. The only thing I want is to return database to initial state if any command or COMMIT fails. The only solution I see is something like: SAVEPOINT mysavepoint INSERT INTO table INSERT INTO table INSERT INTO table RELEASE mysavepoint in this case I would be able to call "ROLLBACK TO mysavepoint" later after RELEASE which would be replacement for COMMIT (as is SAVEPOINT replacement for BEGIN). Or something like: SAVEPOINT mysavepoint BEGIN EXCLUSIVE INSERT INTO table INSERT INTO table INSERT INTO table COMMIT RELEASE mysavepoint and on error - ROLLBACK TO mysavepoint... Anyway... still confused about all this mess. So how to make sure that the transaction is rolled back then? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
