Mysql的锁你知道多少个
共 12483字，需浏览 25分钟
·
2021-07-17 17:32
我们知道，数据也是一种供许多用户共享访问的资源。如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。从这一角度来说，锁对于数据库而言就显得尤为重要。接下来我们了解各种锁的特征。
1.1 表锁
表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好地避免困扰我们的死锁问题。InnoDB支持表级锁。但是默认的行锁，而且只有在查询或者其他SQL语句通过索引才会使用行锁。
1.2 行锁
行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。目前主要是Innodb使用行锁。根据使用方式也分为共享锁（S锁或者读锁）和排它锁（X锁或者写锁）。
1.3共享锁（读锁，S锁）
共享锁的具体逻辑如下案例：
若事务A对数据对象o加上S锁，则事务A可以读数据对象o但不能修改，其他事务只能再对数据对象o加S锁，而不能加X锁，直到事务A释放数据对象o上的S锁。这保证了其他事务可以读数据对象o，但在事务A释放数据对象o上的S锁之前不能对数据对象o做任何修改。
语法和案例如下：
# 加读锁
Lock table tablename read;
#释放锁
unlock table;
案例如下 ：
#session1 给表创建读锁（创建一个连接就是一个回话session）
mysql> lock table userinfo read;
Query OK, 0 rows affected (0.01 sec)
#可以读取当前表的数据
mysql> select * from userinfo ;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 27 |
| 2 | lisi | 27 |
| 3 | dd | NULL |
| 4 | dd | 2 |
+----+----------+------+
4 rows in set (0.00 sec)
#session1 因为给userinfo表加了读锁，所以不能读取其他表数据
mysql> select * from student_score;
ERROR 1100 (HY000): Table 'student_score' was not locked with LOCK TABLES
#session1 因为给表加的读锁，所以当前回话不能对表进行其他操作，
mysql> update userinfo set age=age+1;
ERROR 1099 (HY000): Table 'userinfo' was locked with a READ lock and can't be updated
#session2，在session1 没有释放锁之前，session2可以进行读取userinfo
mysql> select * from userinfo;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 27 |
| 2 | lisi | 27 |
| 3 | dd | NULL |
| 4 | dd | 2 |
+----+----------+------+
4 rows in set (0.00 sec)
#session2，在session1 没有释放锁之前，session2修改或删除表userinfo的任何数据都被阻塞
mysql> update userinfo set age=1 where name='dd';
。。。。一直等待，直到seesion1 释放锁
#session1 释放锁,session1在执行下面释放锁语句后，session2才会提交上面修改语句。
mysql> unlock tables;
1.4 排它锁（写锁,X锁）
共享锁的具体逻辑如下案例：
若事务A对数据对象o加上X锁，事务A可以读数据对象o也可以修改数据对象o，其他事务不能再对数据对象o加任何锁，直到事务A释放数据对象o上的锁。这保证了其他事务在事务A释放数据对象o上的锁之前不能再读取和修改数据对象o。
# 加写锁给表
Lock table tablename write;
# 加写锁给行
select ... for update;
#释放表锁
unlock table;
案例如下 ：
# session1 当前会话session1给表加写锁。
mysql> lock table userinfo write;
Query OK, 0 rows affected (0.00 sec)
#session1 当前会话可以修改此表数据
mysql> update userinfo set age=9;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
#session1 当前会话可以查询此表
mysql> select * from userinfo;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 9 |
| 2 | lisi | 9 |
| 3 | dd | 9 |
| 4 | dd | 9 |
+----+----------+------+
4 rows in set (0.00 sec)
#session2，在session1 没有释放锁之前，session2对表的任何数据任何操作（增删改查）userinfo的任何数据都被阻塞
mysql> update userinfo set age=1 where name='dd';
。。。。一直等待，直到seesion1 释放锁
如上是锁表，接下来我们看看给行加锁的效果：
#修改提交模式，改为手动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
#session1 当前会话给id=1的行加写锁。
mysql> select * from userinfo where id=1 for update;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 10 |
+----+----------+------+
1 row in set (0.00 sec)
#session2 当前会话2 可以查询当前表数据。
mysql> select * from userinfo;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 11 |
| 2 | lisi | 12 |
| 3 | dd | 12 |
| 4 | dd | 11 |
+----+----------+------+
4 rows in set (0.00 sec)
#session2 当前会话2 可以操作其他行数据。
mysql> update userinfo set age=12 where id=2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
#session2 当前会话2修改数据涉及到id=1的行，所以被阻塞知道行锁被释放
mysql> update userinfo set age=12 ;
。。。。一直被阻塞，直到sesssion1 执行commit语句。
#session1 回话1 提交数据，也就是释放锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
1.5意向锁
意向共享锁和意向排它锁总称为意向锁。意向锁的出现是为了支持Innodb支持多粒度锁。它是表级别的锁。两种锁含义如下：
1.意向共享锁：事务想要在获得表中某些记录的共享锁（读锁），需要在表上先加意向共享锁。
2.意向互斥锁：事务想要在获得表中某些记录的互斥锁（写锁），需要在表上先加意向互斥锁。
当我们需要给一个加表锁的时候，我们需要根据意向锁去判断表中有没有数据行被锁定，以确定是否能加成功。如果意向锁是行锁，那么我们就得遍历表中所有数据行来判断。如果意向锁是表锁，则我们直接判断一次就知道表中是否有数据行被锁定了。所以说将意向锁设置成表级别的锁的性能比行锁高的多。
所以一项所的作用就是当一个事务在需要获取资源的锁定时，如果该资源已经被排他锁占用，则数据库会自动给该事务申请一个该表的意向锁。如果自己需要一个共享锁定，就申请一个意向共享锁。如果需要的是某行（或者某些行）的排他锁定，则申请一个意向排他锁。
业务执行流程如下：
有了意向锁之后，事务A在申请行锁（写锁）之前，数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。明显提高性能。快速判断好此表有被锁的数据，所以，不能获取到此表的表级别的锁。
1.6 乐观锁
乐观锁不是数据库自带的，是需要我们自己写业务去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。
操作流程如下：
在表中的数据进行更新操作时，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。这样做的好处是避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统整体性能表现。
伪业务代码如下：
#首先获取修改之前的数据和版本号
SELECT data AS old_data, version AS old_version FROM …;
#进行修改，条件是当前数据的版本号和之前的老版本号一致，表示没有人进行过修改
Int updaterow= UPDATE SET data = new_data, version = new_version WHERE version = old_version
// 表示修改成功，拿到了乐观锁
if (updated row > 0) {
// 乐观锁获取成功，操作完成
} else {
// 乐观锁获取失败，回滚并重试
}
1.7 悲观锁
悲观锁指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）
案例如下：
#关闭自动提交后
set autocommit=0;
#开始事务
start transaction;
#查询当前商品信息，然后通过for update锁定数据防止其他事务修改
select status from goods where goodsid=1 for update;
#根据商品信息生成订单
insert into orders (id,goodsid) values (null,1);
#修改商品status为2
update t_goods set status=2;
#执行完毕,提交事务
commit;
上述案例就实现了悲观锁，悲观锁就是悲观主义者，它会认为我们在事务A中操作数据1的时候，一定会有事务B来修改数据1,所以，在第2步我们将数据查询出来后直接加上排它锁（X）锁，防止别的事务来修改事务1，直到我们commit后，才释放了排它锁。
需要注意的是，乐观锁保证了数据处理时的安全性但是更新有可能会失败，甚至是更新几次都失败，这是有风险的。所以如果写入居多，对吞吐要求不高，可使用悲观锁。而且要注意的是悲观锁加锁造成了开销增加，降低了性能，而且还会出现死锁。
1.8 间歇锁
间隙锁（Gap Lock）是Innodb在提交下为了解决幻读问题时引入的锁机制，（下面的所有案例没有特意强调都使用可重复读隔离级别）幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁，间隙锁就是解决这类问题的。在可重复读隔离级别下，数据库是通过行锁和间隙锁共同组成的（next-key lock），来实现的。加锁规则有以下特性：
1.加锁的基本单位是（next-key lock）,他是前开后闭原则
2.插叙过程中访问的对象会增加锁
3.索引上的等值查询--给唯一索引加锁的时候，next-key lock升级为行锁
4.索引上的等值查询--向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
5.唯一索引上的范围查询会访问到不满足条件的第一个值为止。
id(主键) | name | age |
5 | name1 | 18 |
10 | name2 | 19 |
15 | name3 | 20 |
20 | name4 | 21 |
25 | name5 | 22 |
以上数据为了解决幻读问题，更新的时候不只是对上述的五条数据增加行锁，还对于中间的取值范围增加了6间隙锁，（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+∞]
如下案例：
#session1 操作会话session1 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#session1 更新id=11的数据，获取行锁。
mysql> select * from userinfo where id=11 for update;
Empty set (0.00 sec)
#session2 操作会话session2 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#session2 在会话session2 新增数据主键为12
mysql> insert into userinfo values(12,'hh',18);
.... 一直处于阻塞状态
#如果等待时间过长，session1没有释放锁，会抛出如下异常。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
如上事务session1和事务session2时，事务session1会对数据库表增加（10，15]这个区间锁，这时insert id = 12 的数据的时候就会因为区间锁（10，15]而被锁住无法执行。
特别注意，间歇锁，如果操作不当会出现死锁。如下：
事务A | 事务B |
begin; | |
begin; insert into user value(7,7,7) | |
insert into user value(7,7,7) |
如上，事务A获取到（5,10]之间的间隙锁不允许其他的DDL操作，在事务提交，间隙锁释放之前，事务B也获取到了间隙锁（5,10]，这时两个事务就处于死锁状态
1.9记录锁
记录锁，它封锁索引记录，作用于唯一索引上。如果执行这条语句（select * from userinfo where id=4 for update;）它会在id=4的索引记录上加锁，以阻止其他事务插入，更新，删除id=4的这一行。就是说，当一个会话执行这条语句之后，其它会话，执行下面这几种语句，都会处于阻塞状态。
select * from userinfo where id=1 for update;
delete from userinfo where id=4;
insert into userinfo values(4,'hh',18);
阻塞时间过长可能出抛出如下错误：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
需要注意的是，如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。
1.10临键锁
临键锁，作用于非唯一索引上，是记录锁与间隙锁的组合，如下图表格。
id(主键) | name | age |
5 | name1 | 18 |
10 | name2 | 19 |
15 | name3 | 20 |
20 | name4 | 21 |
25 | name5 | 22 |
它的封锁范围，既包含索引记录，又包含索引之前的区间，即（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+∞]。
如下在事务中执行。
Update userinfo SET age=19 where id= 10;
Select * from userinfo where id=10 FOR UPDATE;
这两个语句都会锁定（5,10]，(10,15)这两个区间。即， InnoDB 会获取该记录行的 临键锁 ，并同时获取该记录行下一个区间的间隙锁。
1.11 死锁
死锁是指两个或两个以上事务在执行过程中因争抢锁资源而造成的互相等待的现象。如下图7-3所示的操作流程。
上图所示，这两种情况即为死锁产生的常规情景。事务A等着事务B释放锁，事务B等着事务A释放锁。就会出现相互等待，一直等待下去。避免这种问题的办法有两种，一种是，等待事务超时，主动回滚。第二种是进行死锁检查，主动回滚某条事务，让别的事务能继续走下去。可以使用相关指令查询当前查看正在被锁的事务。
#查看正在被锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#可以通过如下指令，杀死当前事务进程，释放锁。trx_id字段对应上面语句结果对应的第一个字段值
kill trx_id;
作者 | YLY969
来源 | https://www.cnblogs.com/yiming15/p/15001258.html