MySQL Learning note four: concurrency control and transaction mechanism

I. Concurrency control for MySQL

Concurrency control arises when multiple queries need to modify the same data at the same time. MySQL can control concurrency at two levels: the server layer and the storage engine layer.

MySQL implements concurrency control by locking:

There are two types of ⑴ locks:

Read lock: A shared lock, that is, a read lock does not block other read locks, multiple users can read the same resource at the same time, without interfering with each other.

Write Lock: An exclusive lock, that is, a write lock blocks other read and write locks, and only one user can perform writes at a given time.

⑵ Lock particle Size:

Table-level Lock: locks the entire table

Row-level locks: more concurrency, but more cumbersome to maintain, increases system overhead and creates deadlocks. Row-level locks can only be implemented at the storage engine level and the MyISAM storage engine does not support row-level locks

⑶ Lock Classification:

Implicit lock: Auto-complete by the storage engine

Explicit Lock: User can manually apply a lock (table-level lock)

⑷ manual plus unlock: server level

LOCK TABLES Tb_name {read| WRITE},...;

UNLOCK TABLES;

FLUSH TABLES with READ LOCK; #全局施加读锁

The InnoDB storage engine also supports another explicit lock (only the selected rows are locked):

SELECT ... LOCK in SHARE MODE;

SELECT ... For UPDATE;

Second, the business

A transaction (Transaction) is a program execution unit (unit) that accesses and possibly updates various data items in a database;

Imagine a scenario, a to transfer to B 500 yuan, to go through two steps: A from their own account minus 500 yuan, to B's account increase of 500 yuan. Obviously, it is unreasonable to just complete the first step or just complete the second step, both of which must be placed in a transaction as an indivisible unit of work, either executed or not executed.

⑴ transactions are the basic unit of recovery and concurrency control and must be tested by ACID to conform to the concept of transactions:

atomicity (atomicity): A transaction is an inseparable unit of work, and the operations included in the transaction are either done or not.

consistency (consistency): A transaction must change the database from one consistency state to another. Consistency is closely related to atomicity.

isolation (Isolation): The execution of one transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently.

Persistence (Durability): Persistence is also called permanence (permanence), which means that once a transaction is committed, it changes the data in the database to be permanent

Isolation level for ⑵ transactions:

read-uncommitted (Read UNCOMMITTED): Changes in transactions, even if they are not committed, are visible to other transactions. Transactions can read uncommitted data, which is also known as dirty reads; the lowest isolation level

Read-commtted (read commit): At the beginning of a transaction, you can only "see" The changes made by the committed firm, so executing the same query again may result in different results called "non-repeatable reads."

Repeatable-read (can be stressed): The same transaction is guaranteed to read the same record multiple times the result is consistent, may cause "phantom read"; MySQL default isolation level

Serializabile (SERIALIZABLE): Locking read, that is, a transaction request cannot be read without a lock, and it must wait until the other transaction releases the lock (commit or rollback).

⑶ View the isolation level for MySQL: SELECT @ @global. tx_isolation;

⑷ the start, commit, and rollback of a transaction:

Start: Start TRANSACTION;

Commit: Commit;

Rollback: ROLLBACK;

Save point: savepoint identifier;

Rollback to a savepoint: ROLLBACK [Work] to [savepoint] identifier;

Delete a save point: RELEASE savepoint identifier;

⑸mysql Auto-Submit feature: SELECT @ @GLOBAL. autocommit;

Enabled by default, modifying autocommit only affects the storage engine that supports transactions;

Automatic submission ensures that data is written to disk in a timely manner, but causes frequent I/O and reduces system performance.

⑹MVCC: Multi-version concurrency control, by saving data at a point in time snapshot implementation. Regardless of how long a transaction executes, the data it sees is consistent. Depending on the time the transaction started, each transaction might be different for the same table, the data that was seen at the same time. MVCC is valid only at the second and third isolation levels;

⑺ transaction log: Convert random I/O to sequential I/O to improve transaction efficiency; The transaction log is also known as Write-ahead Logging.

Data from the transaction log, data file, memory

To reduce the disk pressure, it is advisable to put the transaction log and data files on different disks, the transaction log should not be too large;

⑻innodb supports transactions, while MyISAM does not support

MySQL Learning note four: concurrency control and transaction mechanism