This is the sixth day of my participation in the August More text Challenge. For details, see:August is more challenging

Preface: This is my fifth article on MySQL. The article is relatively shallow, suitable for beginners. The last article briefly introduced MySQL about granularity control, lock strategy and so on. This article takes a look at transactions. 😑

A transaction.

1. What is a transaction

A transaction is a set of atomic SQL queries, or a single unit of work. If the database engine succeeds in executing this set of commands, its results are written to the database. If one of them fails, none of the results you just executed will be written to the database. As a result, all statements within a transaction either succeed or fail.

It might be a little confusing for some beginners. Other articles are generally taking transfer as an example, here I take a previous game to complete the game as an example. The level requirement of the game is to save five NPCS, and if one NPC dies during the rescue, the game fails.

The level of the game is like the transaction above, rescuing an NPC alone is like an SQL in the transaction, failing to rescue an NPC means the game fails (the transaction fails to roll back), and all NPCS are trapped again. This level (transaction) is only successful when all NPCS have been rescued (SQL has been executed successfully) and can move on to the next level (data is written to the database).

2. How to start a transaction

start transaction;
update table_name set table_status='error' where id=1;
update table_name set table_status='error' where id=2;
commit;
Copy the code

3. Four attributes of the transaction.

  • Atomicity: a transaction must be the smallest indivisible unit and either all will succeed or all will fail. The atomicity of a transaction is that only part of the SQL cannot be executed.
  • Consistency: The database always transitions from one consistent state to another. As mentioned above, if only one NPC rescue fails, all NPCS return to their original state of being trapped.
  • Isolation: In general, a transaction is not visible to other threads until it completes.
  • Persistence: Once the transaction is committed, all data is written to the database, and even if the database crashes later, the modified data will not disappear.