数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作DML的时候,加 MDL 读锁;当要对表做结构变更操作DDL的时候,加 MDL 写锁。读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。如果对线上一个频繁DML操作的表做DDL如添加字段等操作,可能会导致死锁,使数据库连接资源被消耗完,导致数据库宕机。安全的解决方式是对表做DDL如添加字段时,设置执行语句的超时时间,写锁超时自动释放,不影响读锁。
全局锁
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。
全局锁的典型使用场景是,做全库逻辑备份。,在备份过程中整个库完全处于只读状态。
如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆;如果你在从库上备份,那么备份期间从库不能执行主库同步过来的 binlog,会导致主从延迟。
不加锁的话,备份系统备份的得到的库不是一个逻辑时间点,这个视图是逻辑不一致的。
表级锁
MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。
元数据锁是server层的锁,表级锁,主要用于隔离DML(Data Manipulation Language,数据操纵语言,如select)和DDL(Data Definition Language,数据定义语言,如改表头新增一列)操作之间的干扰。每执行一条DML、DDL语句时都会申请MDL锁,DML操作需要MDL读锁,DDL操作需要MDL写锁(MDL加锁过程是系统自动控制,无法直接干预,读读共享,读写互斥,写写互斥)
读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。
读写锁之间互斥,这是要读写分离的一个理由。为什么要读写分离?
索引要根据表中的每一行的记录值来创建,所以需要全表扫描;加字段或修改字段,也要修改每一行记录中的对应列的数据,所以也要全表扫描
MySQL 不支持 DDL NOWAIT/WAIT n 这个语法。
select @@innodb_lock_wait_timeout; 查询全局资源等待超时时间
set session innodb_lock_wait_timeout=0; 设置当前会话的资源等待超时时间
MDL 会直到事务提交才释放,在做表结构变更的时候,你一定要小心不要导致锁住线上查询和更新。
MySQL lock_wait_timeout
为了减少metadata lock 带来的危害,设置一个合理的 lock_wait_timeout 比较重要,这个值默认是 365 天,我们可以根据自身业务来考虑,避免长时间的 metadata lock 等待。
最后我们简单总结一下: