锁锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源(CPU、RAM、I/O)的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂MySQL中的锁按照锁的粒度分分为以下三类全局锁锁定数据库中的所有表表级锁每次操作锁住整张表行级锁每次操作锁住对应的行数据1.全局锁全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性。1.1 语句使用全局锁flush tables with read lock;拷贝数据库mysqldump [--single-transaction] -uroot -p[123456] itcast itcast.sql不属于MySQL命令需要退出数据库管理系统后在命令行执行只适用于支持 可重复读隔离级别的事务 的存储引擎-u后面跟用户名如root用户但要保证用户有足够权限拷贝数据库-p后面可以跟用户密码也可以不跟不跟命令执行后需要手动输入密码建议不加密码itcast代表要拷贝的数据库只写数据库名就可以了itcast.sql可以写绝对路径但要保证路径存在格式是D:/backup/itcast.sql或D:\\backup\\itcast.sql释放全局锁unlock tables;1.2 特点数据库中加全局锁是一个比较重的操作存在以下问题:如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)会导致主从延迟该结构会在后续主从复制讲解解决方法在InnoDB引擎中我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份通过加上这个参数确保了在备份开始时创建一个一致性的快照通过启动一个新的事务来实现这一点该事务的隔离级别是Repeatable Read级别从而确保在备份数据库时可以对数据库数据进行操作。2.表级锁每次操作锁住整张表。锁定粒度大发生锁的冲突的概率最高并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中对于表级锁主要分为以下三类表锁元数据锁meta data lockMDL意向锁2.1 表锁对于表锁分为两类表共享读锁read lock当前客户端和其他客户端都能读当前客户端不能写其他客户端写被阻塞表独占写锁write lock当前客户端可以读和写其他客户端的读和写会被阻塞加锁lock tables 表名... read|write;释放锁unlock tables;用户端断开与数据库的连接会自动释放锁如事务结束事务内加的锁全部释放2.2 元数据锁MDLMDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。元数据描述数据库对象结构的信息而不是实际的数据内容是描述数据库对象的结构和属性的信息如表结构、视图定义等元数据锁是为了避免DML与DDL冲突保证读写的正确性。在MySQL5.5中引入了MDL当对一张表进行增删改查时加MDL读锁共享当对表结构进行变更时加MDL写锁排他对应SQL锁类型说明lock tables xxx read | writeSHARED_READ_ONLY|SHARED_NO_READ_WRITEselect 、 select … lock in share modeSHARED_READ共享读锁与SHARED_READ、SHARED_WRITE兼容与EXCLUSIVE互斥insert 、update、delete、select …for updateSHARED_WRITE共享写锁与SHARED_READ、SHARED_WRITE兼容与EXCLUSIVE互斥alter table …EXCLYSIVE写锁与其他的MDL都互斥SHARED_READ和SHARED_WRITE是兼容的即可以同时存在但是这两个锁和EXCLYSIVE是互斥的即EXCLYSIVE和他们不能同时存在会发生阻塞查看元数据锁select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;2.3 意向锁当线程A对基本表某一行加行锁后如果线程B要对基本表加表锁那么MySQL会检查基本表的每一行判断是否有其他线程加的锁如果有会判断表锁和行锁是否冲突如果不冲突会直接加锁如果冲突会被阻塞直到其他线程全部释放锁为了避免DML在执行时加的行锁与表锁的冲突在InnoDB中引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查意向锁分为两类意向共享锁IS与表锁共享锁read兼容与表锁排他锁write互斥意向排他锁IX与表锁共享锁read和排他锁write都互斥意向锁之间不会互斥意向锁的好处如果没有「意向锁」那么加「独占表锁」时就需要遍历表里所有记录查看是否有记录存在独占锁这样效率会很慢那么有了「意向锁」由于在对记录加独占锁前先会加上表级别的意向独占锁那么在加「独占表锁」时直接查该表是否有意向独占锁如果有就意味着表里已经有记录被加了独占锁这样就不用去遍历表里的记录意向锁的目的是为了快速判断表里是否有记录被加锁查看锁及元数据锁的加锁情况select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;添加意向共享锁select ... lock in share mode;执行语句后MySQL会先在表上加上意向共享锁然后对读取的记录加共享锁也就是说会加两个锁insert、 update、 delete、 select ... for update在执行增删改语句后会自动加上意向独占锁不需要手动指定执行语句后MySQL会先在表上加上意向独占锁然后对读取的记录加独占锁也就是说会加两个锁2.4 AUTO-INC锁3.行级锁行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中InnoDB数据是基于索引组成的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁行级锁主要分为三类行锁(Record Lock)锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持间隙锁(GapLock)锁定索引记录间隙(不含该记录)确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持临键锁(Next-Key Lock)行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持3.1 Record Lock行锁Record Lock 称为记录锁锁住的是一行记录。而且记录锁是有 S 锁和 X 锁之分共享锁(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将对表中的所有记录加锁此时就会升级为表锁查看意向锁及行锁的加锁情况select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;3.2 Gap Lock间隙锁和Next-Key Lock临键锁默认情况下InnoDB在 REPEATABLE READ可重复读事务隔离级别运行InnoDB使用 next-key 锁进行搜索和索引扫描以防止幻读索引上的等值查询(唯一索引)给不存在的记录加锁时,优化为间隙锁会在应存在的位置间隙插入一个间隙锁其他事务访问这个间隙时会被阻塞索引上的等值查询(非唯一性索引)向右遍历时最后一个值不满足查询需求时next-key lock退化为间隙锁如果值存在会把查询的节点加行锁并且在查询的节点前后两个间隙都添加间隙锁如果值不存在会在应存在的位置间隙插入一个间隙锁索引上的范围查询(唯一索引)会访问到不满足条件的第一个值为止对查询范围内的所有行加行锁对查询范围内的所有间隙加间隙锁索引上的范围查询(非唯一索引)向左遍历时第一个范围不满足查询范围向右遍历时最后一个范围不满足查询范围对查询范围内的所有行加行锁对查询范围内的所有间隙以及起始点的前一个间隙和终止点的后一个间隙加间隙锁