The transaction

Transaction characteristics

  1. Atomicity: do all or nothing
  2. Consistency: Data meets the constraints of business rules before and after a transaction operation
  3. Isolation: The ability of a database to allow multiple concurrent transactions to read, write, and modify data simultaneously
  4. Persistence: Changes to data are permanent

Truncate and DELETE

The difference between:

  • Truncate is a DDL statement operation, and DELETE is a DML statement operation
  • In short (DDL is build database/create table/etc.; DML is for adding, deleting, modifying and checking statements.
  1. Truncate cannot be rolled back, and DELETE can be rolled back
  2. Truncate Clears the auto-increment ID attribute of the table and starts the record from 1. Delete does not

Concurrency issues

  • Dirty read: When a transaction makes changes to data that have not yet committed to the database, another transaction accesses the data
  • Lost changes: When one transaction modifies data, another transaction also modifies the data
  • Unrepeatable read: when a transaction reads the same data multiple times before the end, another data page accesses the data, resulting in the two reads of the data is different
  • Magic read: the first transaction reads a few rows of data, then another transaction inserts a few rows, and the first transaction finds more data than it originally did

Isolation level

  • Read-uncommitted: Data changes that have not been committed can be READ, which may result in dirty, phantom, or unrepeatable reads.
  • Read-committed: Allows reading of COMMITTED data in a concurrent transaction. Dirty reads are prevented, but phantom or non-repeatable reads may still occur.
  • REPEATABLE-READ: The result of multiple reads of the same field is consistent. Dirty and non-repeatable reads can be prevented, but magic reads can still occur.
  • SERIALIZABLE: Fully compliant with ACID to prevent dirty reads, non-repeatable reads, and phantom reads.

InnoDB lock type

Row locks

id name score
1   aa    60
Copy the code
  • In the sessionAUpdate a field in a transactionscore=60Set the name to aaa
  • In the sessionBTransaction, update sessionAThe same row of data will benameIf this parameter is set to a, the lock wait times out

Gap lock

  • At the RR isolation level, Gap lock is introduced to avoid phantom reads
  • But it only locks the range of row record data and does not contain itself, that is, it does not allow any data to be inserted within this range

== In RR level ==

id  name  score
1   aa      60
2   bb      70  
3   cc      80
Copy the code
  • The sessionAQuery records with score<80 and add a shared lock to them
  • The sessionBThe data with score=75 was inserted into the table, but failed to be inserted
  • In this interval where SCOR <80, no data is allowed to be inserted

Pooling ideas (or benefits)

  • Offset the cost of fetching resources each time, such as creating threads, fetching remote connections
  • For example, go to the canteen to play dinner, every time the aunt is played, go to eat directly

InnoDB handles deadlocks

  • A deadlocked cyclic dependency is detected and an error is returned immediately
  • The current approach is to roll back the transaction that holds the least row-level exclusive lock