MySQL教程之MySQL锁机制
发布时间：2022-08-10 10:06:10 所属栏目：MySql教程 来源：互联网
导读：第一部分：概述 Myisam的锁比较容易理解，无论是读还是写都只会加表锁，表锁又分为read锁和write锁，可以使用如下方式手动加锁： 1 2 3 4 5 6 7 8 9 10 11 12 13 --加表锁语句（同样适用于InnoDB）： lock tables tbl_name [[AS] alias] lock_type [, tbl_na
|
第一部分：概述 Myisam的锁比较容易理解，无论是读还是写都只会加表锁，表锁又分为read锁和write锁，可以使用如下方式手动加锁： 1 2 3 4 5 6 7 8 9 10 11 12 13 --加表锁语句（同样适用于InnoDB）： lock tables tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE 或者 flush tables with read lock; #其实加的不是表锁而是针对所有表的一个全局读锁。 --解表锁语句： unlock tables; --如何观察表的元数据锁: show open tables [FROM db_name] [like_or_where] Myisam的read、write表锁其实可以看做一种元数据锁，这种锁对其他存储引擎例如innodb表也可以加，其不同于innodb引擎层的S和X锁。 由于Myisam这样的锁机制，导致Myisam是一款读性能较好，并发写性能较差的存储引擎，本文主要讨论如今的MySQL默认存储引擎InnoDB的锁机制。 第二部分：InnoDB锁分类 1 2 3 --如何观察InnoDB锁： set @@global.innodb_status_output_locks=on; --这样show engine innodb statusG可以显示InnoDB额外的锁信息（锁太多时也无法完全显示），标准情况下只显示锁数目。 InnoDB没有页锁，只有表锁和行锁。 一、InnoDB表锁有以下几种： InnoDB也可以使用lock tables ... read/write来添加元数据表锁。 InnoDB支持的事务表锁有： S ：其实是行锁，MySQL的行锁不会有额外的锁开销，因此我更愿意把这种全表的S行锁称作表S锁。 X ：其实是行锁，MySQL的行锁不会有额外的锁开销，因此我更愿意把这种全表的X行锁称作表X锁。 IS：表级意向共享锁，即表示事务有向底层资源加共享行锁的意向。如select ... lock in share mode语句，在加行锁之前会在表上现加IS锁，这样可以提高锁冲突检测的效率，同时也可以避免事务在表级添加会使其他事务行锁失效的表级锁。 IX：表级意向独占锁，即表示事务有向底层资源加独占行锁的意向。一般来说delete、update语句和select ... for update语句都会在加行锁之前先加表级IX锁，除非未用到索引（此时直接加表级X锁）。 表锁的兼容性图： 此外表级锁还有一种比较特殊的锁：AUTO-INC Locks 这种锁只在向自增主键中插入记录时出现，由于自增主键在MySQL中较为常见，因此也算是经常会遇到的锁，这种锁是为自增主键设计的，无需和以上4钟锁检测冲突。 AUTO-INC Locks的锁机制： 在向自增主键中插入记录时，其他insert事务都需要等待直到本事务的插入完成才能继续插入自增记录，注意是插入完成而不是本事务完成。这很好理解，因为需要保证自增主键的连贯性。但是如果你有超高的插入并发，那么肯定会带来性能问题。 因此InnoDB也提供了折中的方案，innodb_autoinc_lock_mode参数可以控制你是否使用这种锁，如果你的自增主键不需要严格连贯而且需要更高的insert并发，那么可以禁用掉这种锁。 但是如果你做了主从复制，而且使用的是statement模式的binlog，那么禁用innodb_autoinc_lock_mode后可能造成主从自增主键不一致，尤其是遇到insert ... select ... from table_name;这种语句。此时需要改为row模式或mixed模式的binlog主从复制，因为row模式对SQL执行顺序不敏感，而mixed模式也会将可能影响主从复制的statement改为row模式传输。 那么最后还有个问题就是既需要超高插入并发又需要连贯自增，那该怎么办？ 凉拌~ 二、InnoDB行锁有以下四种： 1.Record lock 即在索引上加的锁，lock_mode分为S和X两种模式。 例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;就会c1列的索引上添加X类型的Record lock。 Record lock一定是加在索引记录上的，即便是一个没有定义任何索引的表，InnoDB也会创建一个隐式的聚集索引，在用到此索引时加Record lock。 2.Gap lock 即间隙锁，锁定不存在的索引记录，官方定义是：Gap lock用于锁定2个索引记录之间、或第一个索引记录之前、或最后一个索引记录之后的范围。 通常我们会把Record lock和Gap lock合起来用，称为Next-key lock，因此Gap lock就不多说了。 之所以设计Gap lock主要是为了解决幻读问题的，参考SQL Server的键范围锁。Gap锁是可以禁用的，你可以将数据库的全局隔离级别设置为read committed或者将innodb_locks_unsafe_for_binlog参数设置为1来禁用Gap lock，只是这样就会出现幻读，不过幻读一般并不是什么大问题，比如Oracle数据库的默认隔离级别下就无法避免幻读，不也大把人在用吗。 另外必须要说的一点是同一个gap上的Gap lock的S和X模式效果完全一样的，就算你加了一个X模式的gap lock，其他事务也能在同一个gap上再加一个X模式的gap lock，不会阻塞，当然仅限于同一个gap。 3.Next-key lock 即Record lock和Gap lock的合体。例如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;会在[10,20]之间的c1 index record上加lock_mode为X的next-key lock，也就是说会在[10,20]之间的所有存在的index record上加X模式的record lock，同时也会用X模式的gap锁锁定不存在的index record防止幻读，这两种锁加起来就称作next-key lock。 如果使用的索引是唯一索引，那么不加next-key lock的，只加record lock。 再次提醒的是next-key lock其实并不存在而是Record lock和Gap lock的合体，show engine innodb statusG显示的结果也都是用Record lock来展示的，不过展示出的数目比较诡异看不懂源码的话不建议深究，这点比Oracle和Sqlserver差太远。这里我就要顺带吐槽一下官网手册了，毕竟是开源DB，一些前后矛盾和明显有歧义的解释也是让人很无奈。 4.插入意向锁（Insert Intention Locks） 这个锁也是一个InnoDB的奇葩例子，不知道大家发现没InnoDB在谈IX IS还有行锁这些锁的时候基本不用insert语句来举例，这点如果是熟悉Oracle和SQL Server的人就会很困惑，因为增删改全都是DML语句，大家加锁机制基本相似的，无非就是表级意向锁+页级or行级锁的套路，但是InnoDB不是这样！！！insert语句和delete、update完全不是一路人！！关于Insert语句的加锁模式可以参考http://www.cnblogs.com/leohahah/p/8863422.html中的INSERT说明部分。 这个锁用于表明：只要不是插入相同的index record，多个事务向同一个gap插入记录是不会阻塞的。 Insert语句的基本加锁模式为：表级IX锁--行级插入意向锁--行级锁。 插入意向锁其实是行级别的一种意向gap锁，既然有意向两字那么可以认定就是用于检测锁冲突的，是为在行级别获取X模式的record lock锁提前做检测。 （编辑：淮南站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
