mysql行级锁

行级锁探究

mysql锁按颗粒级别分类

首先从锁的颗粒级别来看可分为三种：表级，页级，行级。从引擎的角度看Innodb支持表级锁和行级锁，myisam只支持表级锁。
其中行级锁又分为：共享锁和排他锁。

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

使用共享锁的方法是在select ... lock in share mode,只适用查询语句。

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

使用排他锁的方法师在sql末尾加上for update，顺便一提在innodb引擎中，会默认在update,delete这种操作加上for update。

在mysql innodb中，行级锁的实现其实是依靠其对应的索引，所以如果操作的行并有用到索引，那么用的还是表级锁。

以下是我做了一些实验去验证这两个锁的特性：
首先建一张表：

测试表

其中id是主键，name是一条不带任何索引的列。
表中内容如下：

test表内容

• 测试行级锁和索引的关联
  是否行级锁只对有索引的操作有效？
  我尝试对一条不用到索引的sql加上排他锁，按之前的理论这一操作将会锁表

set autocommit=0;
begin;
select * from test where name = 'xcm' for update;

set autocommit=0;
begin;
select * from test where name = 'zhengmq'for UPDATE;

执行结果如下：

查询1
查询2
根据实测结果查询1正常查出结果，而查询2卡住了，看来理论是正确的，因为查询1没有锁行，而是锁住了整个表。

• 测共享锁
  是否一旦数据加上共享锁，他的数据将只能被读，但不能被修改？
  下面两段sql，一个事务查询并加上共享锁，另一个事务尝试修改数据。

set autocommit=0;
begin;
select * from test where id = 1 lock in share mode

UPDATE test SET name='xcm' WHERE id = 1 

执行结果：

查询1结果
查询2结果
额外再试一下当查询2是一个查询操作：
当查询2是查询
共享锁可以共享查询。

实操结果也证实了 数据加上共享锁，他的数据将只能被读，但不能被修改。

• 测试排他锁
  是否数据加上排他锁，他的数据将不能被其他事物修改或查询？

set autocommit=0;
begin;
select * from test where id = 1 for update

select * from test where id = 1 for update

执行结果：

查询1结果
查询2结果
结果证实了查询1锁住了这条记录，其他事物将无法查询和修改这个数据。

• 测试排他锁和共享锁是否互斥

set autocommit=0;
begin;
select * from test where id = 1 for update

select * from test where id = 1 lock in share mode;

先给一行数据加上排他锁，能再加上共享锁吗？答案是不能：

查询2结果

那么反过来呢，加上共享锁，能再加上排他锁吗？经测试也是不可行的，图就不贴了，不然文章就太长了。

简单总结一下一上的测试结果吧：

1. 行级锁只对有索引的操作有效
2. 加上共享锁后，其他事务将只能查询不能修改
3. 加上排他锁后，其他事务不能查询也不能修改
4. 排他锁和共享锁两者互斥。

发生死锁如何查询

数据库死锁我之前也遇到过，当时只知道怎么查出死锁的sql并定位到程序中，这次通过这篇文章自己也整理了行级锁的各种特性，对产生死锁的原因有了更好的理解。
下面我先构造一种死锁的情况：
设有查询1和查询2
查询1先执行

set autocommit=0;
select * from test where id=1 for update;

查询2先执行

set autocommit=0;
select * from test where id=2 for update;

查询1再执行

select * from test where id=2 for update;

查询2再执行

select * from test where id=1 for update;

产生死锁的原因就是查询1已经拿到id=1的行级锁尝试拿id=2的行级锁，而查询2已经拿到id=2的行级锁尝试拿id=1的行级锁。

查询2死锁

当业务逻辑遇到这种情况，如何揪出这种sql？查询innodb状态即可：show ENGINE innodb status
死锁信息也将会在status记录：

innodb状态
下面是刚才那段死锁的信息：

 ------------------------
        LATEST DETECTED DEADLOCK
        ------------------------
        2019-10-18 16:09:37 0x4064
        *** (1) TRANSACTION:
        TRANSACTION 1521, ACTIVE 35 sec starting index read
        mysql tables in use 1, locked 1
        LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
        MySQL thread id 31, OS thread handle 24968, query id 7387 localhost ::1 root statistics
        select * from test where id=2 for update
        *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 53 page no 3 n bits 72 index PRIMARY of table `testxcm`.`test` trx id 1521 lock_mode X locks rec but not gap waiting
        Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
        0: len 4; hex 80000002; asc     ;;
        1: len 6; hex 0000000005c6; asc       ;;
        2: len 7; hex bb000001310110; asc     1  ;;
        3: len 7; hex 7a68656e676d71; asc zhengmq;;

        *** (2) TRANSACTION:
        TRANSACTION 1522, ACTIVE 32 sec starting index read, thread declared inside InnoDB 5000
        mysql tables in use 1, locked 1
        3 lock struct(s), heap size 1136, 2 row lock(s)
        MySQL thread id 32, OS thread handle 16484, query id 7391 localhost ::1 root statistics
        select * from test where id=1 for update
        *** (2) HOLDS THE LOCK(S):
        RECORD LOCKS space id 53 page no 3 n bits 72 index PRIMARY of table `testxcm`.`test` trx id 1522 lock_mode X locks rec but not gap
        Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
        0: len 4; hex 80000002; asc     ;;
        1: len 6; hex 0000000005c6; asc       ;;
        2: len 7; hex bb000001310110; asc     1  ;;
        3: len 7; hex 7a68656e676d71; asc zhengmq;;

        *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 53 page no 3 n bits 72 index PRIMARY of table `testxcm`.`test` trx id 1522 lock_mode X locks rec but not gap waiting
        Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
        0: len 4; hex 80000001; asc     ;;
        1: len 6; hex 0000000005d8; asc       ;;
        2: len 7; hex 290000013b041b; asc )   ;  ;;
        3: len 3; hex 78636d; asc xcm;;

        *** WE ROLL BACK TRANSACTION (2)

其中各种信息含义就不再赘述，百度或者翻译即可。

参考 MySQL/InnoDB中，乐观锁、悲观锁、共享锁、排它锁、行锁、表锁、死锁概念的理解和mysql共享锁与排他锁