Mysql数据库 19.Mysql 锁

MySQL锁

锁：锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂

锁解决的问题

解决并发事物的访问问题，解决事务发生时造成的脏读、不可重复读、幻读等问题

锁的分类

按照锁的粒度分，分为以下三类锁：

1.全局锁：锁定数据库中的所有表

2.表级锁：每次操作锁住操作表

3.行级锁：每次操作锁住操作的行数据

锁的范围、力度依次减小

全局锁（只读）

对整个数据库实例加锁，使整个库加锁后处于只读状态（不能增删改），使用该命令后，数据更新语句DML、数据定义语句DDL和更新类事物的提交语句等操作都会被阻塞

典型的应用是作全库逻辑备份，把所有表全部锁定，从而获取一致性视图，保证数据的完整性?

具体使用

1.添加全局锁

语法：flush tables with read lock；

flush tables with read lock；

添加全局锁后，不能使用任何增、删、改操作，可以使用查询操作，更新操作被阻塞

2.执行数据备份，利用MySQL提供的工具mysqldump

语法：mysqldump -uroot -proot 数据库名 > 具体存入脚本名称.sql

mysqldump -uroot -proot 数据库名 > 具体存入脚本名称.sql

3.备份之后，解锁指令（释放全局锁）

语法：unlock tables；

unlock tables；

演示：

添加全局锁

flush table with read lock;?

数据备份

释放全局锁

unlock table;

全局锁缺点（力度太大）

1.在备份期间不能执行更新操作，业务基本上就会停摆

2.如果有主从复制+读写分离的结构，在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

?

表级锁?

表级锁，它的粒度为锁定整张表，每次操作锁住一张表，并且发生锁冲突的概率最高，并发度最低在InnoDB，MyISAM等常见储存引擎中支持

表级锁可分为三类：

1、表锁? ? ? ? 2、元数据锁? ? ? ? 3、意向锁

1.表锁?

表锁分为两类

1.表共享读锁

2.表独占写锁

读锁（read）：只能读，不能写

写锁（write）：既能读，也能写

具体用法：

加锁

????????lock table 表名(可以是多张表) read/write

解锁

????????unlock tables 或 直接断开客户端连接（关闭客户端）

2.元数据锁?

元数据：简单理解为表结构

元数据实际上是系统自动控制的，不需要显示使用，当我们访问一张表的时候会自动加锁，主要作用就是维护表结构的数据一致性，当表中有活动事务存在时，是不允许操作元数据写入的

也就是如果有一张表有存在未提交的事务，不允许修改表结构，其目的就是为了避免DML与DDL冲突

元数据锁是系统自带的锁，只需了解

3.表锁：意向锁

意向锁是为了避免DML执行时，行锁与表锁的冲突，可以让表锁无需检查每行数据是否加锁，直接使用意向锁减少表锁的检查

意向锁判断依据是是否兼容，如果是可兼容的锁就可以直接加上，如果是排他锁则被阻塞，知道行锁释放

意向锁的作用：通过意向锁省略时间，不需要一行一行进行判断，可以直接判断意向锁是排他锁还是兼容锁?

意向锁的使用

意向锁分为两种类型：

? ? ? ? 1.意向共享锁：select ...... lock in share mode 添加，与表共享锁（read）兼容，与表锁排他锁（write）互斥

? ? ? ? 2.意向排他锁：insert、delete、select... for update添加，与表锁共享锁（read）及排他锁（write）都互斥，意向锁之间不会互斥

事务一旦提交，意向共享锁，意向排他锁，都会自动释放

查看意向锁及行锁加锁情况：

?行级锁

行锁也称为记录锁，锁住某一行，MySQL服务器层没有实行行锁机制，行级锁只在储存引擎实现

优点：锁的力度小，发生锁冲突概率低，可以实现高并发

缺点：对于锁的开销比较大，加锁会比较慢

1.行级锁的分类

1.行锁（记录锁）：记录锁就是仅仅把一条记录锁上，仅仅锁住一条记录，对其他数据没有影响，防止其他事务对这条数据进行修改或删除操作

2.间歇锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert插入操作，产生幻读，实际上这也是MySQL隔离级别 可重复读 隔离级别下，解决幻读的一种方案

3.临键锁：既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新纪录，就是行锁和间隙锁组合，innodb默认采用临键锁

2. 行锁/记录锁

行锁的类型

在InnoDB引擎中实现了两种类型的行锁

? ? ? ? 1.共享锁（s）：允许一个事务读取一行，阻止其他事务获得相同数据的排它锁，简单理解就是共享锁和共享锁兼容和排它锁排斥

? ? ? ? 2.排它锁（x）:允许获取排它锁的事务更新数据，阻止其他事务获取相同的排它锁和共享锁

?

?锁模式兼容性

行级锁定实现方式——必定通过索引实现

?常见SQL语句执行时的加锁情况

行锁只能锁定当前行所在的一条数据，其他数据不被锁住?

行锁只有索引存在时才会生效，否则会生成表锁

间歇锁/临键锁

间隙锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读

临键锁：既想锁定某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，就是行锁和间隙锁组合，InnoDB默认的是临键锁

唯一索引等值查询?

当查询的记录是存在的，next-key lock 会退化成 当前记录的「记录锁」。 当查询的记录是不存在的，next-key lock 会退化成 当前记录所在区间的「间隙锁」。?

commit提交以后锁释放

非唯一索引等值查询（尽量避免）?

当查询的记录存在时，除了会加 next-key lock 外，还额外对下一区间加「间隙锁」。 首先先来给age字段添加普通索引

唯一索引范围查询?

对于给定范围中涉及到的值都加next-key lock，会访问到不满足条件的第一个值为止。 直接查询一个范围，并且添加共享锁，来查看?

加锁规则：?

1. 唯一索引等值查询： 当查询的记录是存在的，next-key lock 会退化成 当前记录的「记录锁」。 当查询的记录是不存在的，next-key lock 会退化成 当前记录所在区间的「间隙锁」。 2. 非唯一索引等值查询（不建议使用）： 当查询的记录存在时，除了会加 next-key lock 外，还额外对上下区间加「间隙锁」。 3. 唯一索引范围查询： 对于给定范围中涉及到的值都加next-key lock，会访问到不满足条件的第一个值为 止。?

锁总结

什么是MySQL锁：

在并发访问时，解决数据一致性、有效性问题的解决方案，MySQL中的 锁是在服务器层或者存储引擎层实现的，我们所讲的所有锁的内容都是应用在InnoDB引擎 中的。

锁分为三种：全局锁、表级锁、行级锁

全局锁：对整个数据库实例加锁，粒度最大，加锁后整个数据库实例处于只读状态，性能较 差，一般用于数据库逻辑备份使用

表级锁：锁住整张操作的表，粒度也比较大，发生锁冲突概率比较高，具体分为：表锁（具 体分为读和写两种锁）、元数据锁（主要作用就是针对DML语句和DDL语句的冲突）、意向 锁（主要目的规避行锁与表锁的冲突问题，避免表锁再加锁时逐行扫描行锁，自动无需手动 操作）

行级锁：锁住对应操作表的具体行数据（针对索引），粒度最小，发生锁冲突的概率最小， 具体分为：行锁、间隙所、临键锁，要注意的是其中共享锁与共享锁是可以兼容的，但是与 排它锁，包括排它锁与排它锁之间都是互斥的?