My question is simple: Which mechanism should I use in databases to ensure that if device shuts down in middle, the process is unified. transactions?
Every time you have perform something atomically you should use transactions. Please understand that (depending on the language/framework/ORM), MySQL may be in auto-commit mode, which means that, functionally speaking, if you are using InnoDB something like this:
INSERT INTO innodb_table VALUES (1, 2, 3);
gets converted internally into:
START TRANSACTION; INSERT INTO innodb_table VALUES (1, 2, 3); COMMIT;
That is not true if auto_commit has been disabled (for example, in the default configuration for the python connector), where you need to execute a
COMMIT after each set of queries.
So now the question is: How large should your transactions be? As long-open transactions may limit concurrency, on one side you should do transactions as short as possible. But for performance reasons, you should do as few transactions as reasonable. Where is the limit? Normally, it is given by the application business logic. In the most topic way: a bank operation should commit when both the sender and receiver of the money has been calculated, or being fully ROLLBACKed if something wrong happens.
In the case of something like an online shop, transactions are usually controlled at application level, and not at database level, as one purchase may take minutes to be executed, so to avoid contention problems, the application transaction is split in several database-level transactions by the user session.
Normally in OLTP databases, transactions are so quick that we need not to place explicit transactions.Though internally MySQL take care of this and place implicit transaction. But if you think your scripts are long and it will take time to execute and if unrealistic/unwanted results can occur if failed in middle then you must apply explicit transactions.