MySQL-进阶篇-锁

温馨提示建议在PC端浏览~锁概述介绍锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源CPU、RAM、I/O的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂。分类MySQL中的锁按照锁的粒度分分为以下三类全局锁锁定数据库中的所有表。表级锁每次操作锁住整张表。行级锁每次操作锁住对应的行数据。全局锁介绍全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性。演示说明绿色代表可以执行的操作红色代表被阻塞的操作。特点数据库中加全局锁是一个比较重的操作存在以下问题如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆。如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志binlog会导致主从延迟。运维篇会详细讲解这里只做了解即可在InnoDB引擎中我们可以在备份时加上参数–single-transaction参数来完成不加锁的一致性数据备份。mysqldump --single-transaction -uroot -p123456 itcast itcast.sql表级锁介绍表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。对于表级锁主要分为以下三类表锁元数据锁meta data lockMDL意向锁表锁对于表锁分为两类表共享读锁read lock表独占写锁write lock小结读锁不会阻塞其他客户端的读但是会阻塞写。写锁既会阻塞其他客户端的读又会阻塞其他客户端的写。语法加锁lock tables 表名… read/write。释放锁unlock tables / 客户端断开连接。元数据锁meta data lockMDLMDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与DDL冲突保证读写的正确性。元数据可以简单理解为表结构元数据锁可以确保当前事务修改表结构时不存在其他事务对同一张表进行并发操作在MySQL5.5中引入了MDL当对一张表进行增删改查的时候加MDL读锁共享当对表结构进行变更操作的时候加MDL写锁排他。查看元数据锁selectobject_type,object_schema,object_name,lock_type,lock_durationfromperformance_schema.metadata_locks;意向锁为了避免DML在执行时加的行锁与表锁的冲突在InnoDB中引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查。避免表锁在加锁时逐行的去检查这张表的行锁情况分类意向共享锁IS由语句select … lock in share mode添加。意向排他锁IX由insert、update、delete、select … for update添加。意向锁与表锁的兼容情况意向共享锁IS与表锁共享锁read兼容与表锁排它锁write互斥。意向排他锁IX与表锁共享锁read及排它锁write都互斥。意向锁之间不会互斥。可以通过以下SQL查看意向锁及行锁的加锁情况selectobject_schema,object_name,index_name,lock_type,lock_mode,lock_datafromperformance_schema.data_locks;行级锁行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持。临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持。行锁InnoDB实现了以下两种类型的行锁共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。兼容情况不同SQL语句所加行锁的类型情况默认情况下InnoDB在REPEATABLE READ事务隔离级别运行InnoDB使用next-key锁进行搜索和索引扫描以防止幻读。针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁。InnoDB的行锁是针对于索引加的锁不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时就会升级为表锁。可以通过以下SQL查看意向锁及行锁的加锁情况selectobject_schema,object_name,index_name,lock_type,lock_mode,lock_datafromperformance_schema.data_locks;间隙锁 / 临键锁默认情况下InnoDB在REPEATABLE READ事务隔离级别运行InnoDB使用next-key锁进行搜索和索引扫描以防止幻读。索引上的等值查询唯一索引给不存在的记录加锁时优化为间隙锁。索引上的等值查询普通索引向右遍历时最后一个值不满足查询需求时next-key lock退化为间隙锁。索引上的范围查询唯一索引会访问到不满足条件的第一个值为止。注意间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。