MySQL锁--02---行锁 (记录锁(Record Locks))

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前置知识

行锁特点

• 偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：

1. 支持事务
2. 采用了行级锁

行锁支持事务

ACID

并发事务带来的问题

事务隔离级别

常看当前数据库的事务隔离级别：

SHOW VARIABLES LIKE ‘tx_isolation’;

MySQL-行锁

InnoDB 实现了以下两种类型的行锁。

共享锁（S）：

又称为读锁，简称S锁，

• 共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据
• 但是只能读不能修改。

排他锁（X）：

又称为写锁，简称X锁，排他锁就是不能与其他锁并存

• 如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，
• 但是获取排他锁的事务是可以对数据就行读取和修改。

InnoDB 加锁规则

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加写锁（排它锁 x锁)；

对于普通SELECT语句，InnoDB不会加任何锁；

给记录集加共享锁或排他锁

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE
1
2

InnoDB 行锁争用情况

show status like ‘innodb_row_lock%’;

• 当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

最后可以通过 SELECT * FROM information_schema.INNODB_TRX\G;

来查询正在被锁阻塞的sql语句

行锁到底锁住的是什么？

InnoDB 的行锁，就是通过锁住索引来实现的

问题一：为什么表里面没有索引的时候，实验一锁住一行数据会导致锁表？

案例1 ：

• 这个实验操作是操作没有索引的t1，t1里面有4条数据：1、2、3、4。

• 现在我们在两个会话里面手工开启两个事务。在第一个事务里面，我们通过where id =1
  锁住第一行数据。在第二个事务里面，我们尝试给id=3的这一行数据加锁，大家觉得能成功吗？

• 第一个事务锁住了id=1的这行数据，为什么我不能操作id=3的数据呢？我们再来操作一条不存在的数据，插入id=5。它也被阻塞了。
• 实际上这里整张表都被锁住了。

分析：

1. 如果我们定义了主键(PRIMARYKEY)，那么 InnoDB 会选择主键作为聚集索引
2. 如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引
3. 如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增

所以，实验一为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了。

问题二：为什么通过唯一索引给数据行加锁，主键索引也会被锁住？

• 在辅助索引里面， 索引存储的是二级索引和主键的值。 比如name=4，存储的是name的索引和主键id的值4。
• 而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟我们检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。
  

小结一下：

• 行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁
• 除了直接在主键索引加锁，我们还可以通过辅助索引找到相应主键索引后再加锁

行锁— 案例 1

建表: test_innodb_lock

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;
 
insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');


select * from test_innodb_lock;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

加索引

create index test_innodb_a_ind on test_innodb_lock(a);
 
create index test_innodb_lock_b_ind on test_innodb_lock(b);
1
2
3

设置: 手动提交事务

set autocommit = 0

跟新

行锁— 案例 2

错误操作导致 ==> 索引失效 ==> 行锁升级为表锁

正常情况

不规范的sql,导致行锁变表锁

由于在column字段b上面建了索引，如果没有正常使用，会导致行锁变表锁

分析:

1. b列是索引列,且b列字段是varchar 类型
2. 写sql 语句时的时,忘记加’'符号
3. mysql底层会自动类型转化,帮助把int 转化为varchar 类型
4. 但转化的同时会索引失效,将行锁变表锁

mysql底层会自动类型转化,转化的同时会索引失效

INNODB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁

总结

• InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。
• 但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议：

• 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少索引条件，及索引范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可使用低级别事务隔离（但是需要业务层面满足需求）