锁和索引一样也是一种数据结构,索引是为了加快数据的访问速度而设计的,锁是为了控制并发访问而设计的。
数据库事多用户共享的资源,所以要考虑并发访问。
分类:全局锁、表锁、行锁
全局锁
全局锁是对整个数据库实例加锁。
加全局读锁:
Flush tables with read lock
FTWRL
设置全局变量也可以,但客户端异常时不会自动断开 set global readonly=true
,而且readonly可能要用于判断主备
用于需要让整个库处于只读状态的时候,使用后一下语句会被阻塞:
- 数据更新语句(数据的增删改)
- 数据定义语句(包括建表、修改表结构等)
- 更新类事务的提交语句
使用场景
全库逻辑备份–就是把整库的每个表都select出来存成文本
这时候业务是不能更新的(如果更新 有数据不一致的问题)
业务的更新不只是增删改数据(DML),还有可能是加字段等修改表结构的操作(DDL)
释放:
- 主动unlock
- 客户端异常断开,mysql会自动释放这个全局锁
表级锁
表级别的锁两种: 表锁、元数据锁(meta data lock)
表锁
表锁一般是在数据库引擎不支持行锁的时候才会被用到的。
*用表锁的情景一般都能用事务代替
lock tables... read/write
释放:
unlock tables主动释放锁,或者客户端断开的时候自动释放。
写锁是排他锁;读锁是共享锁
MDL元数据锁
MDL不需要显式使用,在访问一个表的时候会被自动加上。MDL保证读写的正确性, 在读写表时,不允许表结构变更。
mysql5.5引入MDL,当对一个表做增删改查操作时候,加MDL读锁;对表做结构变更操作时,加MDL写锁
读锁之之间不互斥,因此可以有多个线程同时对一张表增删改查
读写锁之间,写锁之间是互斥的,用来保证变更表结构操作的安全性。如果有两个线程同时给一个表加字段,需要一个执行完毕后才能执行下一个。
给一个表加字段/修改字段/加索引,需要扫描全表数据: 因为这些操作对所有记录都会产生影响。
给小表加字段
有未提交的事务时无法修改表字段,而且在存在长事务时执行修改表字段命令是一个危险的操作,可能阻塞其他增删改查请求,或导致线程爆满。
遇到长事务,事务不提交,一直站着MDL锁,然后加字段需要写锁(此时block),后面的增删查改操作都无法申请到读锁。客户端如果有重试机制,超时后在起一个洗呢session再请求,那么这个库的线程很快就会爆满。
解决方法:
-
解决长事务
information_schema库的innodb_trx表中,可以查到当前执行中的事务。如果有长事务在执行,可以考虑暂停DDL,或者kill这个长事务 -
针对请求很频繁的热点表
alter table语句里面设定等待时间,如果在这个指定的等待时间能够拿到MDL最好,拿不到就放弃,不要阻塞后面的业务语句。
使用nowait / wait n
alter table tbl_name nowait add column ...
alter table tbl_name wait n add column ...