MySQL Architecture and history

1.1,MySQL 's logical architecture

the schema is as follows:

Storage Engine : Responsible for the storage and extraction of data, dozens of APIs for the service layer to call. There is no interaction between the various storage engines, just for the service layer to invoke. Transaction control and lock management are also carried out in the storage engine.

Service Layer : after a SQL comes, the service layer will parse, establish the parse tree, call the underlying storage engine to obtain various overhead information and statistics, make various optimizations, determine the table reading order and select the appropriate index.

1.2 concurrency control

1, MySQL through the read lock and write lock for concurrency control.

2, the size of the lock

table lock concurrency Low, lock overhead small alter table locks

Row-level locks are highly concurrent, but lock overhead is large

3, the lock control is implemented in the storage engine, so different storage engine lock order is not the same, some can cause the deadlock some will not.

1.3 Acid Properties for transaction transactions

Atomicity: The operation of one transaction either executes successfully or all fails back.

Consistency: When executing the 3rd and 4 statements of a transaction, the database crashes and the data is consistent.

Isolation: The visibility of one transaction's operation on another. MySQL defaults to repeatable read.

Persistence: Once a transaction commits, its modifications are permanently synchronized to the database.

1.3.1 Isolation Level

Read uncommited (unread) causes dirty reads

Read commited (read-committed) causes non-repeatable reads

Oracle and SQL Server are at this level by default

REPEATABLE READ (REPEATABLE Read) causes Phantom reads

MySQL default is this level (via MVCC and lock mechanism)

MySQL solves the problem of Phantom reading by MVCC and Gap lock

Seriable (Serializable)

The transaction is executed serially

1.3.2 Deadlock

Deadlocks can result because of inconsistent way of requesting resources

The database system realizes deadlock detection, unlocking deadlock, and requesting lock timeout mechanism.

If a deadlock occurs, a transaction that contains a minimum row-level lock is rolled back to unlock the deadlock.

1.3.3 Transaction Log

It takes a long time to update data to the uneven distribution of data on disk. However, you can add the operation as a log to the end of the log file, and the log storage operation is ordered. This can speed up.

1.3.4mysql of transactions

1, by default, each connection is autocommit, you can see the current state by command show variables like ' autocommit ' after establishing the connection.

You can set the transaction to be not autocommit by using set autocommit=0 . Then all queries are in one transaction until they are explicitly committed or rolled back.

2, some operations will automatically submit the current transaction, such as Alter table,lock tables

3. Set the isolation level of the transaction (you can set the global and session level)

The global transaction isolation level can be set by SET TRANSACTION isolation levels

The isolation level of this link can be set through set session transaction isolation levels and will take effect in the next transaction.

1.3.5 Hybrid use of the storage engine

1, do not recommend this use

2. If a transaction operates concurrently with a storage engine table that supports transactions and a storage engine table that does not support transactions, there is no problem if the transaction is successfully committed. If the transaction is rolled back, the storage engine table that supports the transaction can be rolled back, but the storage engine table that does not support transactions cannot be rolled back, resulting in data consistency issues.

MySQL Architecture and history