This is the 12th day of my participation in the August More Text Challenge. For details, see:August is more challenging

Quickly review what you’ve learned

Transaction implementation:

  • Atomicity, consistency, and persistence are passedredo logandundo logTo implement the
  • redo logTo keep the atoms and the persistence
  • undo logTo ensure consistency

Distributed transaction

  • InnoDB mainly implements distributed transactions through XA transactions
  • A distributed transaction is the possibility of multiple independent transaction resources (relational database systems/global events)
  • It is important to note that the isolation level must be serializable when using distributed transactionsSERIALIZABLE
  • XA transactions are powerful because Server A is Mysql, Server B is Oracle, and Server C is SQL Server
  • XA transactions are supported as long as each of the above three servers participates in a global transaction
  • For example, Xiao Wang transfers 100 yuan from Beijing to Xiao Li in Shenyang
# BeiJing
update account set money = money - 100 where name = 'xiaowang'
Copy the code
# ShenYang
update account set money = money + 100 where name = 'xiaoli'
Copy the code
  • During this process, it is necessary to ensure that none of the nodes will fail, otherwise all will be rolled back/committed

XA transaction

  • An XA transaction consists of one or more resource managers, a transaction manager, and an application.
  • Resource manager: Provides access to transactional resources. In general – a database is a resource manager.
  • Transaction manager: Coordinates transactions participating in a global transaction. All resources required and involved in the global transaction

Manager to communicate.

  • Application: Defines transaction boundaries and specifies operations in a global transaction.

There are two stages

  1. All global transaction nodes are ready
  2. All transactions are guaranteed to be atomic, either rolled back or committed

Business level sideshow

  • The default isolation level supported by InnoDB storage engines is RR
  • InnoDB storage engine uses next-key Lock algorithm at RR transaction isolation level to avoid phantom reads
  • So InnoDB storage engine has fully guaranteed transaction isolation requirements at the default RR level, which is equivalent to SQL standardSERIALIZABLE(serializable) isolation level.
  • The lower the isolation level, the fewer locks the transaction requests or the shorter the time the locks are held.

review

  • Transactions must conform to ACID properties, Atomicity, Consistency, Isolation, and Durability. Isolation passage
  • By default, the MySQL database is always automatically committed
  • COMMIT and ROLLBACK are done at the program side, not within the stored procedure. In the complete