Transaction, Commit, Rollback
Let’s pretend you want to execute multiple INSERT command which should be executed at once. However, during the execution, error occured and some INSERT command wasn’t executed. Is there any way that you can get back to the previous state?
The answer is NO. To prevent the disappointing situation, use TRANSACTION.
In SQL, auto commit, which automatically reflects the result of command, is on. By using StART TRANSACTION, you can explicitly turn off auto commit.
At the end of the commands, you should choose between COMMIT and ROLLBACK. By committing, the result in temporary area will be reflected to the result. By rollbacking, the result of commands will be removed and the state of database will be preserved.
-- Commit
START TRANSACTION;
command_1
command_2
...
COMMIT;
-- Rollback
START TRANSACTION;
command_1
command_2
...
ROLLBACK;
By using transaction and commit/rollback, you can decide whether to reflect the result of command.
All images, except those with separate source indications, are excerpted from lecture materials.
댓글남기기