当前位置:首页 > mysql > 正文内容

mysql中锁详解读这一篇就够了

phpmianshi4年前 (2017-04-07)mysql625


InnoDB的七种锁


1. 自增锁(Auto-inc Locks)

2. 共享/排他锁(Shared and Exclusive Locks)

3. 意向锁(Intention Locks)

4. 插入意向锁(Insert Intention Locks)

5. 记录锁(Record Locks)

6. 间隙锁(Gap Locks)

7. 临键锁(Next-Key Locks)


相关名词

|--表级锁(锁定整个表)

|--页级锁(锁定一页)

|--行级锁(锁定一行)

|--共享锁(S锁,MyISAM 叫做读锁)

|--排他锁(X锁,MyISAM 叫做写锁)

|--悲观锁(抽象性,不真实存在这个锁)

|--乐观锁(抽象性,不真实存在这个锁)


默认事务隔离级别为可重复读(Repeated Read, RR)


InnoDB的锁,与索引类型,事务的隔离级别相关


自增锁


    自增锁是一种特殊的表级别锁(table-level lock)

    从MySQL 5.1开始,InnoDB中提供了一种轻量级互斥量的自增长实现机制

    同时InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式

    进而提高自增长值插入的性能

    

innodb_autoinc_lock_mode和插入类型有关,在介绍它之前,我们先来看看都有哪些插入类型


+ **insert-like**  任何会产生新记录的语句,都叫上insert-like,

比如:INSERT,INSERT ...SELECT,REPLACE,REPLACE ...SELECT,and  LOAD DATA,总之包括:simple-inserts,bulk-inserts,mixed-mode inserts.


+ **simple inserts**

插入的记录行数是确定的:比如:insert into values,replace

但是不包括: INSERT ... ON DUPLICATE KEY UPDATE.


+ **bulk inserts**

插入的记录行数不能马上确定的,比如: INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA


+ **mixed-mode inserts**

这些都是simple-insert,但是部分auto increment值给定或者不给定. 例子如下(where c1 is an AUTO_INCREMENT column of table t1):

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 

+ 另外一种 mixed-mode insert 就是 INSERT ... ON DUPLICATE KEY UPDATE


#### innodb_autoinc_lock_mode 的说明



    0 这个表示tradition 传统

    1 这个表示consecutive 连续 (默认)

    2 这个表示interleaved 交错 (MySQL 8.0下默认值为2)


1.1 tradition(innodb_autoinc_lock_mode=0) 模式:

  

+ 它提供了一个向后兼容的能力

+ 在这一模式下,**所有的insert语句("insert like")** 都要在语句开始的时候得到一个表级的auto_inc锁,在语句结束的时候才释放这把锁,**注意呀,这里说的是语句级而不是事务级的,** 一个事务可能包含有一个或多个语句。

+ 它能保证值分配的可预见性,与连续性,可重复性,这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。

+ 由于在这种模式下auto_inc锁一直要保持到语句的结束,所以这个就影响到了并发的插入。


 


1.2 consecutive(innodb_autoinc_lock_mode=1) 模式:


+ 对simple insert做了优化,由于simple insert一次性插入值的个数可以立马得到确定,所以mysql可以一次生成几个连续的值,用于这个insert语句,**该值会用互斥量mutex去对内存(dict_table_struct.autoinc)中的计数器进行累加操作。**mysqld重启后,从哪里得到AUTO_INCREMENT呢?内存值肯定是丢失了,实际上MySQL采用执行类似select max(id)+1 from t1;方法来得到AUTO_INCREMENT

+ **Mysql8.0优化:将自增主键的计数器持久化到redo log中。每次计数器发生改变,都会将其写入到redo log中。如果数据库发生重启,InnoDB会根据redo log中的计数器信息来初始化其内存值。**

+ 对于bulk inserts 还是使用传统表锁,该配置下如果不考虑回滚,对于自增列的增长还是连续的。

+ 深入思考:为什么这个模式要产生表级别的锁呢?因为:他要保证bulk insert自增id的连续性,防止在bulk insert的时候,被其他的insert语句抢走auto increment值。

+ 这个模式的好处是auto_inc锁不要一直保持到语句的结束,只要语句得到了相应的值后就可以提前释放锁


 


1.3 interleaved(innodb_autoinc_lock_mode=2) 模式


+ 由于这个模式下已经没有了auto_inc锁,对于所有insert-like 自增长值得产生都是通过互斥量mutex,所以这个模式下的性能是最好的

+ 但是它也有一个问题,就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的,所以基于statement-base replication会出现问题,因此使用这个模式,任何时候都应该使用row-base replication,才能保证最大的并发性能和主从一致性



    不要没事去更新一个auto_increment列的值,否则自增时,有可能跟你更新的值冲突,导致插入失败


共享/排他锁



    共享/排它锁是标准的行级锁(row-level locking)



1. 事务拿到某一行记录的共享S锁,才可以读取这一行;


2. 事务拿到某一行记录的排它X锁,才可以修改或者删除这一行;


3. 多个事务可以拿到一把S锁,读读可以并行;


4. 而只有一个事务可以拿到X锁,写写/读写必须互斥;


5. 共享/排它锁的潜在问题是,不能充分的并行,解决思路是数据多版本


意向锁


概念

    意向锁,是一个表级别的锁(表级-自动)。表示事务正在读或写某一行记录,而不是整个表。所以意向锁之间不会产生冲突,真正的冲突在加行锁时检查。

   

解决的问题,为什么要有意向锁

    为了方便检测表级锁和行级锁之间的冲突,就引入了意向锁,主要目的解决锁粒度效率问题,支持多粒度,解决表锁和行锁共存的问题。

    如果另一个任务试图在该表级别上应用共享或排它锁,则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁,而只需检查表上的意向锁。


1. 意向共享锁(intention shared lock, IS),它预示着,事务有意向对表中的某些行加共享S锁

2. 意向排它锁(intention exclusive lock, IX),它预示着,事务有意向对表中的某些行加排它X锁

3. 事务要获得某些行的S锁,必须先获得表的IS锁

4. 事务要获得某些行的X锁,必须先获得表的IX锁

5. 意向锁之间互不排斥,但除了 IS 与 S兼容外,意向锁会与共享锁/排他锁互斥

6. IX,IS是表级锁,不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突

7. InnoDB支持多粒度锁,意向锁在保证并发性的前提下,实现了行锁和表锁共存


插入意向锁


概念

    插入意向锁,是间隙锁(Gap Locks)的一种(所以,也是实施在索引上的)(行级-自动)。不是意向锁(跟意向锁没啥关系)

   

解决的问题

    它是专门针对insert操作的,为了提高插入并发效率(强互斥锁)


  1. InnoDB使用共享锁提高读读并发

  2. 为了保证数据强一致,InnoDB使用强互斥锁,保证同一行记录修改和删除的串行性

  3. InnoDB使用插入意向锁,可以提高插入并发

    


+ 普通的Gap Lock 不允许 在 (上一条记录,本记录) 范围内插入数据

+ 插入意向锁Gap Lock 允许 在 (上一条记录,本记录) 范围内插入数据

+ 如果多个事务插入到相同的索引间隙中,如果它们不在间隙中的相同位置插入,则无需等待其他事务。比如说有索引记录4和7,有两个事务想要分别插入5,6,在获取插入行上的独占锁之前,每个锁都使用插入意图锁锁定4和7之间的间隙,但是不要互相阻塞,因为行是不冲突的,意向锁的涉及是为了插入的正确和高效。


##### 插入的过程


假设现在有记录 10, 30, 50 且为主键 ,需要插入记录 25 


1. 找到 小于等于25的记录 ,这里是 10

2. 找到 记录10的下一条记录 ,这里是 30

3. 判断 下一条记录30 上是否有锁

+ 判断 30 上面如果 没有锁 ,则可以插入

+ 判断 30 上面如果有Record Lock,则可以插入

+ 判断 30 上面如果有Gap Lock/Next-Key Lock,则无法插入,因为锁的范围是 (10, 30) /(10, 30] ;在30上增加insert intention lock( 此时处于waiting状态),当 Gap Lock / Next-Key Lock 释放时,等待的事物( transaction)将被 唤醒 ,此时 记录30 上才能获得 insert intention lock ,然后再插入 记录25


4. 注意:一个事物 insert 25 且没有提交,另一个事物 delete 25 时,记录25上会有 Record Lock


```

select * from a;

+----+

| a  |

+----+

| 5 |

| 10 |

| 13 |

| 20 |

+----+

```


```

//事务A

select * from a where a<=13 for update

```


```

//事务B

insert into a values (12)

```


```

//事务C

insert into a values (11)

```

事务A不提交,事务B,C被阻塞了,这时候查看


```

show engine innodb status\G

```


```

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 3, OS thread handle 140018685810432, query id 240 localhost root update

--等待插入的SQL

insert into a values(12)

------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:

--插入记录12的事物等待中,等待获得插入意向锁(lock_mode X locks gap before rec insert intention waiting)


```


此时事务A commit



+ 事务B输出:Query OK, 1 row affected (17.40 sec)

前提条件是insert操作的锁没有超时

+ 事务B未提交,再执行:show engine innodb status\G


```

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 3, OS thread handle 140018685810432, query id 247 localhost root

```


    

记录锁(行锁)


    记录锁,它封锁索引记录(行锁),例如: select * from t where id=1 for update;


+ 记录锁锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引。

+ 所以说当一条sql没有走任何索引时,那么将会在每一条聚合索引后面加X锁,这个类似于表锁,但原理上和表锁应该是完全不同的。


间隙锁(gap锁-行锁)

概念

    它封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。开区间,不包括双端端点(行锁)。


解决的问题

    间隙锁的主要目的,就是为了防止其他事务在间隔中插入数据,以导致“不可重复读”,解决幻读问题。

    如果把事务的隔离级别降级为读提交(Read Committed, RC),间隙锁则会自动失效。


select * from t where id between 7 and 15 for update;


这个SQL语句会封锁区间,以阻止其他事务id=10的记录插入。


注意:gap锁是一种动态锁,比如我们锁定了 7到15之间的区间,如果这时候删除了 7,则gap锁会自动往前继续锁住更多区间。


临键锁(next-key 行锁)


    临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间(行锁)。

    左闭右开区间


+ 但当查询唯一索引的时候,且记录存在,Next-Key Lock 会进行优化,将其降级为RecordLock,即仅锁住索引本身,不是范围。

+ 但当查询唯一索引的时候,且记录不存在,使用Gap Lock

+ 但当查询唯一索引的时候,使用范围查询 > <,使用Gap Lock + Record Lock,锁上界,不锁下界



    临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。


行锁包含 记录锁,间隙锁,临键锁,行锁的加锁规则

原则1:加锁的基本单位是next-key lock

原则2:查找过程中访问到的对象才会加锁

优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁

优化2:索引上的等值查询,给普通索引加锁的时候,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁

bug1:唯一索引上的范围查询会访问到不满足条件的第一个值为止,在mysql8.0.18以后解决了该问题。


metadata lock(MDL锁,自动-表级)

概念

    这是Server 层实现的锁,跟引擎层无关,表级-自动。


解决的问题

    用于解决DDL操作和DML操作之间的一致性,当你执行select的时候,如果这时候有ddl语句,那么ddl会被阻塞,因为select语句拥有metadata lock,防止元数据被改掉

总结


    InnoDB使用共享锁,可以提高读读并发;

    排他锁,为了保证数据强一致,InnoDB使用强互斥锁,保证同一行记录修改与删除的串行性;

    InnoDB使用插入意向锁,可以提高插入并发;

    记录锁锁定索引记录;

    间隙锁锁定间隔,防止间隔中被其他事务插入;

    临键锁锁定索引记录+间隔,防止幻读;

    MVCC解决了快照读的幻读,Next-Key解决了当前读的幻读。

    删除数据的时候尽量加limit,可以控制删除的条数,让操作更安全,还可以减少加锁的范围


普通select

加锁select

update与delete

insert

各类SQL语句分别加了什么锁?


select


普通的select是快照读,而select ... for update或select ... in share mode则会根据情况加不同的锁


如果在唯一索引上用唯一的查询条件时( where id=1),加记录锁


否则,其他的查询条件和索引条件,加间隙锁(BETWEEN AND )或Next-Key 锁(可重复隔离级别)


update与delete


如果在唯一索引上使用唯一的查询条件来update/delete,加记录锁


否则,符合查询条件的索引记录之前,都会加Next-Key 锁


注:如果update的是聚集索引,则对应的普通索引记录也会被隐式加锁,这是由InnoDB索引的实现机制决定的:普通索引存储PK的值,检索普通索引本质上要二次扫描聚集索引。


insert


insert和update与delete不同,它会用排它锁封锁被插入的索引记录,同时,会在插入区间加插入意向锁,但这个并不会真正封锁区间,也不会阻止相同区间的不同KEY插入。


### 查看锁等待情况


```

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;

select * from information_schema.innodb_trx;

```


显示锁 vs 隐示锁



1. 显示锁(explicit lock)

    显示的加锁,在show engine innoDB status 中能够看到  ,会在内存中产生对象,占用内存

    eg: select ... for update , select ... lock in share mode


2. 隐示锁(implicit lock)

    implicit lock 是在索引中对记录逻辑的加锁,但是实际上不产生锁对象,不占用内存空间


3. 哪些语句会产生implicit lock 呢?

   eg: insert into xx values(xx)

   eg: update xx set t=t+1 where id = 1 ; 会对辅助索引加implicit lock


4. implicit lock 在什么情况下会转换成 explicit lock

  eg: 只有implicit lock 产生冲突的时候,会自动转换成explicit lock,这样做的好处就是降低锁的开销

  eg: 比如:我插入了一条记录10,本身这个记录加上implicit lock,如果这时候有人再去更新这条10的记录,那么就会自动转换成explicit lock


5. 数据库怎么知道implicit lock的存在呢?如何实现锁的转化呢?

  1. 对于聚集索引上面的记录,有db_trx_id,如果该事务id在活跃事务列表中,那么说明还没有提交,那么implicit则存在

  2. 对于非聚集索引:由于上面没有事务id,那么可以通过上面的主键id,再通过主键id上面的事务id来判断,不过算法要非常复杂,这里不做介绍




版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

Mysql B+树索引常见面试题

Mysql B+树索引常见面试题

概念一个经典的B+树索引数据结构见下图:B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当...

mysql中可重复读隔离级别的实现原理

原理MySQL默认的隔离级别是可重复读,即:事务A在读到一条数据之后,此时事务B对该数据进行了修改并提交,那么事务A再读该数据,读到的还是原来的内容。 那么MySQL可重复读是如何实现的呢?使用的的一...

mysql中Query语句对系统性能的影响

背景我们重点分析实现同样功能的不同 SQL语句在性能方面会产生较大的差异的根本原因,并通过一个较为典型的示例来对我们的分析做出相应的验证。为什么返回完全相同结果集的不同SQL语句,在执行性能方面存在差...

mysql中Join的实现原理及优化思路

mysql中Join的实现原理及优化思路

实现原理在MySQL中,只有一种 Join 算法,就是大名鼎鼎的 Nested Loop Join,他没有其他很多数据库所提供的 Hash Join,也没有 Sort Merge Join。顾名思义,...

mysql中优化必读

通用规则多读少写加缓存,少读多写加队列带事务的不要整体commit,分段commit一、要保证数据库的效率,要做好以下四个方面的工作:① 数据库设计② sql语句优化③ 数据库参数配置④ 恰当的硬件资...

innodb中统计数据是如何收集的

InnoDB 统计数据如何查看    1. 通过SHOW TABLE STATUS可以看到关于表的统计数据    2....

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。