MyISAM 和 InnoDB 存储引擎使用的锁:
- MyISAM 采用表级锁(table-level locking)。
- InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
表级锁和行级锁对比:
- 表级锁: MySQL中锁定 粒度最大 的一种锁,对当前操作的整张表加锁,实现简单,资源消耗也比较少,加锁快,不会出现死锁。其锁定粒度最大,触发锁冲突的概率最高,并发度最低,MyISAM和 InnoDB引擎都支持表级锁。
- 行级锁: MySQL中锁定 粒度最小 的一种锁,只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小,并发度高,但加锁的开销也最大,加锁慢,会出现死锁。
根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。
加锁
1)自动加锁:
delete / update / insert 语句会自动加上排他锁;
2)手动加排它锁:
select * from student where id=1 FOR UPDATE;
全局锁
全局锁就是对整个数据库实例加锁。
全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。
表级锁
表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。
MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。
MDL 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。
因此,在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。
- 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
- 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。
给一个表加字段,或者修改字段,或者加索引,需要扫描全表的数据。
事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。
行级锁
在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。
知道了这个设定,如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
行锁锁的是什么
MySQL InnoDB 的锁是靠锁住索引项来实现的。
1、为什么锁住唯一索引后,主键索引也会被锁住?
这个是由InnoDB索引的存储和检索方式决定的。辅助索引中存储的是二级索引和主键的ID,所以锁住辅助索引后,会根据主键ID找到对应的主键索引,也锁定之(图中红色箭头)。
而通过主键索引检索数据加锁,则只会锁住主键索引(图中绿色箭头)。
2、为什么表没有索引,表里所有的记录都会被锁住?
当表上没有创建索引的时候,InnoDB会为每一行创建一个隐藏的主键作为聚集索引。这个隐藏的主键是一个6个字节的列,该列的值会随着数据的插入自增。
当不通过索引检索数据的时候,MySQL会使用全表扫描,此时所有行的索引都会被锁定,行锁升级为表锁。