全局锁和表锁什么场景会用到

痛定思痛。 2023-02-13 08:56 8阅读 0赞

数据库往往是多个用户在连接使用的，那么\*\*如何保证数据并发访问的一致性、有效性呢？\*\*首先我们看一下MySQL的锁机制。  
根据加锁的范围，MySQL中的锁可分为三类：

*  全局锁；
 *  表级锁；
 *  行锁；

## 1. 全局锁 ##

MySQL全局锁会关闭所有打开的表，并使用全局读锁锁定所有表，其命令为：

flush tables with read lock;

当执行上面这个命令后，**所有的表都变成只读状态，数据更新或者字段更新都将会被阻塞**，可以使用下面命令解锁：

unlock tables;

全局锁一般会在什么时候用到呢？  
全局锁一般用在整个库做备份（mysqldump）时。也就是说在整个备份过程中，整个库都是只读的。

*  如果是主库备份，会导致业务不能修改数据；
 *  如果是从库备份，会导致主从延迟；

好在mysqldump包含一个参数–single-transaction，可以在一个事务中创建一致性快照，然后在进行所有表的备份。因此增加这个参数的情况下，备份期间可以进行数据修改。但是需要所有表都是innodb表。所以这也是建议使用InnoDB引擎的原因之一。

## 2. 表级锁 ##

表级锁有两种：表锁和元数据锁。

### 2.1 表锁 ###

表锁使用场景：

1.  事务需要更新某张大表的大部分或全部数据。如果使用默认的行级锁，不仅事务执行效率低，而且可能造成其它事务长时间锁等待或锁冲突，这种情况下可以考虑使用表锁来提高事务执行速度；
2.  事务设计多个表，比较复杂，可能会引起死锁，导致大量事务回滚，可以考虑表锁避免死锁；  
    其中表锁又分为表读锁和表写锁，命令分别是：  
    表读锁：

lock tables t1 read;

lock tables t1 write;

*  对表执行lock tables t1 read（表读锁）时，当前线程和其它线程都可以读，本线程写会报错，其它线程写会等待；
 *  对表执行lock tables t1 write（表写锁）时，当前线程可以读写，其它线程读写都会造成阻塞；

### 2.2 元数据锁 ###

在MySQL中，DDL是不属于事务范畴的。如果事务和DDL并行执行同一张表，可能会出现事务特性被破坏等bug。为了解决这个问题，MySQL引入了元数据锁（MDL锁）。MDL锁的出现解决了同一张表上事务和DDL并行执行时可能导致数据不一致的问题。很多情况下，我们需要考虑MDL的存在，否则可能导致长时间锁等待甚至连接被打满的情况。  
比如：执行如下SQL1语句，表示10秒后返回结果：

select id, a, b, SLEEP(10) from t1 limit 1;

在此期间，执行如下两条SQL语句：  
SQL2：

alter table t1 add column c int;

SQL3：

select id, a, b from t1 limit 1;

可以看出，SQL1属于慢SQL，在慢SQL期间，如果执行了DDL语句，即执行了SQL2，SQL2会等待（原因是SQL1在执行期间对t1加了MDL锁，SQL2又会跟SQL1争抢MDL锁），再执行SQL3，SQL3也会等待，因为SQL1没执行结束的话，其他的所有SQL查询都会等待。  
如果出现这种情况，可以kill掉SQL1或者终止SQL2的DDL操作，让其他查询操作恢复。  
因此在工作中应该避免慢查询、尽量保证事务及时提交，同时避免在业务高峰执行DDL操作。

## 3. 总结 ##

*  全局锁会让所有的表变成只读状态，所有更新操作都会被阻塞，通常进行备份操作（mysqldump）时会用到全局锁；
 *  表级锁分为表锁和元数据锁，表锁又分为表读锁和表写锁，两者的区别是：
    
        表读锁：本线程和其他线程可以读，本线程写会报错，其他线程写会等待；
         表写锁：本线程可以读写，其他线程读写都会阻塞；
 *  为了保证事务和DDL并行执行数据一致性，引入了MDL锁，应该注意的几点是：
    
        尽量避免慢查询；
          事务要及时提交；
          避免在业务高峰执行DDL操作；