电子商务网站建设配置,网站 项目方案,桐乡做网站的公司,5万一套小院出售文章目录 一、锁的概述二、全局锁三、表级锁四、元数据锁#xff08;meta data lock, MDL#xff09;五、意向锁六、行级锁七、行锁#xff08;Record Lock#xff09;八、间隙锁#xff08;Gap Lock#xff09;九、临键锁#xff08;Next-Key Lock#xff09;十、锁总… 文章目录 一、锁的概述二、全局锁三、表级锁四、元数据锁meta data lock, MDL五、意向锁六、行级锁七、行锁Record Lock八、间隙锁Gap Lock九、临键锁Next-Key Lock十、锁总结 本篇博客深入详细地讨论了数据库锁的种类和使用场景。首先介绍了全局锁的含义和使用场景如全库逻辑备份但这将阻塞后续的写和更新操作。然后博客讨论了表级锁和元数据锁MDL它们在操作表和表结构时自动添加以保持数据一致性。同时博文也讲解了“意向锁”和行级锁的概念解释了它们如何减少冲突和提高并发性能。最后作者阐述了更加细粒度的“间隙锁”和“临键锁”如何通过防止’幻读’来维护数据的一致性。本文涵盖了从更宏观的全局锁到更微观的行级锁甚至于间隙锁和临键锁的各种锁机制为读者对数据库锁有了更全面深入的了解。 一、锁的概述 二、全局锁 对整个数据库实例加锁加锁后整个实例处于只读状态后续DML的写语句DDL语句更新操作的事务提交都将被阻塞
使用场景全库逻辑备份将数据库中的数据备份成一个SQL文件保存在磁盘中
若执行备份过程中还有数据的修改和插入则备份的数据无法保证数据的一致性和完整性。
例如表订单表库存表订单日志逐个备份该表的过程备份顺序订单-库存-日志中依然有下单的操作即备份了订单 但库存已经减少等操作 会导致数据库的数据不一致
flush tables with read lock ; # 加全局锁
sudo mysqldump -h 127.0.0.1 -uroot -p123456 -d db01 db01.sql # 数据备份 shell界面中执行
unlock tables; # 再登录到MySQL释放锁
加锁后DML和DDL无法操作DQL查询数据 依然可以使用
数据库中加全局锁是一个比较重的操作存在以下问题
如果在主库上备份那么在备份期间都不能执行更新 如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志binlog会导致主从延迟
在InnoDB引擎中可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份 sudo mysqldump -single-transaction -h 127.0.0.1 -uroot -p123456 -d db01 db01.sql
三、表级锁 表锁
表共享读锁(read lock) 表独占写锁(write lock)
lock tables 表名... read/write # 加锁
unlock tables / 客户端断开连接 # 释放锁
四、元数据锁meta data lock, MDL
MDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上
主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与 DDL冲突增删改 DDL数据库 表 表字段 DML表中数据保证读写的正确性。
元数据就是表结构元数据锁就是维护表结构一致性的锁
当对一张表进行增删改查的时候 DML语句加MDL读锁(共享) 当对表结构进行变 更操作的时候 DDL语句加MDL写锁(排他)
查看元数据锁的加锁情况select object_type, object_schema, object_name,lock_type, lock_duration from performance_schema.metadata_locks ;五、意向锁
若两个线程操作一张表线程A对表加了行锁/表锁之后 线程B要对表加表锁线程B需要先遍历表 查看表是否已经被加行锁 或者 表锁。
为了减少类似以上DML执行时加的行锁与后来要加的表锁冲突的现象。InnoDB引入了意向锁使表锁不用再检查每行是否被加锁使用意向锁减少了表锁的检查。 意向共享锁(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;六、行级锁 七、行锁Record Lock
行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。在 RC、RR隔离级别下都支持
共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁
排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁
排他锁所有客户端都不能进行对表的任何操作
注意针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁
InnoDB的行锁是针对于索引加的锁若不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时就会升级为表锁八、间隙锁Gap Lock
锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事 务在这个间隙进行insert产生幻读。在RR隔离级别下都支持。
举例
# -----------------------------------------客户端1-----------------------------------------
begin;
update usr set age 40 where id 5;
# 在客户端1 update了一个不存在的id5后加上了间隙锁 (3,8)
commit;
# -----------------------------------------客户端2-----------------------------------------
begin;
INSERT INTO usr VALUES (7,安拉, 17799998819, jd1h126.com, 城市规划, 51,2, 0, 2001-09-15 00:00:00); # 阻塞被间隙锁直到客户端1 commit
commit;
九、临键锁Next-Key Lock
行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。 在RR隔离级别下支持。
默认情况下InnoDB在 REPEATABLE READ事务隔离级别运行InnoDB使用 next-key 锁进行搜 索和索引扫描以防止幻读。
举例
非唯一索引为了其它事务向间隙中插入一条记录 出现幻读现象因此会把37的间隙锁上把3之前的这块间隙也锁住3这行数据也锁住
注意索引上的等值查询(唯一索引)给不存在的记录加锁时, 优化为间隙锁 。索引上的等值查询(非唯一普通索引)向右遍历时最后一个值不满足查询需求时next-key lock 退化为间隙锁。索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止举例锁19这行的数据邻键锁锁上1925以及25这行记录还有邻键锁锁上正无穷大 以及 (25,∞) 的间隙 间隙锁锁间隙不包含对应的数据记录只锁定该数据记录之前的这部分间隙
邻键锁即包含当前的数据记录也会锁定该数据记录之前的这部分间隙
间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会 阻止另一个事务在同一间隙上采用间隙锁
十、锁总结
全局锁锁定数据库中的所有表数据库备份
表级锁每次操作锁住整张表 读锁/写锁 lock tables 表名… read/write; unlock tables; 元数据锁 自动加 DML语句 数据的增删改查时 MDL读锁DDL语句表结构变更 MDL写锁 MDL读锁 shared_readselect, select … lock in share mode, shared_writeinsert ,update, select … for update MDL写锁alter table 意向共享锁/意向排它锁 自动加 加意向共享锁shared_readselect, select … lock in share mode, 加意向排它锁shared_writeinsert ,update, select … for update
行级锁每次操作锁住对应的行数据。只针对索引如果不是索引 升级成表锁 行锁共享锁/排它锁 排它insert,update,delete, select … for update 共享select … lock in shared mode select 不加任何锁 间隙锁和邻键锁间隙锁锁住间隙邻键锁锁数据和间隙。
