ON CONFLICT Rollback algorithm in SQLite

Question

Why we use Rollback algorithm in ON CONFLICT clause when Abort algorithm also work same.

Pravin More · Answer

hi monika , see, there is difference between both because when we use Rollback, and conflict occure then it ROLLBACKs current transaction but when we use ABORT,and conflict occure then it rollbacks changes of current sql statment not transaction. so ABORT undo changes of current sql statement and ROLLBACk undo changes of current transaction Thanks, Pravin.

Monika Arora · Answer

Actually now I m talking about ABORT conflict clause. Rollback is working fine. So tell me for the same. Sorry I forget to mention earlier.

Pravin More · Answer

monika see, before starting transaction you have code is 1 then you start transaction and in that 1st you update id to 6 2nd you try to update to NULL and conflict occure so rollback will undo code to 1.(it was before start of transaction) thats the concept. if you are not geting result like this then please check transaction i am assuming that you are going to put 2 update in same transaction i.e update to 6 and update to null. Thanks, Pravin.

Monika Arora · Answer

Hi. It means that when I have performed some update statement with "update emp set code=6 where code=1". Later on I change it to NULL then it will show value 6 when I execute select statement for the table. I have tried it and getting the same result which I have mentioned here. So is it correct?

Monika Arora · Answer

Thanks Pravin