High Performance MySQL Learning notes

1th MySQL architecture and history 1.2 concurrency control

MySQL implements concurrency control at two levels: the server layer and the storage engine layer.

Read lock and Write lock:

When dealing with concurrent reads or writes, you can solve the problem by implementing a system of two locks.

These two types of locks are often referred to as shared and exclusive locks , or read and write locks.

　　Read locks: Shared , or non-blocking, where multiple customers can read the same data at the same time.

　　Write Lock: Is exclusive , a write lock blocks other write and read locks, only one user can write at the same time, and prevents other users from reading the data being written.

Size of Lock:

　　lock granularity: refers to the size of the locked object. Obviously, the smaller the granularity of the lock, the higher the concurrency control efficiency.

The various operations of the lock, including acquiring locks, checking locks, and releasing locks, can increase the system overhead.

Therefore, if the system spends a lot of time managing locks, rather than getting the data, it can affect system performance.

　　Lock Policy: seek a balance between the cost and security of the lock.

There are two common lock policies, table locks, and row-level locks .

　　table Lock: The overhead is small, but concurrency control is not good.

　　row-level locks: concurrency control can be implemented well, but the overhead is large.

Row-level locks are implemented in the MySQL storage engine layer, not implemented at the server level, and row-level locks are implemented in InnoDB.

1.3 Business

　　transaction: is a set of atomic SQL statements in which statements within a transaction are either successfully executed or all failed to execute.

four characteristics of the business acid:

　　atomicity : The entire transaction either commits successfully or all fails back, and cannot be executed only as part of it.

　　consistency : The database is always transitioning from one consistent state to another consistent state. (After the transfer failed, the card should not change the money, not less)

　　Isolation : Modifications made before a transaction commits are not visible to other transactions. (Generally speaking)

　　Persistence : After a transaction commits, the modifications are persisted to the database.

Transaction processing also allows the system to do additional work, and the user can choose the appropriate storage engine based on whether the business requires transactional processing.

Isolation levels for four types of transactions:

Read uncommitted (UNCOMMITTED):

Changes in the transaction, even if they are not committed, are visible to other transactions.

Transactions can read uncommitted data, also called Dirty reads , and are seldom used in general .

Read comitted (submitted for reading):

At the beginning of a transaction, only the modifications that have been committed are visible, and the modifications are not visible to other transactions.

This level is sometimes called non-repeatable , because two executions of the same query may have different results.

Transaction a executes two queries before and after the previous read of a record, and transaction B modifies and commits it, and when a reads the data again, it finds that it is not the same as the result read earlier.

Is the default isolation level for most databases (but MySQL is not).

REPEATABLE READ (repeatable Read):

Ensure that the same transaction reads the same record multiple times the same result is consistent. But cannot solve the phantom reading.

　　Phantom reads: when transaction a reads a range of records, transaction B inserts new records within that range, and when transaction a reads again, a magic line is generated.

Another kind of phantom reading: When transaction a modifies all rows of the database, transaction B inserts a new row of data into the database. At this point a finds that there are no modified records, just like hallucinations.

The InnoDB storage engine solves this problem through a multi-version concurrency control (mvcc,multiversion Concurrency) mechanism.

This is the default transaction isolation level for MySQL (InnoDB engine).

SERIALIZABLE (serializable):

Forcing transactions to execute serially, locking on each row of data, can result in a large number of timeouts and lock contention issues.

Consider using this level only if you are very much in need of ensuring data consistency and can accept no concurrency.

High Performance MySQL Learning notes