目录一、什么是锁1.锁的概述2.锁的分类二、全局锁三、表级锁1.表锁2.元数据锁3.意向锁四、行级锁1.行锁2.间隙锁一、什么是锁1.锁的概述锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源(CPU、RAM、I/O)的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。2.锁的分类按照锁的粒度来分有以下三类全局锁:锁定数据库中的所有表。表级锁:每次操作锁住整张表。行级锁:每次操作锁住对应的行数据。二、全局锁全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性。不加全局锁加上全局锁语法-- 对数据库加上 **全局锁** flush tables with read lock; -- 对数据库中的所有数据进行备份到指定文件 mysqldump -u root -p 1234 itcast itcast.sql; -- 对数据库进行 **解锁** unlock tables;在InnoDB引擎中我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份。mysqldump --single-transaction -uroot -p1234 itcast itcast.sql三、表级锁表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。应用在My/5AM、InnoDB、BDB等存储引擎中。表级锁分为以下三类表锁元数据锁meta data lockMDL意向锁1.表锁表锁也分为两类表共享读锁read lock表独占写锁write lock语法# 加锁 lock tables 表名…… read/write; # 释放锁 unlock tables; / 客户端断开连接读锁共享读锁顾名思义加上读锁之后允许多个客户端去共享读该表的数据但是不允许写。写锁当前客户端对数据表加上写锁后允许当前客户端进行查询、写入操作不允许其他客户端进行任何操作。2.元数据锁MDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与DDL冲突保证读写的正确性。在MySQL5.5中引入了MDL当对一张表进行增删改查的时候加MDL读锁(共享);当对表结构进行变更操作的时候加MDL写锁(排他)。3.意向锁当线程B想给表加上表锁时要判断本身所要加锁的类型与线程A加上的意向锁的类型是否兼容。意向锁的类型意向共享锁(IS):由语句select...lock in share mode添加。与表锁共享锁read兼容与表锁排它锁write互斥。意向排他锁(IX):由insert、update、delete、select ...for update 添加。与表锁共享锁read和表锁排他锁write都互斥。意向锁之间不会互斥。可以通过以下SQL查看意向锁及行锁的加锁情况:select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;一旦事务提交客户端对表添加的意向锁以及行锁都会释放。四、行级锁行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类:行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。间隙锁Gap Lock锁定索引记录间隙(不含该记录)确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持。临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持。1.行锁InnoDB实现了以下两种类型的行锁:1.共享锁(S):允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。2. 排他锁(X):允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。进行增删改查时行锁添加的类型注意默认情况下InnoDB在REPEATABLE READ事务隔离级别运行InnoDB使用next-key 锁进行搜索和索引扫描以防止幻读。1.针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁。2.InnoDB的行锁是针对于索引加的锁不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时就会升级为表锁。2.间隙锁默认情况下InnoDB在REPEATABLE READ事务隔离级别运行InnoDB使用next-key锁进行搜索和索引扫描以防止幻读。1.索引上的等值查询(唯一索引)给不存在的记录加锁时优化为间隙锁。2.索引上的等值查询(普通索引)向右遍历时最后一个值不满足查询需求时next-key lock退化为间隙锁。3. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。补充以及正无穷注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁 【MySQL | 第十篇】锁
目录一、什么是锁1.锁的概述2.锁的分类二、全局锁三、表级锁1.表锁2.元数据锁3.意向锁四、行级锁1.行锁2.间隙锁一、什么是锁1.锁的概述锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源(CPU、RAM、I/O)的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。2.锁的分类按照锁的粒度来分有以下三类全局锁:锁定数据库中的所有表。表级锁:每次操作锁住整张表。行级锁:每次操作锁住对应的行数据。二、全局锁全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性。不加全局锁加上全局锁语法-- 对数据库加上 **全局锁** flush tables with read lock; -- 对数据库中的所有数据进行备份到指定文件 mysqldump -u root -p 1234 itcast itcast.sql; -- 对数据库进行 **解锁** unlock tables;在InnoDB引擎中我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份。mysqldump --single-transaction -uroot -p1234 itcast itcast.sql三、表级锁表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。应用在My/5AM、InnoDB、BDB等存储引擎中。表级锁分为以下三类表锁元数据锁meta data lockMDL意向锁1.表锁表锁也分为两类表共享读锁read lock表独占写锁write lock语法# 加锁 lock tables 表名…… read/write; # 释放锁 unlock tables; / 客户端断开连接读锁共享读锁顾名思义加上读锁之后允许多个客户端去共享读该表的数据但是不允许写。写锁当前客户端对数据表加上写锁后允许当前客户端进行查询、写入操作不允许其他客户端进行任何操作。2.元数据锁MDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与DDL冲突保证读写的正确性。在MySQL5.5中引入了MDL当对一张表进行增删改查的时候加MDL读锁(共享);当对表结构进行变更操作的时候加MDL写锁(排他)。3.意向锁当线程B想给表加上表锁时要判断本身所要加锁的类型与线程A加上的意向锁的类型是否兼容。意向锁的类型意向共享锁(IS):由语句select...lock in share mode添加。与表锁共享锁read兼容与表锁排它锁write互斥。意向排他锁(IX):由insert、update、delete、select ...for update 添加。与表锁共享锁read和表锁排他锁write都互斥。意向锁之间不会互斥。可以通过以下SQL查看意向锁及行锁的加锁情况:select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;一旦事务提交客户端对表添加的意向锁以及行锁都会释放。四、行级锁行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类:行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。间隙锁Gap Lock锁定索引记录间隙(不含该记录)确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持。临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持。1.行锁InnoDB实现了以下两种类型的行锁:1.共享锁(S):允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。2. 排他锁(X):允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。进行增删改查时行锁添加的类型注意默认情况下InnoDB在REPEATABLE READ事务隔离级别运行InnoDB使用next-key 锁进行搜索和索引扫描以防止幻读。1.针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁。2.InnoDB的行锁是针对于索引加的锁不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时就会升级为表锁。2.间隙锁默认情况下InnoDB在REPEATABLE READ事务隔离级别运行InnoDB使用next-key锁进行搜索和索引扫描以防止幻读。1.索引上的等值查询(唯一索引)给不存在的记录加锁时优化为间隙锁。2.索引上的等值查询(普通索引)向右遍历时最后一个值不满足查询需求时next-key lock退化为间隙锁。3. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。补充以及正无穷注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁