全局锁
Flush tables with read lock (FTWRL)
这是一个锁,执行后数据库处于只读状态。一般用于全库的逻辑备份(把整个表的数据做查询导出为文件)。
set global readonly=true 也可以让全库进入只读状态,但是一般不用来在备份的时候设置,因为有可能通过该变量来判断当前库是主库还是备库。
另外FTWRL 有一些特殊的保护机制,可以在客户端发生异常断开是自动释放全局锁。设置readonly 并没有这种安全保护机制。
表级和行级锁分类原则
锁类型(lock_type)和锁模式(lock_mode)
按模式分
#define LOCK_ORDINARY 0
Next-Key Lock 锁一条记录及其之前的间隙,这是 RR 隔离级别用的最多的锁;( ]
#define LOCK_GAP 512
间隙锁,锁两个记录之间的 GAP,防止记录插入;()
#define LOCK_REC_NOT_GAP 1024
记录锁(就是普通的行锁);
#define LOCK_INSERT_INTENTION 2048
插入意向 GAP 锁,插入记录时使用,是 LOCK_GAP 的一种特例。
按照类型分
- LOCK_IS:读意向锁;
- LOCK_IX:写意向锁;
- LOCK_S:表级读锁;
- LOCK_X:表级写锁;
- LOCK_AUTO_INC:自增锁;
表级锁
1. 表锁
lock tables … read/write
执行后会限制其他线程和本线程对该表的写(lock read)、或者读写(lock write)
2. 元数据锁
metadata lock 用于锁MDL,不需要显式的引用。
对一个表做增删改查会自动加一个MDL 读锁,对表做结构变更时会加一个MDL写锁。
读锁之间不互斥,读写之间,写锁之间是互斥的,所以可同时增删改查,但是不能同时修改表结构。
如果MDL读锁被占用未释放(查询语句未结束,事务提交才会释放MDL读锁),
此时进行表结构变更,会被阻塞住(等待释放MDL读锁),
并且后续的MDL读锁也会被阻塞,此时客户端线程一多,数据库可能会支撑不住。
所以执行表结构变更之前要确定是否有长事务,如果有要kill掉:
information_schema. innodb_trx
5.6的Online ddl 执行过程如下:
1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL //期间数据库可以正常读写
4. 升级成MDL写锁
5. 释放MDL锁
所以理论上说还是要注意,在第一步拿MDL写锁的时候会阻塞。
意向锁
因为表锁和行锁之间是有冲突的
譬如某个表有 10000 条记录,其中有一条记录加了 X 锁,如果这个时候系统需要对该表加表锁,为了判断是否能加这个表锁,系统需要遍历表中的所有 10000 条记录,看看是不是某条记录被加锁,如果有锁,则不允许加表锁,显然这是很低效的一种方法,为了方便检测表锁和行锁的冲突,从而引入了意向锁。
意向锁为表级锁。
当事务试图读或写某一条记录时,会先在表上加上意向锁,然后才在要操作的记录上加上读锁或写锁。
意向锁之间是不会产生冲突的,也不和 AUTO_INC 表锁冲突,它只会阻塞表级读锁或表级写锁,另外,意向锁也不会和行锁冲突,行锁只会和行锁冲突。
IS 读意向
表里正在执行读数据,所以不能获取表级写锁(表结构不能进行修改)。
IX 写意向
表里面有数据在写,不能对表锁的读锁和写锁进行获取
兼容性 | IS | IX | S | X |
IS | 兼容 | 兼容 | 兼容 | 不兼容 |
IX | 兼容 | 兼容 | 不兼容 | 不兼容 |
S | 兼容 | 不兼容 | 兼容 | 不兼容 |
X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
注意上面的 S X 都是表级的。
AUTO_INC 锁
自增锁,是一种特殊类型的表锁。
当插入的表中有自增列(AUTO_INCREMENT)的时候可能会遇到。
当插入表中有自增列时,数据库需要自动生成自增值,在生成之前,它会先为该表加 AUTO_INC 表锁,其他事务的插入操作阻塞,这样保证生成的自增值肯定是唯一的。
行锁(记录锁)
行锁由引擎层实现,所以不是所有的引擎都支持行锁。
在InnoDB中,行锁是需要的时候才加上,但是直到事务提交才会释放。
所以在一个事务中有可能有锁冲突的语句可以放在后面。
如果数据库中有两个事务也在互相等待,那么就是进入死锁状态。
为了避免死锁可以通过设置超时时间和开启死锁检测解决。
1. innodb_lock_wait_timeout (默认50s)
2. innodb_deadlock_detect=no (默认on)
死锁检测本身是一个比较影响性能的操作,时间复杂度是O(n)。比如100个线程并发修改同一行,死锁检测操作会执行 100*100 = 10000次。
S 读锁
又称共享锁(Share locks,简称 S 锁)
加了读锁的记录,所有的事务都可以读取,但是不能修改,并且可同时有多个事务对记录加读锁。
X 写锁
又称排他锁(Exclusive locks,简称 X 锁),或独占锁
对记录加了排他锁之后,只有拥有该锁的事务可以读取和修改,其他事务都不可以读取和修改,并且同一时间只能有一个事务加写锁。
Gap Lock 间隙锁
幻读是指,可重复读隔离级别下,同一个事务中同一条SQL在连续两次读取的结果不一样,后一次读取出来的数据能读到期间新增的数据。
间隙锁是加在两个索引之间,或者是第一个索引之前,或者是最后一个索引之后。
Next-key lock
是一种行锁和间隙锁的结合,区间是左开右闭 ( ]
插入意向锁 Insert Intention Locks
是一种特殊的间隙锁 ,II GAP
和表级意向锁没啥关系,只不过在插入数据的时候需要加入插入意向锁,这回和间隙锁冲突,所以会阻止插入操作。