7.MySql锁机制_mysql乐观锁cas机制-CSDN博客

本文链接：https://blog.csdn.net/Csdn_xzh/article/details/142685461

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

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

一、锁分类

从性能上划分：分乐观锁(版本对比或者CAS机制)和悲观锁；乐观锁适合读比较多的场景，悲观锁适合写比较多的场景；如果写比较多的场景使用乐观锁较多，会导致性能降低。
对数据操作粒度上分：分为表锁、页锁和行锁
对数据库的操作类型上分：分为读锁和写锁(都属于悲观锁)，还有意向锁

1.乐观锁

乐观锁（Optimistic Locking）是一种并发控制的策略。它假设在大多数情况下，数据的并发访问不会发生冲突，所以在操作数据时不会对数据进行加锁（不像悲观锁那样提前加锁）

通过在数据行上添加版本号或者时间戳，读取时从获取版本号，每次更新时数据时，比对数据库的版本号和之前读取的版本号是否一致，一致则更新，不一致则不更新。

2.悲观锁

悲观锁（Pessimistic Locking）是一种并发控制的方法，它假定在数据处理过程中，数据很可能被其他并发事务修改，所以在数据操作开始之前就对数据进行枷锁，以防止其他事务对其并发进行访问，直到当前事务执行完并释放锁。

相比乐观锁，悲观锁每次操作都要对数据加锁、解锁，性能和效率会很低。

3.表锁

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

基本操作

‐‐建表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),表名称 read(write); 
‐‐查看表上加过的锁 
show open tables; 
‐‐删除表锁 unlock tables;

4.页锁

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

5.行锁

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

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

InnoDB支持事务(TRANSACTION)
InnoDB支持行索引

注意：InnoDB的行索引是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，其他对该表的任意操作都会造成堵塞。

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

因为在RR级别下，我们要解决的是不可重复读和幻读的问题，所以在遍历扫秒索引的时候，为了防止扫秒过的索引被其他事务修改（不可重复读问题）或者间隙被其他事务插入记录(幻读问题)，从而导致数据不一致问题，所以MySql的解决方案就是把所有扫秒过的索引记录和间隙都锁上，并不是将整张表加表锁，因为不一定能加上表表，可能会有其他事务锁住了表里的其他记录。

6.间隙锁

间隙锁是两个值之间的间隙上进行锁定的锁。间隙锁在可重复度隔离级别下生效的。

MySql默认级别是可重复读，是用来解决幻读问题的。

假设account表里数据如下：

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行如下sql：

select * from account where id = 18 for update;

则其他Session没法在这个(10,20)这个间隙范围里插入任何数据。

如果执行下面这条sql：

select * from account where id = 25 for update;

则其他Session没法在这个(20,正无穷)这个间隙范围里插入任何数据。

也就是说，只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，不锁边界记录，这样就能防止其它Session在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。

7.临键锁

Next-Key Locks是行锁与间隙锁的组合。

总结

MYISAM在执行查询语句SELECT前，会自动给涉及的表加锁，在执行update、insert、delete操作会自动给涉及的表加锁。

InnoDB在执行SELECT前不会给表加锁，会在执行update、insert、delete操作时自动给行数据加锁。

读锁会堵塞写操作，但是不会阻塞读操作，写锁会堵塞读和写操作。

InnoDB存储引擎由于实现了行锁，虽然锁机制的实现会带来性能方面的损耗也有可能比表锁的性能更高，但是在整体并发的处理方面要远远由于MYISAM的表锁定。当系统并发量高的时候InnoDB的性能会远远高于MyISAM。但是如果使用不到，性能就会差很多。

二、锁等待分析

1.命令

通过检查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.查看INFORMATION_SCHEMA系统库锁相关数据表

-- 查看事务 
select * from INFORMATION_SCHEMA.INNODB_TRX; 
-- 查看锁，8.0之后需要换成这张表performance_schema.data_locks 
select * from INFORMATION_SCHEMA.INNODB_LOCKS; 
-- 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits 
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
-- 释放锁，trx_mysql_thread_id
可以从INNODB_TRX表里查看到 kill trx_mysql_thread_id 
-- 查看锁等待详细信息 show engine innodb status;

3.死锁问题分析

set tx_isolation='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; 
查看近期死锁日志信息：show engine innodb status;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉。