欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL锁机制

程序员文章站 2022-06-24 16:06:11
本文参考自MySQL官网5.6版本参考手册的14.5.1,此小节说明MySQL的锁分类,此外还有14.5.2小节和14.5.3小节详述事务隔离级别和各SQL语句的加锁模式,后两节将单独写2篇笔记。 https://dev.mysql.com/doc/refman/5.6/en/innodb-lock ......
本文参考自MySQL官网5.6版本参考手册的14.5.1,此小节说明MySQL的锁分类,此外还有14.5.2小节和14.5.3小节详述事务隔离级别和各SQL语句的加锁模式,后两节将单独写2篇笔记。
https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html
 
第一部分:概述
Myisam的锁比较容易理解,无论是读还是写都只会加表锁,表锁又分为read锁和write锁,可以使用如下方式手动加锁:
--加表锁语句(同样适用于InnoDB):
lock tables
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
--解表锁语句:
unlock tables
--如何观察InnoDB锁:
set @@global.innodb_status_output_locks=on; 
--这样show engine innodb status\G可以显示InnoDB额外的锁信息(锁太多时也无法完全显示),标准情况下只显示锁数目。
Myisam的read、write表锁其实可以看做一种元数据锁。
由于Myisam这样的锁机制,导致Myisam是一款读性能较好,并发写性能较差的存储引擎,本文主要讨论如今的MySQL默认存储引擎InnoDB的锁机制。
 
第二部分:InnoDB锁分类
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锁)。
表锁的兼容性图:
MySQL锁机制
此外表级锁还有一种比较特殊的锁: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列的索引上添加S类型的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 status\G显示的结果也都是用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锁提前做检测。
用一个例子来解释更为明了:
--会话A执行:
CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
INSERT INTO child (id) values (90),(102);
START TRANSACTION;
SELECT * FROM child WHERE id > 100 FOR UPDATE;
--会话B执行:
INSERT INTO child (id) VALUES (101);
可以看到会话B被阻塞了,而show engine innodb status\G看到的锁等待如下:
MySQL锁机制
即insert语句想在(90,102)的gap上加个lock_mode=X的gap锁,也就是Insert Intention Lock,但是会话A的select for update语句已经在(100,102)的gap上添加了X模式的gap锁,这是一个与(90,102)不同但被包含在内的gap,于是被阻塞无法获取X模式的Insert Intention Gap Lock。
 
三、总结
MySQL的锁机制基本就如上所示了,但是了解InnoDB锁只是初步的,还必须结合事务隔离级别的概念去判断各种SQL的具体加锁机制,因为事务隔离级别会影响SQL的默认加锁模式。
MySQL的事务隔离级别定义也是遵循ANSI SQL92标准的,不过但凡是家数据库厂商都会说自己遵循SQL92标准,而事实是早已加料加的面目全非。当然这全都是为了能够提供更好的并发性能。例如Oracle也说自己遵循SQL92标准,结果四大隔离级别只支持2个,SQL Server也说自己支持,结果又多造出来2个事务隔离级别。
同样的MySQL也提供了4大基本的事务隔离级别,不同的隔离级别下加锁机制区别很大,参考:http://www.cnblogs.com/leohahah/p/8857124.html