MySQL锁的设计初衷就是处理并发。根据加锁的范围,MySQL的锁大致可分为:全局锁、表级锁和行锁。
全局锁
全局锁就是对整个数据库实例加锁。命令: flush tables with read lock(FTWRL)。使用这个命令的时候整个数据库处于只读状态,其它线程会被阻塞:数据更新语句数据的(增删改)、数据的定义(建表,修改表结构)和更新事务的提交语句。
应用场景:做全库逻辑备份(把整个数据库的所有表都select出来存成文本)。这个状态下如果客户端异常重启,MySQL会自动释放这个全局锁。
备份还有一个方法就是:在可重复读级别下开启一个事务。在这个事务下读区到的数据可以保持数据一致性。由于MVCC的支持,这个过程数据是可以正常更新的。这个方法的
前提是引擎要支持这个事务的隔离级别。
还有一个办法也可以使整个数据库处于只读状态,set global readonly=true;但是一般read only一般会用来做其他逻辑,比如判断一个库是主库还是从库,而且将数据库设置成read only之后,如果客户端发生异常,则数据库就会一直处于readonly状态
表级锁
表级别锁有两种:表锁和原数据锁(meta data lock,MDL)。
语法:
锁表 lock tables T read/write; 该命令会限制其他线程的读写操作,也会限制本线程下面的操作对象。
解锁 unlock tables T,或者客户端断开的时候释放
例如:线程A执行 lock tables t1 read, t2 write; 则 其他线程对t1的写,t2的读会被阻塞。同时在unlock tables之前,也只能执行读t1,写t2操作,也不能访问其他表。
元数据锁(MDL)
MDL不需要显式使用,在访问一个表的时候会自动加上。MDL是在5.5版本中引入的,当对一个表进行增删改查的时候,加MDL读锁;当要对表结构做变更操作的时候,加MDL写锁。
机制:
读锁之间不互斥,因此你可以有多个线程对表进行增删改操作
读写锁之间,写锁之间互斥,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另外一个执行完之后才能开始执行。
所以在修改表结构的时候,最好确定此时这张表没有长事务,或者在alter table语句里设置等待时间;预发是 alter table T wait n add column ...
行锁
行锁是各个引擎引入的,所以不是所有的引擎都支持行锁。不支持行锁意味着并发的时候只能使用表锁。
两阶段锁
在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,要等要事务执行结束时才释放。
所以,如果事务中需要锁多个行,要把最可能造成锁冲突,最可能影响并发度的锁尽量放在后面。
例如:
session A
begin;
update t1 set a = 1 where id = 1;
update t1 set a = 2 where id = 2;
session B
begin;
update t1 set a = 2 where id = 1;
在session A 事务结束之前 seaaion B会被阻塞。
死锁和死锁检测
死锁:当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源的时候,就会导致这几个线程都处于无限等待的状态名称为死锁。
例如:
事务 A 事务 B
begin; begin;
update t set k = k + 1 where id = 1;
update t set k = K + 1 where id = 2;
update t set k = k + 1 where id = 2;
update t set k = k + 1 where id = 1;
此时事务A在等待事务B释放id = 2的行锁,而事务B在等待事务A释放id = 1的行锁就进入了死锁。
解决方案:
1.直接进入等待,直到超时。设置超时的参数 innodb_lock_wait_timeout。
2.发起死锁检测,发现死锁后,主动回滚死锁链条中的某个事务,参数 innodb_deadlock_detect :on表示开启,这个会很耗cpu