MySQL-transactions

MySQL-transactions

Transaction overview

Transaction is one of the important characteristics of a database system that distinguishes it from all other file systems. A transaction is a set of atomic SQL statements or an independent unit of work

Transaction characteristics

1. Atomicity A transaction must be regarded as an inseparable smallest unit of work. All operations in the entire transaction are either submitted successfully or all failed. For a transaction, it is impossible to perform only part of the operations.

Eg: 1. Check whether the balance in the wealth management account is higher than 2,000 yuan. 2. Subtract 2,000 yuan from the balance of the wealth management account. 3. Add 2,000 yuan to the active deposit account.

All operations in the entire transaction either all commit successfully, or all fail and roll back. 2. Consistency (CONSISTENCY) Consistency refers to the transaction transforming the database from one consistency state to another consistency state, and the integrity of the data in the database is not destroyed before the transaction starts and after the transaction ends. 3. Isolation (ISOLATION) Isolation requires a transaction to modify the data in the database, which is invisible to other transactions before the completion of the submission. The four types of levels defined in the SQL standard (from low to high isolation) (from high to low concurrency) Uncommitted read (READ UNCOMMITED) Commited read (READ COMMITED) Repeatable read (REPEATABLE READ) Serializable (SERIALIZABLE) 4. Persistence Once the transaction is committed, the changes made will be saved to the database forever, even if the system crashes, the modified data that has been submitted will not be lost.

Big business

The transaction risk with longer running time and more operating data: Locking too much data will cause a large amount of blockage and lock timeout rollback. It will take a long time to execute and cause master-slave delay. How to deal with large transactions? Avoid processing too much data at once to move out unnecessary SELECT operations in the transaction

Reference: https://cloud.tencent.com/developer/article/1437145 MySQL-Transaction-Cloud + Community-Tencent Cloud