mysql数据库的行级锁有几种_Mysql 行级锁、表级锁、死锁

锁，在计算机中，是协调多个进程或线程并发访问某一资源的一种机制。在数据库当中，当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

就像酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他使用完毕才可以再次使用。

1、锁的分类

从性能上分为乐观锁和悲观锁

对数据库操作类型分，分为读锁和写锁(都属于悲观锁)

读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会相互影响。

写锁(排它锁)：当前写操作没有完成前，他会阻断其他写锁和读锁。

从数据操作的颗粒度，分为行锁、页锁、表锁

2、 三锁

相对于其他的数据库而言，MySQL的锁机制比较简单，最显著的特点就是不同的存储引擎支持不同的锁机制。根据不同的存储引擎，MySQL中锁的特性可以大致归纳如下：

行锁

表锁

页锁

MyISAM

√

BDB

√

√

InnoDB

√

√

2.1 表锁(偏读)

表锁偏向MyISAM存储引擎，开销小，加锁快，无思索，锁定粒度大，发生锁冲突的概率最高，并发度最低。

2.1.1 基本操作

建表SQL

CREATE TABLE `mylock` (

`id` INT (11) NOT NULL AUTO_INCREMENT,

`NAME` VARCHAR (20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE = MyISAM DEFAULT CHARSET = utf8;

插入数据

INSERT INTO `mylock` (`id`, `NAME`) VALUES ('1', 'a');

INSERT INTO `mylock` (`id`, `NAME`) VALUES ('2', 'b');

INSERT INTO `mylock` (`id`, `NAME`) VALUES ('3', 'c');

INSERT INTO `mylock` (`id`, `NAME`) VALUES ('4', 'd');

手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

2.1.2 案例分析(加读锁)

这里以已经创建好的mylock表为例

lock table mylock read;

image.png

此时当前session和其他session都可以读该表；当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待。(这里可以新开一个连接进行更新测试)

2.1.3案例分析(加写锁)

这里以已经创建好的mylock表为例

lock table mylock write;

image.png

当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

2.1.4 案例结论

MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。

对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作 总结：简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

2.2 行锁(偏写)

行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MYISAM的最大不同有两点：

一是支持事务(TRANSACTION)；

二是采用了行级锁。

由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

2.2.1行锁支持事务

事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性(Atomicity)表示最小的执行单元，不能再切割。事务的原子性保证执行过程要么全部成功，要么全部失败。

一致性(Consistency)一致性是指事务将数据库从一种一致性转换到另外一种一致性状态，在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。

隔离性(Isolation)并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

持久性(Durability)一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失。

并发事务处理带来的问题

更新丢失(Lost Update)

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。

脏读(Dirty Reads)

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。

一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

​ 3. 不可重读(Non-Repeatable Reads)

在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性

​ 4. 幻读(Phantom Reads)

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

脏读是事务B里面修改了数据

幻读是事务B里面新增了数据

事务隔离级别

脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。

隔离级别

脏读

不可重复读

幻读

READ-UNCOMMITTED

✓

✓

✓

READ-COMMITTED

✕

✓

✓

REPEATABLE-READ

✕

✕

✓

SERIALIZABLE

✕

✕

✕

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。

同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

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

-- mysql5.7及之后版本

show variables like 'transaction_isolation';

-- 或者

select @@transaction_isolation;

-- mysql5.7之前版本

show variables like 'tx_isolation';

-- 或者

select @@tx_isolation;

-- 查看系统当前隔离级别

select @@global.tx_isolation;

设置事务隔离级别：

-- 设置当前会话隔离级别

set session transaction isolation level (read uncommitted|read committed|repeatable read|seriaizable);

-- 摄像当前系统隔离级别

set global transaction isolation level (read-uncommitted|read-committed|repeatable-read|seriaizable);

2.2.2行级锁示例演示

建表及插入语句

drop table if exists `account`;

create table `account`(

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',

`name` varchar(45) DEFAULT NULL COMMENT '姓名',

`balance` INT(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `account` (`name`, `balance`) VALUES ('lilei', '450');

INSERT INTO `account` (`name`, `balance`) VALUES ('hanmei', '16000');

INSERT INTO `account` (`name`, `balance`) VALUES ('lucy', '2400');

读未提交

打开一个客户端A，并设置当前事务模式为read uncommitted(未提交读)，查询表account的初始值：

image.png

在客户端A的事务提交之前，打开另一个客户端B，并设置当前事务模式为read uncommitted(未提交读)更新表account

image.png

image.png

3. 这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

image.png

4. 一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据：

image.png

读已提交

打开一个客户端A，并设置当前事务模式为read committed(未提交读)，查询表account的所有记录：

image.png

在客户端A的事务提交之前，打开另一个客户端B，更新表account：

image.png

这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题

image.png

客户端B的事务提交

image.png

客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题

image.png

可重复读

打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的所有记录

image.png

在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交

image.png

在客户端A查询表account的所有记录，与步骤(1)查询结果一致，没有出现不可重复读的问题!

image.png

在客户端A，接着执行update balance = balance - 50 where id = 1，balance没有变成100-50=50，zs的balance值用的是步骤(2)中的50来算的，所以是0，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读(历史版本)；insert、update和delete会更新版本号，是当前读(当前版本)。

image.png

再次用客户端B开启事务插入一条数据后提交；

image.png

在客户端A查询表account的所有记录，没有 查出 新增数据，所以没有出现幻读

image.png

验证幻读、在客户端A执行update account set balance=888 where id = 4;能更新成功，再次查询能查到客户端B新增的数据

image.png

在客户端A执行update account set balance=888 where id = 4;能更新成功，再次查询能查到客户端B新增的数据

串行化

打开一个客户端A，并设置当前事务模式为serializable，查询表account的初始值：

image.png

打开一个客户端B，并设置当前事务模式为serializable，插入一条记录报错，表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

image.png

Mysql默认级别是repeatable-read，有办法解决幻读问题吗？

****间隙锁在某些情况下可以解决幻读问题****

要避免幻读可以用间隙锁在Session_1下面执行

update account set name = 'zhuge' where id > 10 and id <=20;

则其他Session没法插入这个范围内的数据;

2.2.3 案例结论

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

2.2.4 行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like'innodb_row_lock%';

对各个状态量的说明如下：

Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg: 每次等待所花平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间

Innodb_row_lock_waits:系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：

Innodb_row_lock_time_avg (等待平均时长)

Innodb_row_lock_waits (等待总次数)

Innodb_row_lock_time(等待总时长)

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

2.3 死锁

死锁异常Deadlock found when trying to get lock;

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

1)以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。

2)大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4)降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5)为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

执行下面的语句演示一个死锁产生的情况。

-- 设置隔离级别

set session transaction isolation level repeatable read;

-- Session_1执行：

select * from account where id=1 for update;

-- Session_2执行：

select * from account where id=2 for update;

-- Session_1执行：

select * from account where id=2 for update;

-- Session_2执行：

select * from account where id=1 for update;

实际的执行顺序如下，分别用两个会话；session1先锁住id=1;session2先锁住id=2的行；然后session再去获取id=2的锁，session2去获取id=1的锁，此时便会发生死锁。

Session1

Session2

select * from account where id=1 for update;

select * from account where id=2 for update;

select * from account where id=2 for update;

select * from account where id=1 for update;

从下图中可以看出产生了死锁，并自动释放了锁。

image.png

查看近期死锁日志信息：

show engine innodb status;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

2.3.1 锁与索引的关系

假设我们有一张消息表(msg)，里面有3个字段。假设id是主键，token是非唯一索引，message没有索引。

id: bigint

token: varchar(30)

message: varchar(4096)

drop table if EXISTS `msg`;

create table `msg`(

`id` BIGINT(20) not null auto_increment comment '主键ID',

`token` varchar(30) default null comment 'token',

`message` VARCHAR(4096) default null comment '消息内容',

PRIMARY KEY (`id`),

key `token` (`token`)

)ENGINE INNODB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ;

insert into msg values(1,'aaa','你好');

insert into msg values(2,'axd','怎么申请退款');

insert into msg values(3,'cvs','怎么处理');

insert into msg values(4,'asd','订单号多少');

insert into msg values(5,'cvs','谢谢');

innodb对于主键使用了聚簇索引，这是一种数据存储方式，表数据是和主键一起存储，主键索引的叶结点存储行数据。对于普通索引，其叶子节点存储的是主键值。

img

下面分析下索引和锁的关系。

delete from msg where id=2；

由于id是主键，因此直接锁住整行记录即可。

img

delete from msg where token=’cvs’;

由于token是二级索引，因此首先锁住二级索引(两行)，接着会锁住相应主键所对应的记录；

img

delete from msg where message=订单号是多少’；

message没有索引，所以走的是全表扫描过滤。这时表上的各个记录都将添加上X锁。

img

可以发现在执行更新或删除时where后的条件一定要走索引，否则数据量特别大的时候，造成全表锁定的时候，会造成性能损耗，降低了并发数。

锁与隔离级别的关系

为了保证并发操作数据的正确性，数据库都会有事务隔离级别的概念ACID，

1. 未提交读 Read uncommitted

2. 已提交读 Read committed(RC)

3. 可重复读 Repeatable read(RR)

4. 可串行化 Serializable

这些上面已经说过了，InnoDB默认的隔离级别是可重复度，我们较常使用的是RC和RR。

​ 提交读(RC)：只能读取到已经提交的数据。

可重复读(RR)：在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。

innodb的RR隔离级别如果要避免幻读需要借助gap锁(间隙锁：说白了就是锁住一个区间，如果要插入的数据在锁定的区间，则会等待)

在事务A执行：update msg set message=‘订单’ where token=‘asd’;

innodb首先会和RC级别一样，给索引上的记录添加上X锁，此外，还在非唯一索引’asd’与相邻两个索引的区间加上锁。

​ 这样，当事务B在执行insert into msg values (null,‘asd',’hello’); commit;时，会首先检查这个区间是否被锁上，如果被锁上，则不能立即执行，需要等待该gap锁被释放。这样就能避免幻读问题。

img

不同表相同记录行锁冲突

这种情况很好理解，事务A和事务B操作两张表，但出现循环等待锁情况。

img

相同表记录行锁冲突

这种情况比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。

img

2.3.2不同索引锁冲突

这种情况比较隐晦，事务A在执行时，除了在二级索引加锁外，还会在聚簇索引上加锁，在聚簇索引上加锁的顺序是[1,4,2,3,5]，而事务B执行时，只在聚簇索引上加锁，加锁顺序是[1,2,3,4,5]，这样就造成了死锁的可能性。

img

2.3.3 gap锁冲突

innodb在RR级别下，如下的情况也会产生死锁，比较隐晦。

img

这里冲突的原因主要是由于要插入的token=aad在刚好在token值为aaaasd之间，当事务A和事务B执行完更新语句后，在token=aaaasd之间的字符都会被锁定，所以此时插入aad会失败，而插入bsa没有问题。

image.png

总结

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

死锁：因争夺资源而造成的一种互相等待的现象，若无外力作用它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程；表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的InnoDB。