Advance preparation
For the smooth development of the story, we first create a table and insert some records into the table. The following is the SQL statement:
CREATE TABLE hero (
number INT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (number),
KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;
INSERT INTO hero VALUES
(1, 'l Liu Bei', 'Shu'),
(3, 'z Zhuge Liang', 'Shu'),
(8, 'c Cao Cao', 'Wei'),
(15, 'x Xun Yu', 'Wei'),
(20, 's Sun Quan', 'Wu');
Now the records in the hero table are as follows:
mysql> SELECT * FROM hero; +--------+------------+---------+ | number | name | country | +--------+------------+---------+ | 1 | l Liu Bei | Shu | | 3 | z Zhuge Liang | Shu | | 8 | c Cao Cao | Wei | | 15 | x Xun Yu | Wei | | 20 | s Sun Quan | Wu | +--------+------------+---------+ 5 rows in set (0.01 sec)
phenomenon
In the Q & a group in the booklet, a classmate asked a question: it was said that there was a puzzle under the isolation level of READ COMMITTED. OK, first construct the environment and set the default isolation level of the current session to READ COMMITTED:
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec)
Transaction T1 executes first:
# In T1, the isolation level is READ COMMITTED mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM hero WHERE country = 'Wei' FOR UPDATE; +--------+---------+---------+ | number | name | country | +--------+---------+---------+ | 8 | c Cao Cao | Wei | | 15 | x Xun Yu | Wei | +--------+---------+---------+ 2 rows in set (0.01 sec)
The country column is not an index column, so this statement must be executed using the full table scanning method of scanning the cluster index. The EXPLAIN statement also proves our idea:
mysql> EXPLAIN SELECT * FROM hero WHERE country = 'Wei' FOR UPDATE; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | hero | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.02 sec)
We have learned the lock analysis of MySQL statements before. We know that under the READ COMMITTED isolation level, if the query statement is executed by full table scanning, the InnoDB storage engine will add a positive record lock to each record in turn, and test whether the record complies with the WHERE condition at the server layer. If not, release the lock on the record. In this example, the FOR UPDATE statement is used, and the X-type formal record lock must be added. Only two records meet the WHERE condition, so in the end, only X-type formal record locks are added to the two qualified records (that is, the two records with the values of 8 and 15 in the number column). Of course, we can use the SHOW ENGINE INNODB STATUS command to prove our analysis:
mysql> SHOW ENGINE INNODB STATUS\G ... Omitted a lot of content ------------ TRANSACTIONS ------------ Trx id counter 39764 Purge done for trx's n:o < 39763 undo n:o < 0 state: running but idle History list length 36 Total number of lock structs in row lock hash table 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 281479653009568, not started 0 lock struct(s), heap size 1160, 0 row lock(s) ---TRANSACTION 281479653012832, not started 0 lock struct(s), heap size 1160, 0 row lock(s) ---TRANSACTION 39763, ACTIVE 468 sec 2 lock struct(s), heap size 1160, 2 row lock(s) MySQL thread id 19, OS thread handle 123145470611456, query id 586 localhost 127.0.0.1 root TABLE LOCK table `xiaohaizi`.`hero` trx id 39763 lock mode IX RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39763 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000008; asc ;; 1: len 6; hex 000000009b4a; asc J;; 2: len 7; hex 80000001d3012a; asc *;; 3: len 7; hex 63e69bb9e6938d; asc c ;; 4: len 3; hex e9ad8f; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000009b4a; asc J;; 2: len 7; hex 80000001d30137; asc 7;; 3: len 7; hex 78e88d80e5bda7; asc x ;; 4: len 3; hex e9ad8f; asc ;; ... Omitted a lot of content
The transaction with id 39763 refers to T1. It can be seen that it adds X-mode x locks rec but not gap to the two records with heap no values of 4 and 5.
Then start a transaction T2 with the isolation level of READ COMMITTED, and execute it:
# In T2, the isolation level is READ COMMITTED mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM hero WHERE country = 'Wu' FOR UPDATE; (Enter blocking state)
Obviously, this statement will also be executed in the way of full table scanning, and the lock of each clustered index record will be obtained in turn. However, because the record with number value of 8 has been locked by T1 with X-type formal record lock, T2 can't get it, and can only be in the blocking state. At this time, the SHOW ENGINE INNODB STATUS can also prove our conjecture (only part is intercepted):
---TRANSACTION 39764, ACTIVE 34 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1160, 1 row lock(s) MySQL thread id 20, OS thread handle 123145471168512, query id 590 localhost 127.0.0.1 root Sending data SELECT * FROM hero WHERE country = 'Wu' FOR UPDATE ------- TRX HAS BEEN WAITING 34 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39764 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000008; asc ;; 1: len 6; hex 000000009b4a; asc J;; 2: len 7; hex 80000001d3012a; asc *;; 3: len 7; hex 63e69bb9e6938d; asc c ;; 4: len 3; hex e9ad8f; asc ;;
You can see that T2 is waiting to acquire the lock_mode X locks rec but not gap waiting on the record with heap no of 4.
The above is a very normal blocking logic, which we can analyze. However, if the UPDATE statement below is executed in T2:
# In T2, the isolation level is READ COMMITTED mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE hero SET name = 'xxx' WHERE country = 'Wu'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
WTF? Unexpectedly, there is no blocking, and the execution is so random and successful? The same WHERE condition, the same execution plan, how to select Is the locking of for UPDATE and UPDATE statements different?
reason
Ha ha, yes, it's really different. In fact, MySQL supports three types of read statements:
-
Common reading (also known as consistent reading, English Name: Consistent Read).
This refers to an ordinary SELECT statement without a SELECT statement of FOR UPDATE or LOCK IN SHARE MODE at the end. The execution method of ordinary reading is to generate ReadView and directly use MVCC mechanism to read without locking records.
Tip: for the SERIALIZABLE isolation level, if the autocommit system variable is set to OFF, the normal read statement will be changed to lock read, which is the same as adding LOCK IN SHARE MODE after the normal SELECT statement.
-
Locking Read (English Name: Locking Read).
This means that the transaction needs to obtain the lock corresponding to the record before reading the record. Of course, what type of lock to obtain depends on the isolation level of the current transaction, the execution plan of the statement, query conditions and other factors.
-
Semi consistent read.
This is a reading method sandwiched between normal reading and locked reading. It is only used when the UPDATE statement is used at the READ COMMITTED isolation level (or when the innodb_locks_unsafe_for_binlog system variable is turned on). The specific meaning is that when the UPDATE statement reads a record that has been locked by other transactions, InnoDB will read the latest submitted version of the record, and then judge whether the version matches the WHERE condition in the UPDATE statement. If not, the record will not be locked, so as to jump to the next record; If it matches, read the record again and lock it. This sub processing is only to make the UPDATE statement less blocked by other statements.
Tip: semi consistent reading is only applicable to locking clustered index records, not secondary index records.
Obviously, in the above nagging example, transaction T2 uses semi consistent reading when executing the UPDATE statement. It is judged that the value of the country column of the latest submitted version of the number column is 8 and 15, which is not 'Wu' in the WHERE condition in the UPDATE statement, so we skip them directly and lock them.
This knowledge point is easy to be ignored. Don't forget to consider semi consistent read when analyzing in the process of work
last
Xiaobian carefully prepared first-hand materials for you
**Click here for free **The above Java advanced architecture information, source code, notes and videos. Dubbo, Redis, design pattern, Netty, zookeeper, Spring cloud, distributed, high concurrency and other architecture technologies
[attachment] presentation of architecture books
- Analysis of 20 high frequency database problems in BAT interview
- Java interview dictionary
- Netty actual combat
- algorithm
BATJ interview points and advanced data of Java Architect
C loud, distributed, high concurrency and other architecture technologies
[attachment] presentation of architecture books
- Analysis of 20 high frequency database problems in BAT interview
- Java interview dictionary
- Netty actual combat
- algorithm
[external chain picture transferring... (img-tts3xdxf-1623734872740)]
BATJ interview points and advanced data of Java Architect
[external chain picture transferring... (IMG txscotha-1623734872742)]