This article on lock, transaction, concurrency control to do a summary, read a lot of articles on the Internet, description is very inaccurate. If there is inconsistent with your point of view, welcome well-founded pat brick!
Logical architecture of mysql server
Each connection creates a thread on the mysql server (threads are managed internally by the thread pool). For example, if a select statement is entered, mysql will first check the query cache to see if the select result set is cached. If not, it will continue parsing, optimizing, and executing the process. Otherwise the result set will be fetched from the cache between.
Mysql concurrency control — shared locks, exclusive locks
A Shared lock
Shared locks are also known as read locks. Read locks allow multiple connections to concurrently read the same resource at the same time without interfering with each other.
Exclusive lock
An exclusive lock is also called a write lock. A write lock blocks other write locks or read locks to ensure that only one connection can write data at a time and prevent other users from reading or writing data.
Lock strategy
The cost of locking is relatively expensive. The locking strategy is actually a balance strategy between ensuring thread safety and obtaining maximum performance.
- Mysql lock policy: talbe lock
Table lock is the most basic lock strategy in mysql. It is also the least expensive lock. It locks the entire table.
In this case, if a user is performing a write operation, an exclusive “write lock” is obtained, which may lock the entire table and block other users’ read and write operations.
If a user is performing read operations, the user obtains the shared lock read lock first. This lock concurrently reads the table using other read locks without interfering with each other. A read lock can concurrently read a unified resource as long as no write lock is entered.
DML update table set columnA= “A” where columnB= “B”. If the columnB field does not have an index (or is not prefixed with a composite index), all records are locked, i.e. the lock table. If the statement is executed to perform an index of the columnB field, rows that meet where are locked (row locks).
- Mysql lock policy: row lock
Row locking maximizes concurrency, but also maximizes overhead. As the name implies, row locking is granular to each row.
The transaction
A transaction is a set of atomic SQL, or a single unit of work. A transaction means that either the mysql engine will execute all of the SQL statements, or none of them (for example, if one of the statements fails).
For example, Tim wants to transfer 100 yuan to Bill: 1. Check whether Tim’s account balance is greater than 100 yuan. 2. Tim’s account is reduced by 100 yuan; 3. Add 100 yuan to Bill’s account; These three operations are a single transaction and must be executed as a package, with either all successful or none, and the failure of any of them results in all three operations “not executed” — rolled back.
CREATE DATABASE IF NOT EXISTS employees;
USE employees;
CREATE TABLE `employees`.`account` (
`id` BIGINT (11) NOT NULL AUTO_INCREMENT,
`p_name` VARCHAR (4),
`p_money` DECIMAL (10, 2) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ;
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('1'.'tim'.'200');
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('2'.'bill'.'200');
START TRANSACTION;
SELECT p_money FROM account WHERE p_name="tim"; -- step1 UPDATE account SET p_money=p_money-100 WHERE p_name="tim"; -- step2 UPDATE account SET p_money=p_money+100 WHERE p_name="bill"; -- step3 COMMIT;Copy the codeA good transactional system must meet ACID characteristics:
The transaction of the ACID
-
A: Atomiciy A transaction must ensure that all operations are either executed or rolled back. It is impossible for only part of the operation to be executed.
-
C:consistency Data must be converted from one consistent state to another consistent state. For example, in the last transaction, the system crashed when the second step was executed, and the data would not show that Bill’s account lost 100 pieces, but Tim’s account remained the same. Either the original (all rolled back), or Bill lost 100 pieces and Tim gained 100 pieces, the only two consistent states
-
I: Isolation Isolation When a transaction is not completed, it is usually guaranteed that other sessions cannot see the results of the transaction
-
D: Durability Transactions once commit, data is saved and will not be lost even if the system crashes after committing.
Isolation level
Check the system isolation level: select @@global.tx_ISOLATION; Query the current session isolation level select @@TX_ISOLATION; SET session TRANSACTION ISOLATION LEVEL serialIZABLE; SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;Copy the codeREAD UNCOMMITTED
Changes in a transaction are visible to other sessions even if they are not committed. Uncommitted data can be read — dirty reads. Dirty reads cause a lot of problems and this isolation level is generally not appropriate. Example:
-- ------------------------- read-uncommitted实例 ------------------------------
-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ UNCOMMITTED"; -- commit; -- Session B SELECT * FROM USER; //SessionB Console can see UNCOMMITTED transactions in Session A and in another Session. This is called dirty READ ID name 2 READ UNCOMMITTED 34 READ UNCOMMITTEDCopy the codeREAD COMMITTED(COMMITTED or unrepeatable reads, unreal reads)
This is the default isolation level used by most databases (mysql is not). This isolation level ensures that if a transaction is not completely successful (commit completion), the operations in the transaction will not be visible to other sessions.
-- ------------------------- read- cmmitted instance -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- SET the GLOBAL system ISOLATION LEVEL SET GLOBAL TRANSACTION ISOLATION LEVEL the READ COMMITTED; -- Session A START TRANSACTION; SELECT * FROM USER; UPDATE USER SET NAME="READ COMMITTED"; -- COMMIT; -- Session B SELECT * FROM USER; //Console OUTPUT: Id name 2 READ UNCOMMITTED 34 READ UNCOMMITTED -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- when the Session Id Name 2 READ COMMITTED 34 READ COMMITTEDCopy the codeThis validates that data at the Read COMMITTED level will not be visible to other sessions until a commit has been performed. We can see that Session B gets two different queries.
The Read COMMITTED isolation level solves the problem of dirty reads, but can result in two inconsistent reads to another Session (consistency changes because another Session performed a transaction).
REPEATABLE READ(REPEATABLE READ)
The unified read SQL is executed multiple times in a transaction and returns the same result. This isolation level solves the dirty read problem, the phantom read problem. Innodb uses a next-key lock on the “current read” to lock rows and insert locations that may generate phantom rows, preventing new data inserts from generating phantom rows. Detailed analysis is given below.
SERIALIZABLE
The strongest isolation level, by locking each row read in a transaction, writes and writes locks, guarantees no phantom read problems, but can lead to numerous timeouts and lock contention issues.
Multi-version concurrency control -MVCC
Multiple-version-concurrency – Control (MVCC) is a variant of row-level locking. It avoids locking in normal read situations, so the overhead is lower. Implementations vary, but they generally implement non-blocking reads, locking only the necessary rows for writes.
- Select * from table… .;
- Current read (i.e. read persistent data) Special read operations, insert/update/delete, belong to current read, process the current data, need to lock. select * from table where ? lock in share mode; select * from table where ? for update; insert; update ; delete;
Select… From where… The use of MVCC ensures that read snapshots (known as consistent Read by mysql) are read from a snapshot of data prior to the start of the current transaction, and updates after the start of the transaction will not be read. The details are detailed below in SELECT.
FOR LOCK read SELECT with FOR UPDATE or LOCK IN SHARE MODE, UPDATE or DELETE statements, consider whether the query is equivalent to a unique index.
Write lock -recordLock,gapLock, Next key lock
For equivalence queries that use unique indexes: for example, where columnA= “…” If the index on columnA is used, a row lock is placed on records that satisfy where (for update is exclusive,lock in shared is shared, and other writes are exclusive). Here is a row-level lock, record lock.
For range queries (use non-unique indexes) : for example: where columnA between 10 and 30, the next key lock will not insert data after 10 in other sessions, thus solving the phantom read problem.
Here’s the next key lock that will include all the rows involved. Next key lock=recordLock+gapLock, not only locks related data, but also locks boundaries, thus completely avoiding phantom read.
DML update table set columnA= “A” where columnB= “B”. If the columnB field does not have an index (or is not prefixed with a composite index), all records are locked, i.e. the lock table. If the statement is executed to perform an index of the columnB field, rows that meet where are locked (row locks).
INNODB MVCC is usually through two hidden behind the row data save column to realize (is actually three columns, and the third column is used for transaction rollback, it), a save the line to create the version number, another saved the updated version number (the last version is updated data) this version is the version number of each transaction, the increment.
This ensures that InnoDB reads data correctly without locking.
MVCC select Lockless operation and maintenance version number
Repeatable Read isolation (MVCC);
-
Select (snapshot read, read snapshot is read before the current transaction data.) : a.innodb select only rows with a version number older than the current version, ensuring that the data read is either committed before the transaction started (prior to the current version) or created in the transaction itself (equal to the current version).
B. The updated version number of the lookup row is either undefined or greater than the current version number (to ensure that the transaction can read old data), which ensures that the transaction reads data that has not been updated since the start of the current transaction. Note: select cannot have for update, lock in share statements. To achieve snapshot read effect, only rows that meet the following conditions are returned:
(Line creation version < = current version && (line update version ==null or line update version > current version))Copy the code-
Insert
InnoDB stores the current transaction version number as the creation version number of the row for the newly inserted row in this transaction.
-
Delete InnoDB stores the current transaction version number for each deleted row as a Delete mark for the row.
-
Update
Keep the current version number as the new version number of the updated data, and save the current version number as the updated version number of the old data row.
Current version - Write - > New data row creation version && Current version - Write - > old data update version ();Copy the codeDirty read vs. phantom read vs. unrepeatable read
Dirty read: Update data of an intermediate state that has not been committed by a transaction is read by another session. When a transaction is accessing the data, and the data were modified, and this change has not yet been submitted to the database (commit not executed), at this time, the session also visit this data, because the data is not submitted, then another session to read this data is dirty data, on the basis of operations to do dirty data may also be incorrect.
Unrepeatable Reads: Simply put, the data read in a transaction can change. ReadCommitted is also known as unrepeatable reads.
Multiple reads of the same data within the same transaction return different results. In other words, subsequent reads can read updated data committed by another session transaction. In contrast, repeatable reads ensure that the same data is read multiple times in the same transaction, that is, subsequent reads cannot read updates that have been committed by another session transaction.
Phantom read: a query is executed in session T1, and a new row is inserted in session T2, which meets the criteria for the query used by T1. T1 then retrieves the table again using the same query, but sees the new row that transaction T2 just inserted. This new line is called “phantom” because it appears suddenly to T1. InnoDB’s RR level cannot completely avoid phantom reading, as detailed below.
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- lead to -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- prerequisite: Mysql > CREATE TABLE 't_bitfly' (' id 'bigint(20) NOT NULL DEFAULT'0', 'value' varchar(32) DEFAULT NULL, PRIMARY KEY (' id ')) -- ensure that the current isolation level is the DEFAULT RR level mysql> select @@global.tx_ISOLATION, @@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 rowin set(0.00 SEC) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- to -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the session A | B session | | mysql> START TRANSACTION; | mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 SEC) | Query OK, 0 rows affected (0.00 SEC) | | mysql > SELECT * FROM the test. The t_bitfly; | mysql> SELECT * FROM test.t_bitfly; Emptyset (0.00 sec) | Empty set(0.00 SEC) | | mysql > INSERT INTO t_bitfly VALUES (1,'test'); | Query OK, 1 row affected (0.00 SEC) | | mysql > SELECT * FROM the test. The t_bitfly; | Emptyset(0.00 SEC) | | | mysql > commit; | Query OK, 0 rows affected (0.01 SEC) mysql > SELECT * FROM the test. The t_bitfly; | Emptyset(0.00 SEC) | - although you can see two execution results returned data consistent, | - but not read without illusions. And then look: | | mysql > INSERT INTO t_bitfly VALUES (1,'test'); |
ERROR 1062 (23000): |
Duplicate entry '1' for key 'PRIMARY'| | - yao is empty table, why the primary key repeat? - Unreal reading appears!! |Copy the codeHow to ensure that RR level absolutely does not generate hallucinations?
In use of select… Add a for update or lock in share mode statement to the WHERE statement. In fact, it locks the data that may cause illusory reading and prevents the data from being written.
Insert and UPDATE operations need to acquire a write lock first. The lock has already been acquired for the part that may produce phantom read. Therefore, the prerequisite for obtaining a write lock in another session is to release all locks generated by lock statements in the current session.
Mysql deadlock problem
Deadlock, is to produce a loop waiting chain, I wait for your resources, but you wait for my resources, we are waiting for each other, who do not release their own occupied resources, resulting in wireless waiting. Such as:
//Session A
START TRANSACTION;
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
COMMIT;
//Thread B
START TRANSACTION;
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
COMMIT;Copy the codeUPDATE account SET p_money=p_money-100 WHERE p_name= “Tim”; Lock row data where p_name= “Tim”; P_name = “bill”;
UPDATE account SET p_money=p_money+100 WHERE p_name= “bill”;
Lock data where p_name= “bill” and attempt to obtain data where p_name= “Tim”; At this point, the two threads enter a deadlock, neither can obtain their desired resources, enter the wireless wait, until the timeout!
Innodb_lock_wait_timeout Wait lock timeout rollback transaction: The intuitive way is that when two transactions are waiting for each other, if the wait time of one transaction exceeds a certain threshold, one transaction is rolled back and the other transaction can continue. This method is simple and effective. In InnoDB, the parameter Innodb_lock_wait_timeout is used to set the timeout.
Innodb also provides a wait-for-graph algorithm for active deadlock detection. Innodb also provides a wait-for-graph algorithm for active deadlock detection. Innodb also provides a wait-for-graph algorithm for active deadlock detection.
How to avoid deadlocks as much as possible
1) Access tables and rows in a fixed order. For example, if two transactions update data, transaction A updates data in the order of 1,2; Transaction B updates data in the order of 2,1. This is more likely to cause deadlocks.
2) Great things are divided into small things. Large transactions tend to be deadlocked, and if business permits, large transactions are broken down.
3) In the same transaction, try to lock all resources needed at one time to reduce the deadlock probability.
4) Lower the isolation level. If services allow, it is also a good choice to lower the isolation level, for example, to change the isolation level from RR to RC to avoid many deadlocks caused by gap locks.
5) Add a reasonable index to the table. You can see that if the index is not moved, a lock will be added to each row of the table, and the probability of deadlock will be greatly increased.
Explicit vs. Implicit Locks Implicit locks: All of the above locks are implicit locks that do not require additional statement locks. Display the lock:
SELECT ... LOCK IN SHARE MODE; SELECT ... FOR UPDATE(add exclusive lock);Copy the codeThe details have been given above.
You can view the status of the lock waiting using the following SQL
select * from information_schema.innodb_trx where trx_state="lock wait"; Or show engine innodb status;Copy the codeTransactions in mysql
show variables like "autocommit";
setautocommit=0; //0 indicates that AutoCommit is disabledsetautocommit=1; //1 indicates that AutoCommit is enabledCopy the code- Automatic commit (AutoCommit, mysql default)
Mysql defaults to the AutoCommit mode, which means that each SQL is a transaction and does not need to display the execution of the transaction. If autoCommit is turned off, a transaction is enabled by default for each SQL and will not be committed until an explicit “COMMIT” is executed.