[MySQL]有趣的锁，TX，TM傻傻分不清楚！

实验道具：

1）数据库

create database test;

2）表

create table testtx1(no int,name varchar(20),sales float(8,2));

3）主键，索引

alter table testtx1 add primary key (no);         建立主键约束，也就是建立主键索引
create index idx_City_name on City(Name);      //普通二级索引，索引Cardinality高

4）数据

delimiter $$  

create procedure pre()

begin

declare i int;

set i=1;

while i<500 do

insert into testtx1 (no,name,sales)

values(i,'dd',i); 

set i=i+1;

end while;

end 

$$

call pre();

delimiter ;

        上一步实验道具准备好了，从这一步我们来通过mysql客户端连接上数据库。

按照STEP1中，依次建立数据库、表、主键，然后插入数据。

mysql-session1 >select count(*) from test.testtx1;

+----------+

| count(*) |

+----------+

|      499 |

+----------+

1 row in set (0.00 sec)

mysql-session1 >show indexes from test.testtx1\G

*************************** 1. row ***************************

        Table: testtx1

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: no

    Collation: A

  Cardinality: 499

     Sub_part: NULL

       Packed: NULL

         Null: 

   Index_type: BTREE

      Comment: 

Index_comment: 

      Visible: YES

   Expression: NULL

1 row in set (0.00 sec)

        通过上述两条命令，我们查出来，表testtx1有499条记录，有一个主键索引（MySQL主键索引没有主键索引名），且所存储的数据特点是name列值都一样，sales列值和主键列值一样唯一。

     见证奇迹的时刻到了。

将MySQL的自动提交功能在每个窗口都设置为取消（临时会话级别）。

> set autocommit=0;

在会话1执行以下update语句。这里sales可指定表中任意一个值都可以。

mysql-session1 >update testtx1 set name='TX-LOCK' where sales=499.00;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

在会话2执行以下update语句。

mysql-session2 >update testtx1 set name='TX-LOCK' where sales=498.00;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

咦？ 啥情况？

现象是当我在会话1执行了一条普通的update语句时，在没有提交的情况，竟然将另一个会话执行同一张表不同行的更新给阻塞了！！！（ERROR 1205报错，是等待一段时间超时的错误）

天理何在，你俩是连体婴儿么？

随后我怀疑是是不是表锁住了，然后做了一个锁阻塞时的innodb存储引擎的状态收集。

mysql> show engine innodb status\G

*************************** 1. row ***************************

  Type: InnoDB

  Name: 

Status: 

=====================================

2019-07-07 22:36:51 0x7f2a400ca700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 8 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 5 srv_active, 0 srv_shutdown, 645 srv_idle

srv_master_thread log flush and writes: 0

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 1

OS WAIT ARRAY INFO: signal count 1

RW-shared spins 0, rounds 0, OS waits 0

RW-excl spins 1, rounds 30, OS waits 1

RW-sx spins 0, rounds 0, OS waits 0

Spin rounds per wait: 0.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx

------------

TRANSACTIONS

------------

Trx id counter 10260

Purge done for trx's n:o < 10258 undo n:o < 0 state: running but idle

History list length 5

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421294474144480, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 10259, ACTIVE 345 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)

MySQL thread id 10, OS thread handle 139819439634176, query id 153 localhost root updating

update testtx1 set name='TX-LOCK' where sales=498.00

------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 576 index PRIMARY of table `test`.`testtx1` trx id 10259 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 4; hex 80000001; asc     ;;

 1: len 6; hex 00000000246b; asc     $k;;

 2: len 7; hex 810000009f0110; asc        ;;

 3: len 2; hex 6464; asc dd;;

 4: len 4; hex 0000803f; asc    ?;;

------------------

---TRANSACTION 10258, ACTIVE 353 sec

2 lock struct(s), heap size 1136, 500 row lock(s)

MySQL thread id 8, OS thread handle 139819440215808, query id 145 localhost root

看到TRANSACTION 10259这段中标红色部分，分析这段话大概意思是主键索引正在等待一个独占锁。（蒙蒙的！！！）

进一步再看阻塞SQL的执行计划是什么样的：

mysql-session1 >explain update testtx1 set name='TX-LOCK' where sales=499.00\G

*************************** 1. row ***************************

           id: 1

  select_type: UPDATE

        table: testtx1

   partitions: NULL

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 4

          ref: NULL

         rows: 499

     filtered: 100.00

        Extra: Using where

1 row in set, 1 warning (0.00 sec)

type：指访问方法，index即通过索引访问。

key：指实际上用的索引，这里primary即主键索引。

rows：指扫描行数，这里用的是索引，那么即扫描499行索引（全索引扫描）。

filtered：指优化器预测rows中n%的数据满足where条件，n为filtered值。单表查询忽略此值。

也就是说，在会话1中，更新sales=499.00一条数据，其实是对索引做了全扫描。但还是解释不清楚为什么会锁住其他行的记录。

网上搜了很多，答案五花八门，没有说服力，在仔细读了MySQL官方文档后，找到了问题的答案：

问题在于Locking read上，翻译过来为读锁，包括对表行的读和索引行的读。在update或者delete时，都会产生这个读锁。而读锁在MySQL默认事务隔离级别中，是会阻塞对扫描包括的行的DML操作的。

结合我们当前这个例子，update时，主键索引全扫描，所以就会阻塞整个索引，也就是阻塞整张表。

官方文档的截图还说了，也会阻塞新插入的数据，因此也做了一下实验，验证确实是不能做插入操作。