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结果
那么反过来呢，加上共享锁，能再加上排他锁吗？经测试也是不可行的，图就不贴了，不然文章就太长了。
简单总结一下一上的测试结果吧：
- 行级锁只对有索引的操作有效
- 加上共享锁后，其他事务将只能查询不能修改
- 加上排他锁后，其他事务不能查询也不能修改
- 排他锁和共享锁两者互斥。
发生死锁如何查询
数据库死锁我之前也遇到过，当时只知道怎么查出死锁的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)
其中各种信息含义就不再赘述，百度或者翻译即可。
