mysql之行锁和表锁篇

mysql常用引擎有MYISAM和InnoDB，而InnoDB是mysql默认的引擎。MYISAM不支持行锁，而InnoDB支持行锁和表锁。

1，锁类型：

在mysql 的 InnoDB引擎支持行锁，与Oracle不同，mysql的行锁是通过索引加载的，即是行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁。

行锁：

针对数据表中行记录的锁，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。

间隙锁（Gap Lock）：

锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

临键锁（Next-Key Lock）：

行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。 1，索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。 2，索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。 3，索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。 注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

锁的优缺点：

表锁：不会出现死锁，发生锁冲突几率高，并发低。

行锁：也称为记录锁，会出现死锁，发生锁冲突几率低，并发高。

锁冲突：

例如说事务A将某几行上锁后，事务B又对其上锁，锁不能共存否则会出现锁冲突。（但是共享锁可以共存，共享锁和排它锁不能共存，排它锁和排他锁也不可以）

死锁：

例如说两个事务，事务A锁住了15行，同时事务B锁住了610行，此时事务A请求锁住610行，就会阻塞直到事务B施放610行的锁，而随后事务B又请求锁住15行，事务B也阻塞直到事务A释放15行的锁。死锁发生时，会产生Deadlock错误。

锁是对表操作的，所以自然锁住全表的表锁就不会出现死锁。

2.行锁的类型

行锁分 共享锁 和 排它锁。

共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

case1：select正常

case2：select…lock in share mode

case3：共享锁和排他锁

case4：排他锁和排他锁

上共享锁的写法：lock in share mode 例如： select math from zje where math>60 lock in share mode； 上排它锁的写法：for update 例如：select math from zje where math >60 for update；

3.行锁的实现

注意几点：

1. 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

2. 两个事务不能锁同一个索引，例如：

事务A先执行： select math from zje where math>60 for update; 事务B再执行： select math from zje where math<60 for update；

这样的话，事务B是会阻塞的。如果事务B把 math索引换成其他索引就不会阻塞，但注意，换成其他索引锁住的行不能和math索引锁住的行有重复。

3 . insert ，delete ， update在事务中都会自动默认加上排它锁。

会话1： begin； select math from zje where math>60 for update； 会话2： begin； update zje set math=99 where math=68； 阻塞...........

会话相当与用户

如上，会话1先把zje表中math>60的行上排它锁。然后会话2试图把math=68的行进行修改，math=68处于math>60中，所以是已经被锁的，会话2进行操作时，

就会阻塞，等待会话1把锁释放。当commit时或者程序结束时，会释放锁。

4、避免死锁

（1）合理设计索引

业务sql尽可能通过索引定位更少的行，减少锁竞争

（2）调整业务逻辑sql执行顺序

避免update/delete长时间持有锁的sql在事务前面

（3）避免大事务

尽量将大事务拆分成多个小事务来处理，小事务缩短锁定资源的时间，发生锁冲突的几率也降低

（4）在并发比较高的系统中，不要显示加锁，特别是在事务里显示加锁

（5）降低隔离级别

在业务允许的情况下，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。