Transactions often appear in everyone’s business logic

The transaction

  • What is a transaction?
    • I’ll give you an example. When buying tickets on 12306, have you ever encountered a situation of no tickets, and the official will recommend the transfer car, then in the process of buying tickets, the first half and the second half can only be purchased successfully or failed at the same time, only to buy the half of the ticket is meaningless. So the definition of a transaction is that it both succeeds or fails
  • What is good business?
    • A good transaction must satisfy the ACID properties of the transaction
  • What is ACID?
    • A stands for atomiciy — the smallest unit involved in A chemical reaction, meaning both success and failure, with no third state in between
    • C) consistency C) consistency D) consistency – when a financial system collapses, the sum of the accounts must remain the same.
    • I stands for isolation — in a concurrent transaction, when one of the transactions is not finished, the other transactions should be invisible to the operation of that transaction
    • D stands for durability – after a transaction normally ends, data should be persisted and have a way to recover even if the system crashes

The isolation level of the transaction

The isolation level of a transaction is defined by the SQL standards organization

  • Four isolation levels for transactions

  • READ UNCOMMITTED and SERIALIZZBLE are not used very often, representing two extremes. The former does nothing to deal with the concurrency problem of transactions, which can easily lead to system chaos. The latter guarantees the serialization of transactions, resulting in significant performance degradation

  • READ uncommadmitted READ Not submitted. Allows a transaction to read data not committed by another transaction

  • READ COMMITTED The READ is COMMITTED. Allows transactions to read data already committed by other transactions

  • REPEATABLE READ REPEATABLE READ. Consistent results for the same data query under the same transaction

  • SERIALIZABLE serialized transactions. At this level, all transactions are executed in serialized order to avoid dirty reads, unrepeatable reads, and phantom all problems. However, transaction execution at this transaction isolation level is inefficient and takes a toll on database performance

  • Check the system isolation level: select @@global.tx_ISOLATION; Query the current session isolation level select @@TX_ISOLATION; SET session TRANSACTION ISOLATION LEVEL serialIZABLE; SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

The lock

  • From the point of view of the lock (this is just a mental partition, it can be understood that this is an interface)
    • Optimistic lock – Optimistic about other transactions (sunny boy), always assume that the data for this transaction will not be modified. CAS is a common optimistic lock implementation
    • CAS – Determines whether the data is modified before the operation. If the data is consistent, the transaction continues. If the data is inconsistent, the transaction restarts.
    • Pessimistic lock – Pessimistic about other transactions (victimization delusion), always thinking that the data for this transaction will be modified
  • From the granularity of the lock
    • Table lock – Other transactions are blocking operations on the entire table
    • Row lock – Another transaction is blocking the operation to lock the row
    • Gap lock — To lock a range in a table, the range must exist between the table records. If the range is 4 — 6, but the table records are only 3 — 8, then the range is (3, 8).
    • Next-key-lock — similar to a gap LOCK, the range is (x,y), that is, the record that can be locked behind the boundary
  • From the characteristics of the lock
    • Shared locks – Typically with read operations, multiple transactions sharing read operations but not modification, and locking operations
    • Exclusive lock – usually used for write operations that do not allow other transactions to operate (including write and lock operations)

MVCC multi-version concurrency control

  • Transaction version number
    • Before each transaction is started, a self-growing transaction ID will be obtained from the database, and the order of transaction execution can be judged from the transaction ID
    • Select TRX_ID from information_schema. INNODB_TRX;
  • Hidden fields (Innodb adds 3 extra fields per row)
    • DB_TRX_ID: The value is 6 bytes. Refers to the transaction identifier, the transaction ID, of the last transaction inserted or updated in the row. Also, deletes are internally treated as updates, in which special bits in the line are set to mark it as deleted.
    • DB_ROLL_PTR: The value is 7 bytes. Represents a pointer to the rollback segment of that row. The rollback pointer points to the undo log record written to the rollback segment. If the row has been updated, undo logging will contain the information necessary to rebuild the contents of the row before updating it.
    • DB_ROW_ID: The value is 6 bytes. Contains a row ID that increases monotonically as a new row is inserted. If InnoDB automatically generates a clustered index, the index contains the row ID value. Otherwise, the DB_ROW_ID column will not appear in any indexes
  • undo.log
    • Undo log is an important part of InnoDB’S MVCC transaction feature. The Undo log is used to record the logs before data modification
  • Current read and snapshot read
    • Current reading means that the current record is always read
      • All operations that need to be locked are currently read select * from…. where … For update, select * from…. where … lock in share mode update …. set .. where … delete from. . where ..
    • Snapshot reading refers to reading the data of the visible version of the transaction according to certain rules
      • Common query operations are snapshot reads

If snapshot reads are used in all transactions, there will be no illusions, but mixing snapshot reads with current reads will produce illusions. When all transactions use current reads, next-lock is used to avoid phantom reads