On 10/5/2011 12:42 PM, Pero Mirko wrote:
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.
What kind of error? Some errors are so severe that there's no way to
recover, and SQLite does perform automatic rollback. E.g. failure to
write to journal file will do that, I think. Others are recoverable and
leave the transaction open - e.g. SQLITE_BUSY (someone else is reading
the same database; wait a bit and retry).
In any case, if you only get an error on COMMIT, then doing INSERT OR
<anything> won't change the situation - INSERT statements aren't
failing, so they will have no reason to exercise their conflict clause,
whether implicit or explictily specified.
By the docs I should be able to put ROLLBACK - before the COMMIT.
Now *that* makes no sense. Where in the docs do you see this? Once you
execute ROLLBACK, the changes are reverted and the transaction is
closed. There's nothing to commit.
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.
If you want to always roll back on any error, what's stopping you? Just
do that - whether the error comes from COMMIT or any other statement. I
don't understand the problem you (think you) have.
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?
If INSERT fails due to constraint violation (the only kind that conflict
resolution clause affects), then OR ROLLBACK clause will roll back the
whole transaction, including any prior statements.
In my tests it never did partial inserts in BEGIN/COMMIT block but the docs
do specify to issue ROLLBACK on error.
Structure your tests so as to trigger a constraint violation (e.g. try
to insert the same value twice into a UNIQUE column), if you want to see
conflict resolution clause in action.
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.
That's what ROLLBACK is for.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
