mysql进阶--锁

锁的概述:锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源CPU、RAM、I/O的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂。锁的作用保证数据的「一致性」和「正确性」防止脏读A 事务修改数据还没提交B 事务读到了未提交的脏数据防止不可重复读A 事务两次读同一行数据中间被 B 事务修改两次结果不一样防止幻读A 事务按条件查询中间 B 事务插入了符合条件的新数据A 两次查询结果行数不一样防止数据覆盖两个事务同时修改同一行后改的覆盖先改的导致数据丢失锁的分类:全局锁锁定数据库中的所有表。表级锁每次操作锁住整张表。行级锁每次操作锁住对应的行数据。全局锁全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的 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/ 客户端断开连接。读锁不会阻塞其他客户端的读但是会阻塞写。写锁既会阻塞其他客户端的读又会阻塞其他客户端的写。元数据锁MDL 加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL 锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免 DML 与 DDL 冲突保证读写的正确性。在 MySQL5.5 中引入了 MDL当对一张表进行增删改查的时候加 MDL 读锁 (共享)当对表结构进行变更操作的时候加 MDL 写锁 (排他)。查看元数据锁select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;意向锁为了避免 DML 在执行时加的行锁与表锁的冲突在 InnoDB 中引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查。简单来说就是让表锁快速知道表里有没有行锁避免冲突不用全表扫描。加行锁时自动加意向锁。行锁跟意向锁是绑定的。意向共享锁IS由语句select ... lock in share mode添加。意向排他锁IX由insert、update、delete、select ... for update添加。意向共享锁IS与表共享写锁read兼容与表独占写锁write互斥。意向排他锁IX与表共享写锁read及表独占写锁write都互斥。意向锁之间不会互斥。查看意向锁及行锁的加锁情况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 隔离级别下支持。行锁InnoDB 实现了以下两种类型的行锁共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排他锁。排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。请求锁类型 \ 当前锁类型S共享锁X排他锁S共享锁兼容冲突X排他锁冲突冲突SQL行锁类型说明INSERT ...排他锁自动加锁UPDATE ...排他锁自动加锁DELETE ...排他锁自动加锁SELECT正常不加任何锁-SELECT ... LOCK IN SHARE MODE共享锁需要手动在 SELECT 之后加 LOCK IN SHARE MODESELECT ... FOR UPDATE排他锁需要手动在 SELECT 之后加 FOR UPDATE行锁 - 演示默认情况下InnoDB 在REPEATABLE READ事务隔离级别运行InnoDB 使用 next-key 锁进行搜索和索引扫描以防止幻读。针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁。InnoDB 的行锁是针对于索引加的锁不通过索引条件检索数据那么 InnoDB 将对表中的所有记录加锁此时就会升级为表锁。通过以下 SQL查看意向锁及行锁的加锁情况select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;间隙锁/临界锁间隙锁 / 临键锁 - 演示默认情况下InnoDB 在REPEATABLE READ事务隔离级别运行InnoDB 使用 next-key 锁进行搜索和索引扫描以防止幻读。索引上的等值查询 (唯一索引)给不存在的记录加锁时优化为间隙锁。索引上的等值查询 (普通索引)向右遍历时最后一个值不满足查询需求时next-key lock 退化为间隙锁。索引上的范围查询 (唯一索引)-- 会访问到不满足条件的第一个值为止。注意间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。总结1. 全局锁对整个数据库实例加锁加锁后整个实例就处于只读状态性能较差数据逻辑备份时使用2. 表级锁操作锁住整张表锁定粒度大发生锁冲突的概率高表锁、元数据锁、意向锁3. 行级锁操作锁住对应的行数据锁定粒度最小发生锁冲突的概率最低行锁、间隙锁、临键锁