ACID characteristics and implementation methods of MySQL relational database transactions

1. Detailed ACID characteristics of transactions

ACID are the four characteristics that must be possessed to ensure that the transaction is correct and reliable:

• Atomicity: The operations in the transaction succeed or fail at the same time.

• Consistency: Database transactions cannot destroy the integrity of the data and the consistency of the business logic.

• Isolation: A transaction does not affect the operation of other transactions.

• Durability (Durability): After the transaction is completed, the modifications made by the transaction should be persistently saved in the database and will not be rolled back.

Take A transferring 100 yuan to B as an example:

• Atomicity: A The loss of 100 yuan occurs at the same time as B's receipt of 100 yuan.

• Consistency: A's account cannot be negative after losing 100 yuan.

• Isolation: If account A loses 1 yuan by executing transaction B while executing this transaction, then the final loss will be 101 yuan, and the two have no influence on each other. .

• Persistence: A’s account cannot get it back after losing 100 yuan.

2. Implementation of MySQL transactions

MySQL transactions are implemented by the InnoDB storage engine.

You can use the following command to explicitly start the transaction:

start transaction / (Begin);
#一条或多条sql语句
Commit;

In addition, in the automatic submission (autocommit) mode, every SQL we execute Statements are an independent transaction; if the autocommit mode is turned off, all SQL statements are in one transaction until commit or rollback is executed, and the transaction ends and another transaction starts.

The ACID characteristics of MySQL transactions are realized by the following mechanism:

• Atomicity: undo log, logical log, record SQL execution related information. When a rollback occurs, InnoDB will do the opposite of the previous work based on the contents of the undo log

• Persistence: redo log, when the transaction is committed, will be called The fsync interface flushes the redo log.

• Isolation: Lock mechanism and MVCC.

• Consistency: The design of the database itself.

3. Use of Gorm transactions

The Gorm of Go language provides support for transaction operations:

db.Transaction(func(tx *gorm.DB) error {
  // 在事务中执行一些 db 操作（从这里开始，您应该使用 'tx' 而不是 'db'）
  if err := tx.Create(&Animal{Name: "Giraffe"}).Error; err != nil {
    // 返回任何错误都会回滚事务
    return err
  }

  if err := tx.Create(&Animal{Name: "Lion"}).Error; err != nil {
    return err
  }

  // 返回 nil 提交事务
  return nil
})

In addition , as well as operations such as nested transactions and manual transactions, you can refer to the Chinese document: Detailed introduction to Go GORM transactions

4. The use of Spring transactions

public class AClass {

    @Transactional(rollbackFor = Exception.class)
    public void aFunction() {
        //todo: 数据库操作A(增，删，该)
    }
}

@Transactional annotation must be added to the public method , private and protected methods are invalid.

Under normal circumstances, it is recommended to add the @Transactional annotation to the method, because @Transactional is added directly to the class or interface. The @Transactional annotation will be effective for all public methods in the class or interface, which will affect performance. .

The above is the detailed content of ACID characteristics and implementation methods of MySQL relational database transactions. For more information, please follow other related articles on the PHP Chinese website!