MySQL-basic concepts,

MySQL-basic concepts,

I. Mysql logic architecture

Reference from high performance Mysql

Ii. Concurrency Control

 

  
Read/write locks: Read locks are shared and exclusive, blocking other write and read locks.
  
Lock granularity: Table lock and row-Level Lock
 

Iii. Transactions

 

  
Transactions (ACID properties): atomicity, consistency, isolation, and persistence
  
Isolation level
 
Read uncommittedUncommitted read
Modifications in the transaction, even if not committed, are visible to other transactions.
The transaction reads uncommitted data calledDirty read.
Read committedSubmit read
The transaction can only see the modifications that have been committed by other transactions.
The query results of this transaction before and after other transactions are committed may be inconsistentNon-repeated read.
Repeatable readRepeatable read-Mysql Default level
Ensure that the data read by the same transaction is consistent multiple times.
When a transaction reads a record in a certain range, another transaction inserts a record into the returned record. When the previous transaction reads the record againPhantom read.
SerializableSerializable
Enforces the serial execution of transactions.
 

  
A deadlock occurs when multiple transaction colleagues lock a resource.
 
After a deadlock, you can roll back one or all of the transactions to break the deadlock. during the design of the application, you can consider re-executing the rollback transaction.
 

  
Transaction Log
 
The modification of data is recorded in the transaction log and persistent, but the data itself is not written to the disk, the system crashes, and the data can be recovered when the storage engine is restarted.