06 Mysql锁机制

假装自己会头腔共鸣

已于 2024-07-22 20:26:04 修改

阅读量1.1k

点赞数 23

分类专栏： MYSQL 文章标签： mysql 数据库

于 2024-07-22 17:26:08 首次发布

本文链接：https://blog.csdn.net/lizhihappy01/article/details/140613685

版权

MYSQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁的分类

乐观锁与悲观锁

乐观锁适合读操作较多的场景，悲观锁适合写操作较多的场景，如果在写操作较多的场景使用乐观锁会导致比对次数过多，影响性能

悲观锁

这种锁其实是从性能的角度上进行的分类，我理解是从锁的实现方式上进行的分类，比如在RR这个隔离级别下，事物A如果执行一个sql语句：

update T set balance = balance + 50 where id = 1

那么mysql一定是锁住当前行，事物B想要执行，只有等待事务A执行完成才可以执行，这就是悲观锁，一直在等待，是否执行完成。

乐观锁

如果是RC这个隔离级别，事务A在第一次查询出来的时候，会查询出来一个版本字段，比如说是1

select balance,version from T where id = 1;

这个版本字段由所有事务来控制，比如事务B，执行了一个update语句，这行的version字段就会+1那么就是2，当事务A再次想要执行update，这个sql语句的时候，就会对比版本字段，是否和当初查询出来的相同，如果相同就执行update，如果不相同则重新执行，其实就会有一个while循环，在事务最外层，他就是不断在重试，相对于悲观锁，这种锁更加主动，这就是乐观锁

读锁与写锁

读锁（共享锁，S锁(Shared)）

针对同一份数据，多个读操作可以同时进行而不会互相影响，比如：

select * from T where id=1 lock in share mode

写锁（排它锁，X锁(eXclusive)）

select * from T where id=1 for update

意向锁

又称I锁，针对表锁，主要是为了提高加表锁的效率，是mysql数据库自己加的。当有事务给表的数据行加了读锁或者写锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

表锁页锁行锁

--建表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`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

--手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
--查看表上加过的锁
show open tables;
--删除表锁
unlock tables;

页锁

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB相对于MYISAM的最大不同有两点：

InnoDB支持事务（TRANSACTION）
InnoDB支持行级锁

注意，InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁)

比如我们在RR级别执行如下sql

select * from account where name = 'lilei' for update;   --where条件里的name字段无索引

则其它Session对该表任意一行记录做修改操作都会被阻塞住。

PS：关于RR级别行锁升级为表锁的原因分析

因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其它事务修改(不可重复读问题) 或间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录