Understanding and operation of MySQL database transaction

A transaction is a series of operations that are performed as a single logical unit of work that is submitted to the system as a whole, either executed or not executed. A transaction is an inseparable unit of work logic.

Transactions have four properties, referred to as ACID properties:

1. Atomicity (atomicity): A transaction is a complete operation. The steps of a transaction are non-divided (atomic), either executed or not executed.
2. Consistency (consistency): When a transaction completes, the data must be in a consistent state.
3. Isolation (Isolation): All concurrent transactions that modify data are isolated from each other, indicating that the transaction must be independent and that it should not rely on or affect other transactions in any way.
4. Persistent (durablity): After a transaction is complete, its modifications to the database are persisted, and the transaction log keeps the transaction permanent.

By default, MySQL will run in autocommit mode, which means that each SQL command will run as a small transaction.

1. Turn off autocommit mode: Mysql>set autocommit=0;
2. To set a thing restore point: mysql>savepoint p1;
3. things roll back to a restore point: mysql>rollback to P1;
4. things are completely rolled back: mysql>rollback;
5. Things to submit: mysql>commit;

Understanding and operation of MySQL database transaction