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 an inexplicable thing had happened 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 cluster indexes. 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, test whether the record complies with the WHERE condition at the server layer, and release the lock on the record if not. 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 the number value of 8 has been locked by T1 with an X-type formal record lock, T2 can't get it. It can only be blocked. At this time, the SHOW ENGINE INNODB STATUS can also prove our conjecture (only some are 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 normal blocking logic, which can be analyzed. 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? There was no blocking, and it was executed so casually and successfully? 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 common read is to generate ReadView, directly use MVCC mechanism to read, and will not lock the record.

  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 (English Name: semi consistent read).

  This is a read mode sandwiched between normal read and locked read. 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). Specifically, 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, the record is read again and locked. 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 do not 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

If you think it's helpful, give me a compliment. Thank you very much~

Due to space constraints, the detailed information of pdf documents is too comprehensive, and there are too many details, so only the screenshots of some knowledge points are roughly introduced, and there are more detailed contents in each small node! If you need a program, ape (yuan) can help you like + comment 666, Click here to get it for free

[external chain picture transferring... (img-y5cW3dxw-1627093829620)]

Due to space constraints, the detailed information of pdf documents is too comprehensive, and there are too many details, so only the screenshots of some knowledge points are roughly introduced, and there are more detailed contents in each small node! If you need a program, ape (yuan) can help you like + comment 666, Click here to get it for free