Technology sharing, semi consistent reading and optimization of Update

What is semi consistent reading

First look at the official description:

• It is an optimization of read operation (consistent read) used in Update statement, which is a combination of consistent read and RC transaction isolation level.
• When the matched record in the where condition of the Update statement has been locked, it will go to the InnoDB engine layer to read the corresponding row record again to determine whether it really needs to be locked (for the first time, InnoDB needs to return the latest submitted version first).
• Only under RC transaction isolation level or InnoDB is set_ locks_ unsafe_ for_ Binlog = 1.
• innodb_ locks_ unsafe_ for_ The binlog parameter has been removed in version 8.0 (it can be seen that this is a parameter that may cause data inconsistency and is not recommended officially).

Test cases

The strength of InnoDB engine is that it can support transactions perfectly, and the consistency of transactions is guaranteed by transaction isolation level and concurrent transaction lock. Next, let's use two test cases to observe the impact of semi consistent reading on transactions.

• RC isolation level, 3 sessions execute transaction statements

-- Create test table root@localhost:mysqld.sock[zlm] <5.7.30-log>create table zlm.t(id int,sal int) engine innodb default character set utf8mb4; Query OK, 0 rows affected (0.06 sec) root@localhost:mysqld.sock[zlm] <5.7.30-log>show create table zlm.t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `sal` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.01 sec) -- Write test data (create stored procedure to insert data) root@localhost:mysqld.sock[zlm] <5.7.30-log>drop procedure if exists zlm.proc_t; Query OK, 0 rows affected (0.00 sec) root@localhost:mysqld.sock[zlm] <5.7.30-log>delimiter $$ root@localhost:mysqld.sock[zlm] <5.7.30-log>create procedure zlm.proc_t() -> begin -> declare i int default 1; -> declare j int default 100; -> while i<11 do -> insert into t(id,sal) values(i,j); -> set i=i+1; -> set j=j+100; -> end while; -> end $$ Query OK, 0 rows affected (0.01 sec) root@localhost:mysqld.sock[zlm] <5.7.30-log>delimiter ; root@localhost:mysqld.sock[zlm] <5.7.30-log>select * from t; +------+------+ | id | sal | +------+------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | 400 | | 5 | 500 | | 6 | 600 | | 7 | 700 | | 8 | 800 | | 9 | 900 | | 10 | 1000 | +------+------+ 10 rows in set (0.00 sec) -- open RC Isolation level root@localhost:mysqld.sock[zlm] <5.7.30-log>set @@global.tx_isolation='read-committed'; Query OK, 0 rows affected, 1 warning (0.00 sec)

Note that since version 8.0.3, TX has been removed_ Isolation parameter, parameter name only supports transaction_isolation

-- Open two new Session(set up global After parameter, only valid for new connection) -- confirm Session 1 Isolation level and threads for ID root@localhost:mysqld.sock[(none)] <5.7.30-log>show variables like 'tx_isolation'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.01 sec) root@localhost:mysqld.sock[(none)] <5.7.30-log>select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 8 | +-----------------+ 1 row in set (0.00 sec) -- Session 1 Execute the currently read Select sentence root@localhost:mysqld.sock[zlm] <5.7.30-log>begin;select * from t where id>3 and id<6 for update; Query OK, 0 rows affected (0.00 sec) +------+------+ | id | sal | +------+------+ | 4 | 400 | | 5 | 500 | +------+------+ 2 rows in set (0.00 sec) -- View locking details (parameters to be set innodb_status_output_locks=on，Otherwise, we can't see it IX Lock) -- In transaction 1314 of thread 8, a table level insert intent lock was obtained IX，2 Record locks, corresponding to id=4,id=5 These two records of ---TRANSACTION 1313, ACTIVE 7 sec 2 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 8, OS thread handle 140086065960704, query id 277 localhost root TABLE LOCK table `zlm`.`t` trx id 1313 lock mode IX RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1313 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000515; asc ;; 2: len 7; hex b0000001240110; asc $ ;; 3: len 4; hex 80000004; asc ;; 4: len 4; hex 80000190; asc ;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000205; asc ;; 1: len 6; hex 000000000516; asc ;; 2: len 7; hex b1000001250110; asc % ;; 3: len 4; hex 80000005; asc ;; 4: len 4; hex 800001f4; asc ;; -- confirm Session 2 Isolation level and threads for ID root@localhost:mysqld.sock[(none)] <5.7.30-log>show variables like 'tx_isolation'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.00 sec) root@localhost:mysqld.sock[(none)] <5.7.30-log>select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 9 | +-----------------+ 1 row in set (0.00 sec) -- Session 2 Execute the currently read Select sentence root@localhost:mysqld.sock[zlm] <5.7.30-log>begin;select * from t where id = 7 for update; Query OK, 0 rows affected (0.00 sec) ERROR 1205 (HY000): Unknown error 1205 ## 1205 indicates that the lock waiting timeout, the 5.7.30 of the latest GA under the tucking slot, will only throw out a code when it meets the mistake, no error description, and inconvenience to make complaints about it. -- View lock details -- innodb Before the lock wait timeout, you can see that the 1314 transaction of thread 9 is requesting and waiting for a record lock, id=4 This record of ---TRANSACTION 1314, ACTIVE 3 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 9, OS thread handle 140086065690368, query id 282 localhost root Sending data select * from t where id = 7 for update ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1314 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000515; asc ;; 2: len 7; hex b0000001240110; asc $ ;; 3: len 4; hex 80000004; asc ;; 4: len 4; hex 80000190; asc ;; ------------------ TABLE LOCK table `zlm`.`t` trx id 1314 lock mode IX RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1314 lock_mode X locks rec but not gap RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1314 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000515; asc ;; 2: len 7; hex b0000001240110; asc $ ;; 3: len 4; hex 80000004; asc ;; 4: len 4; hex 80000190; asc ;; -- innodb After the lock wait timeout, observe again. The transaction of transaction 1314 of thread 9 still hasn't ended t Table holding IX Lock, and still waiting id=4 Row lock release of ---TRANSACTION 1314, ACTIVE 453 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 9, OS thread handle 140086065690368, query id 282 localhost root TABLE LOCK table `zlm`.`t` trx id 1314 lock mode IX RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1314 lock_mode X locks rec but not gap -- confirm Session 3 Isolation level and threads for ID +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.01 sec) root@localhost:mysqld.sock[(none)] <5.7.30-log>select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 10 | +-----------------+ 1 row in set (0.00 sec) -- Session 3 implement Update sentence root@localhost:mysqld.sock[zlm] <5.7.30-log>begin;update t set sal = sal + 1 where id = 7; Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec)

When Session 1 transaction is still not finished, Session 3 transaction is not blocked and can be executed normally

-- View the execution plan of three statements root@localhost:mysqld.sock[zlm] <5.7.30-log>explain select * from t where id>3 and id<6 for update; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) root@localhost:mysqld.sock[zlm] <5.7.30-log>explain select * from t where id = 7 for update; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) root@localhost:mysqld.sock[zlm] <5.7.30-log>explain update t set sal=sal+1 where id=7; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | UPDATE | t | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set (0.00 sec)

It is expected that since the t table has no index, the execution plan must be to scan the whole table, that is to say, every record read will be uplink locked. So why does Session 1 only lock the two items with id=4 and id=5, and not lock the whole table? For the same record with request id=7, why can Session 2 fail to acquire the lock resource and Session 3 can execute successfully? Maybe you can quickly draw a conclusion from the lock analysis above. Since Session 1 only occupies row locks with id=4 and id=5, there will be no conflict when Session 3 requests id=7 (it seems reasonable) Why is Session 2 locked when it requests id=7?

With these questions, let's move on to the second case

• RC isolation level. This time, the Select statement executed by Session 1 does not have a where condition

-- Session 1 Execute the currently read Select sentence root@localhost:mysqld.sock[zlm] <5.7.30-log>begin;select * from t for update; Query OK, 0 rows affected (0.00 sec) +------+------+ | id | sal | +------+------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | 400 | | 5 | 500 | | 6 | 600 | | 7 | 700 | | 8 | 800 | | 9 | 900 | | 10 | 1000 | +------+------+ 10 rows in set (0.00 sec) -- View lock details -- Thread 8's 1317 transaction gets one IX Table lock and 10 X Record lock, that is, lock all 10 records in the table -- t There is no index on the table, MySQL Created by default GEN_CLUST_INDEX The clustered index of, but the statement does not add where Condition, only full table scanning can be performed. For each read record, a record lock (full table record lock, equivalent to a table lock) must be added to the clustered index ---TRANSACTION 1317, ACTIVE 5 sec 2 lock struct(s), heap size 1136, 10 row lock(s) MySQL thread id 8, OS thread handle 140086065960704, query id 312 localhost root TABLE LOCK table `zlm`.`t` trx id 1317 lock mode IX RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1317 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000201; asc ;; 1: len 6; hex 00000000050e; asc ;; 2: len 7; hex ab0000011f0110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 80000064; asc d;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000202; asc ;; 1: len 6; hex 00000000050f; asc ;; 2: len 7; hex ac000001200110; asc ;; 3: len 4; hex 80000002; asc ;; 4: len 4; hex 800000c8; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000203; asc ;; 1: len 6; hex 000000000514; asc ;; 2: len 7; hex af000001230110; asc # ;; 3: len 4; hex 80000003; asc ;; 4: len 4; hex 8000012c; asc ,;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000515; asc ;; 2: len 7; hex b0000001240110; asc $ ;; 3: len 4; hex 80000004; asc ;; 4: len 4; hex 80000190; asc ;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000205; asc ;; 1: len 6; hex 000000000516; asc ;; 2: len 7; hex b1000001250110; asc % ;; 3: len 4; hex 80000005; asc ;; 4: len 4; hex 800001f4; asc ;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000206; asc ;; 1: len 6; hex 000000000517; asc ;; 2: len 7; hex b2000001260110; asc & ;; 3: len 4; hex 80000006; asc ;; 4: len 4; hex 80000258; asc X;; Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000207; asc ;; 1: len 6; hex 000000000518; asc ;; 2: len 7; hex b3000001270110; asc ' ;; 3: len 4; hex 80000007; asc ;; 4: len 4; hex 800002bc; asc ;; Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 000000000519; asc ;; 2: len 7; hex b4000001280110; asc ( ;; 3: len 4; hex 80000008; asc ;; 4: len 4; hex 80000320; asc ;; Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000209; asc ;; 1: len 6; hex 00000000051a; asc ;; 2: len 7; hex b5000001290110; asc ) ;; 3: len 4; hex 80000009; asc ;; 4: len 4; hex 80000384; asc ;; Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000000020a; asc ;; 1: len 6; hex 00000000051b; asc ;; 2: len 7; hex b60000012a0110; asc * ;; 3: len 4; hex 8000000a; asc ;; 4: len 4; hex 800003e8; asc ;; -- Session 2 Execute the currently read Select sentence root@localhost:mysqld.sock[zlm] <5.7.30-log>begin;select * from t where id = 7 for update; Query OK, 0 rows affected (0.00 sec) ERROR 1205 (HY000): Unknown error 1205

The same as the previous case 1, it is also a lock waiting timeout exit

-- View lock details -- This time, transaction 1318 of thread 9 has been locked since record 1 ---TRANSACTION 1318, ACTIVE 4 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 9, OS thread handle 140086065690368, query id 315 localhost root Sending data select * from t where id = 7 for update ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1318 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000201; asc ;; 1: len 6; hex 00000000050e; asc ;; 2: len 7; hex ab0000011f0110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 80000064; asc d;; ------------------ TABLE LOCK table `zlm`.`t` trx id 1318 lock mode IX RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1318 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000201; asc ;; 1: len 6; hex 00000000050e; asc ;; 2: len 7; hex ab0000011f0110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 80000064; asc d;; -- Session 3 implement Update sentence root@localhost:mysqld.sock[zlm] <5.7.30-log>begin;update t set sal = sal + 1 where id = 7; Query OK, 0 rows affected (0.00 sec) ERROR 1205 (HY000): Unknown error 1205

Unlike case 1, the Update statement also encountered a lock wait timeout to exit

-- View lock details -- this time Session 3 request id=7 The record lock of the Session 1 Holding not released, resulting in Session 3 Lock wait timeout ---TRANSACTION 1319, ACTIVE 14 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 8 row lock(s) MySQL thread id 10, OS thread handle 140086066231040, query id 322 localhost root updating update t set sal = sal + 1 where id = 7 ------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1319 lock_mode X locks rec but not gap waiting Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000207; asc ;; 1: len 6; hex 000000000518; asc ;; 2: len 7; hex b3000001270110; asc ' ;; 3: len 4; hex 80000007; asc ;; 4: len 4; hex 800002bc; asc ;; ------------------ TABLE LOCK table `zlm`.`t` trx id 1319 lock mode IX RECORD LOCKS space id 23 page no 3 n bits 80 index GEN_CLUST_INDEX of table `zlm`.`t` trx id 1319 lock_mode X locks rec but not gap waiting Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000207; asc ;; 1: len 6; hex 000000000518; asc ;; 2: len 7; hex b3000001270110; asc ' ;; 3: len 4; hex 80000007; asc ;; 4: len 4; hex 800002bc; asc ;;

case analysis

Since there is no index on the t table, the statements executed by the three sessions are all full table scans. At the RC transaction isolation level, these statements need to initiate the current read operation (read the latest committed transaction version on the t table), and all the records read need to be locked (i.e. row lock or InnoDB lock). In most cases, there is no Gap at the RC isolation level Lock, so the next key lock is less likely to appear, which is more friendly to high concurrency scenarios).

• Session 1: at first, you need to lock each record. Because there is no need to maintain repeatable read and lock Gap, when you return to MySQL Server layer and filter through where conditions, you only need to lock the records with id=4 and id=5.
• Session 2: read and lock the record from id=1. When reading the record with id=4, because Session 1 locks the record with id=4 first, it can't lock it anymore. We see that it has been waiting for the X lock with id=4 until the lock is over. Why is id=4, not id=5? Because the records are read by clustering index one by one, the lock also needs to be added one by one. When the lock resource of the previous record is not obtained, the next record will not be locked.
• Session 3: similarly, you need to lock all the records you read at the beginning. Since the records with id=7 do not conflict with the row locks on id=4 and id=5, here you can use the optimization feature of semi consistent read on Update to release the row locks on id=7 in advance, so Update will not be blocked and transactions can be executed normally.

• Session 1: Select statement does not use where condition, and all records accessed through full table scanning cannot be filtered through MySQL Server layer, so all records of table t are X-locked.
• Session 2: because Session 1 has x-locked all records, the current Select operation read by session 2 is blocked because it is unable to obtain X-lock of any records.
• Session 3: similarly, the full record X lock held by Session 1 makes the where condition of session 3 fall into the matching range, which means that the row with id=7 of Session 1 needs to be updated and must be locked, so the Update of session 3 is blocked.

summary

• At the RC transaction isolation level, the Update statement can take advantage of the semi consistent read feature and make more than one judgment. When the record matched by the where condition does not conflict with the record in the current lock holding transaction, InnoDB will be released in advance Lock, although it violates the two-stage lock protocol, can reduce lock conflicts and improve the concurrency of transactions, which is a good optimization behavior.

Reference link

https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html https://my.oschina.net/JKOPERA/blog/1929335

Add new comment

0 comments