MySQL支持四种级别的锁:全局锁、表级锁、页级锁、行级锁
MyISAM和Memory存储引擎采用的是表级锁;
InnoDB存储引擎既支持行级锁,也支持表级锁,但默认情况下是采用行级锁。
全局锁就是对整个数据库进行加锁,加锁后整个数据库就是只读状态,经常被用于整个数据库的备份操作中,保证数据的完整性。
加锁语法:
flush tables with lock;
释放锁语法:
unlocak tables;
开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
表级锁的分类:
独占锁 | 共享锁 | |
---|---|---|
独占锁 | 冲突 | 冲突 |
共享锁 | 冲突 | 兼容 |
在执行查询语句(select)前,会自动给涉及的所有表加读锁。
在执行更新操作(select、update、insert)前,会自动给涉及的表加写锁。
表锁的加锁过程并不需要用户干预,用户一般不需要直接用
LOCK TABLE
命令给表手动加锁。如果需要手动加锁,在执行
LOCK TABLES
后,只能访问显式加锁的这些表,不能访问未加锁的表。锁定表-语法:
lock table tbl_name {READ | WRITE};
解锁表-语法:
unlock tables
(当手动加锁时,如果同一个表在SQL语句中出现多次,则要通过与SQL语句中相同的别名锁定多次,例如:
lock table sys_user as a read,sys_user as b read
)
只在InnoDB存储引擎中支持。
开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。
InnoDB行锁是通过索引实现的,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。
行级锁的分类:
X-排它锁 | S-共享锁 | 不加锁(Select) | |
---|---|---|---|
X-排它锁 | 冲突 | 冲突 | 兼容 |
S-共享锁 | 冲突 | 兼容 | 兼容 |
InnoDB引擎默认的修改数据操作(update、delete、insert)都会自动给涉及到的数据加上排他锁,而select语句默认不会加任何锁类型。
语法: select … for update
解释:不希望别的事务修改数据,其他的事务只能用不带锁的select来查询,无法进行update、delete、insert
语法:select … lock in share mode
解释:不希望别的事务修改数据,但可以用select不带任何锁来查询数据或同样添加共享锁来查询数据
InnoDB在加排它锁或共享锁的同时,会在表上增加一个表级的意向排它锁或意向共享锁。
意向锁使得其他事务在对表增加表锁时,不需要遍历表中的所有数据,来判断是否有数据被加锁。
意向排它锁:其他事务可以对其他数据加行级锁,但不能增加表级读锁或写锁
意向共享锁:其他事务可以对其他数据加行级锁,也可以增加表级读锁,但不能增加表级写锁
(意向锁只是InnoDB中的一个性能优化机制,了解即可)
分为悲观锁和乐观锁,是人们定义出来的概念,可以理解为一种思想,不属于MySQL中的锁机制。
对于数据的处理持悲观态度,总认为会发生冲突,获取和修改数据时,别人也会修改数据。
实现方法:在MySQL中,所有的锁机制都是悲观锁,例如:排它锁和共享锁。
应用场景:悲观锁通常在发生冲突几率较大时使用,悲观锁保证了数据处理的安全性,但也增加了事务加锁的开销,降低了效率。
对数据的处理持乐观态度,认为一般情况下不会发生冲突,只会在提交数据更新时,才会对数据是否冲突进行检测。
实现方法:一般通过记录数据版本来实现,在数据表中增加version版本号字段,在更新数据时判断是否与数据取出时的数据版本号一致,一致则更新数据并更新版本号,如果不一致,则根据业务需求再进行处理。乐观锁是基于程序来实现的,所以不存在死锁的情况,适用于冲突比较少的场景。
在InnoDB存储引擎中,行级锁可能会发生死锁(指多个事务互相请求对方所占用的资源时,导致一种互相等待的现象)
死锁解决方法:
自增长锁是一种特殊的表锁机制,在事务对包含了 AUTO_INCREMENT 列的表中新增数据时就会去持有自增锁,其他事务尝试执行插入时,则会被阻塞。自增锁在sql执行完就释放锁,并不是事务执行完。
在MySQL5.1.2版本之后,可以手动调整自增加锁的方式。
获取当前锁的语法:
show variables like 'innodb_autoinc_lock_mode';
innodb_autoinc_lock_mode有3种模式:0、1、2,分别对应”传统模式”, “连续模式”, “交错模式”。
传统模式:所有的 insert 语句串行执行,可以保证单个语句内生成的自增值是连续的,性能较差。
连续模式:MySQL8.0之前默认的模式,在该模式下,如果 insert 语句能够提前确定插入的数据量,则可以不用获取自增锁(例如简单的 insert into)。如果 insert 语句不能提前确定数据量,则会去获取自增锁(例如 insert into … select … )。
交错模式:该模式下,所有 insert 语句(包含可确定数据量的和不可确定数据量的)都不会使用自增锁,而是使用较为轻量的 mutex 锁,多条 insert 语句可以并发执行。但是副作用是单个 insert 语句的自增值无法保证连续,AUTO_INCREMENT 的值分配会在多个 insert 语句中来回交叉执行。
交错模式缺陷:在使用MySQL的Binlog进行主从同步时,如果采用 Statement,那么就不能将自增锁模式设置为交错模式,因为 Statement 模式是基于语句的,只记录对数据做了修改的SQL语句,如果自增锁模式设置为交错模式,那么就有可能会导致主从库同一数据的 AUTO_INCREMENT 列的值不一致(因为交错模式的特性)
元数据锁是MySQL为了避免表数据在增删改查时,执行了DDL(定义数据库表字段)导致冲突而增加的锁。
在访问任何一张表的时候,MySQL都会在表上增加元数据锁,无需显示使用。