What is a transaction?

A transaction in a database is a batch of operations on a database that will either all succeed or all fail, with no partial success.

Properties of transactions (ACID)

  • The whole process of an Atomicity transaction, like an atomic operation, will eventually either all succeed or all fail. This Atomicity is seen from the end result, and the process is indivisible from the end result.

  • Consistency (Consistency), execution, the execution of finished before the start of the transaction and the point in time, more than one person to observe transaction data, see the data is consistent, such as the transaction is in the process of operation, A connection is 100, so at this time also went to see the time is 100 B, don’t say AB to see different data, The data they saw at one point in time was consistent.

  • Isolation The execution of a transaction cannot be disturbed by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

  • Durability Once a transaction commits, their changes to data in the database should be permanent. When a transaction commits, the data is persisted to disk and the changes are permanent.

Concurrency issues for transactions

  1. Dirty read: Transaction A reads the data updated by transaction B, and then TRANSACTION B rolls back the data
  2. Non-repeatable read: When transaction A reads the same data for many times, transaction B updates and commits the data during the process of reading the same data for many times. As A result, when transaction A reads the same data for many times, the results are inconsistent.
  3. Phantom read: transaction A is modifying some data, but at this time, transaction B directly inserts A data, resulting in A transaction after the modification of A data is not modified, as if there is an illusion, this is called phantom read.
  4. Read committed, literally, means that a transaction can read committed data from another transaction. Each read in a transaction reads the latest data (equivalent to the current read) that has been committed by another transaction in the database.
  5. Repeatable reads: no matter how many times a read is performed in a transaction, the read results are the same.

Transaction isolation level:

  • READ UNCOMMITTED: Read-uncommitted: At this isolation level, transactions are not isolated and dirty reads are generated. UNCOMMITTED records can be READ but are not used in practice.
  • READ COMMITTED: READ-COMMITTED (Oracle’s default isolation level, commonly used in projects), this transaction reads the latest data (after other transactions COMMITTED). The problem is that in the same transaction, the same SELECT will read different results (not read twice).
  • REPEATABLE-READ (default isolation level of MySql, there are two reasons why the default isolation level is READ commited. A gap lock in repeatable reads increases the probability of deadlock. In repeatable read isolation level, a table will be locked if a conditional column does not hit the index! At the read committed isolation level, only rows are locked; 2. What binlog format is used for master/slave replication: row format is used for row-based replication. In the same transaction, the SELECT result is the state at the point in time when the transaction started. Therefore, the same SELECT operation in the same transaction will read the same result. However, there will be illusions.
  • Serial: SERIALIZABLE, read operations implicitly acquire the shared lock, which ensures mutual exclusion between different transactions.
// Check the isolation level:show variables like 'transaction_isolation'; Init = read-uncommitted; transaction- ISOLATION = read-uncommittedCopy the code
Isolation level Dirty read Unrepeatable read Phantom read Read Data Consistency
READ-UNCOMMITTED There are There are There are At the lowest level, only physically corrupted data can be read
READ-COMMITTED There is no There are There are statement-level
REPEATABLE-READ There is no There is no There are The transaction level
SERIALIZABLE There is no There is no There is no The highest level, transaction level

The mysql binlog format is statement, row, and mixed

Row: Records changes to the actual data in each row. Mixed: a mixed statement format with row modes. Row format, which in this case is row-based replication

Note: In REPEATABLE-READ isolation level, the table will be locked if the condition column does not match the index! In read-COMMITTED isolation, the binlog format used is row, which is row-based replication and locks only rows.