mysql锁的相关知识_mysql在执行select会加读锁吗?-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_40799202/article/details/111867588

1、锁的分类

1.1从对数据操作的类型来分

读锁（共享锁）:针对同一份数据，多个读操作可以同时进行而不会互相影响。　　　　
1.如果某一个会话对A表加了read锁，则该会话可以对A表进行读操作、不能进行写操作；且该会话不能对其他表进行读、写操作。
2.即如果给A表加了读锁，则当前会话只能对A表进行读操作。

某会话给某个表加了读锁，所有的会话都能对该表进行读操作，不能进行写操作，除非该会话释放读锁。

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

当前会话（会话0）可以对加了写锁的表进行任何操作（增删改查）；但是不能操作（增删改查）其他表
其他会话：对会话0中加写锁的表可以进行增删改查的前提是：等待会话0释放写锁

1.2 从锁粒度划分。

一般分为：行锁、表锁

**（1）行锁：**访问数据库的时候，锁定整个行数据，防止并发错误。如InnoDB存储引擎使用行锁

（2）表锁: 访问数据库的时候，锁定整个表数据，防止并发错误。如MyISAM存储引擎使用表锁

行锁和表锁的区别：

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

1. 3 补充悲观锁和乐观锁

**（1）悲观锁：**顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

（2）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

（3）悲观锁和乐观锁的区别：

乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能
乐观锁还适用于一些比较特殊的场景，例如在业务操作过程中无法和数据库保持连接等悲观锁无法适用的地方

2.InnoDB引擎的行锁模式和加锁方法

InnoDB行锁是通过索引上的索引项来实现的(锁住的是索引项，而不是记录本身)

这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。

InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

如下图所示：查询窗口1手动提交的情况下修改数据但是不提交

然后打开一个新窗口进行查询是可以查出数据来的

但是修改数据则一直在运行，是因为第一个查询窗口的update语句加了排它锁

而使用select…for update 来加排它锁的时候也是不能加的，以为查询窗口1已经给这行数据加了排它锁了

但是换成其他行使用select…for update就可以查询到数据

给上图的查询语句手动提交事务然后select…for update加排它锁

然后新开一个窗口进行更新该行，可以发现更新不了程序一直在运行

不使用主键来进行查找加锁

可以发现另外的查询窗口不能修改这个表的数据，不同的行也不可以

所以可以推断出如果不是使用索引来进行查找条件的话则会给整个表加锁

而一个查询语句加了共享锁的话，其他的事务只能也加共享锁或者不加锁（即普通select语句）就可以查到数据但是不能修改

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

3.表锁、意向锁

mysql存在表锁加锁方式如下，可以加读锁或者写锁

#一定要设置为0 不然mysql不会给表加锁
SET AUTOCOMMIT=0;
#格式： 
LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},……] 

#例子:
lock tables db_a.tbl_aaa read; 　// 锁定了db_a库中的tbl_aaa表

#sql语句。。。

#要自己提交 不能先解锁  解锁的话会隐含的提交事务
commit;

#解锁： 
unlock tables;

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向锁 Intention Locks，意向锁相互兼容

1、表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”

2、意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。

这两中类型的锁共存的问题考虑这个例子：
事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。
数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

数据库要怎么判断这个冲突呢？
step1：判断表是否已被其他事务用表锁锁表
step2：判断表中的每一行是否已被行锁锁住。
注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。
于是就有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。在意向锁存在的情况下，
上面的判断可以改成
step1：不变
step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

注意：申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。

下图表示意向锁和共享锁、排他锁的兼容关系。
意思是当事务A对某个数据范围（行或表）上了“某锁”后，另一个事务B是否能在这个数据范围上“某锁”。

在这里插入图片描述

4.行锁算法

1.Record Lock

单个行记录上的锁。Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

2.Gap Lock

锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

间隙锁（Gap Lock）一般是针对非唯一索引而言的，gap 锁只锁记录之间的范围，不对记录产生影响。

有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock）

A. 将事务隔离级别设置为READ COMMITED

B. 将参数innodb_locks_unsafe_for_binlog设置为1

3.Next Key Lock

是record lock+gap lock的结合除了锁住记录本身还锁住了索引之间间隙 来保证不会产生幻读

mysql的默认事务隔离级别是可重复读不能保证幻读的情况不发生，所以通过采取next-key lock来保证幻读的不发生

幻读：是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

例如 select * from person where age>20

这时候如果另外一个事务插入一条大于age>20的数据的话那么前面的事务查询出来的数据前后就不一样了

接下来，我们来通过一个例子解释一下。

CREATE TABLE z (
    a INT,
    b INT,
    PRIMARY KEY(a),    // a是主键索引
    KEY(b)    // b是普通索引
);
INSERT INTO z select 1, 1;//插入数据a=1,b=1
INSERT INTO z select 3, 1;
INSERT INTO z select 5, 3;
INSERT INTO z select 7, 6;
INSERT INTO z select 10, 8;
1234567891011

这时候在会话A中执行 SELECT * FROM z WHERE b = 3 FOR UPDATE，索引锁定如下：(对主键索引加上了记录锁锁住5这个数，对普通索引加上了间隙锁锁住(1,3],3,(3,6]这三段)

这时候会话B执行的语句落在锁定范围内的都会进行waiting.
第一个SQL语句不能执行,因为在会话A中执行的SQL语句已经对聚集索引中列a=5的值加上排他锁,因此执行会被阻塞。
第二个SQL语句,主键插人4,没有问题,但是插人的辅助索引值2在锁定的范围(1,3)中,因此执行同样会被阻塞。
第三个SQL语句,插人的主键6没有被锁定,5也不在范围(1,3)之间。但插入的值5在另一个锁定的范围(3,6)中,故同样需要等待。

SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4, 2;
INSERT INTO z SELECT 6, 5;
123

用户可以通过以下两种方式来显示的关闭Gap Lock：

将事务的隔离级别设为 READ COMMITED。
将参数innodb_locks_unsafe_for_binlog设置为1

从上面的例子可以看出来，Gap Lock的作用是为了阻止一个事务A读取(1，100)之间的记录两次，事务B在A前后读取的事件中插入记录插入到(1,100)内，设计它的目的是用来解决Phontom Problem（幻读问题）。在MySQL默认的隔离级别（Repeatable Read）下，InnoDB就是使用它来解决幻读问题。（事实上，考虑另一种常见情况，事务 A读取数据20到50之间的数据，数据库只有20和50两个数满足要求，持有锁(20,50)和(50,无穷)两把锁，由于没有间隙锁，事务 B 插入数据30，此时事务A再次读取数据，导致在同一个事务内两次读取数据不一致，产生幻读。）

何时使用行锁，何时产生间隙锁