MySQL 表锁以及FLUSH TABLES操作

青旅半醒 2022-08-09 11:56 30阅读 0赞

创建测试表t1, t2

use test;
    CREATE TABLE `t1` ( `i` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
     CREATE TABLE `t2` ( `i` int(255) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

先来测试一下read锁  
**SESSION 1**

mysql> lock table t1 read;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select * from t1 limit 1; +---+
    | i | +---+
    | 1 | +---+
    1 row in set (0.02 sec)
    
    mysql> insert into t1 values(104);
    ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
    mysql> update t1 set i=105 where i=104;
    ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

可见，对于加了读锁的表，在执行加读锁的session中可读取该表，但不能插入和更新，当然也不能进行删除。

mysql> select * from t2 limit 1;
    ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
    mysql> insert into t2 values(106);
    ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
    mysql> update t2 set i=105 where i=104;
    ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

对于没有加锁的表，不能在执行加锁的session中对表进行访问（包括增删改查）

**SESSION 2**

mysql> select * from t1 limit 1; +---+
    | i | +---+
    | 1 | +---+
    1 row in set (0.04 sec)
    
    mysql> select * from t2 limit 1; +---+
    | i | +---+
    | 1 | +---+
    1 row in set (0.01 sec)
    
    mysql> insert into t1 values(105);
    ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

可见，对于加了读锁的表，在不同的SESSION中不可以进行插入操作（更新和删除同理）。而且可以对任意的表进行读取。

再来测试一下write锁(记得先在SESSION 1解锁刚刚被加锁的表)  
**SESSION 1**

mysql> lock table t1 write;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from t1 limit 1; +---+
    | i | +---+
    | 1 | +---+
    1 row in set (0.03 sec)
    
    mysql> insert into t1 values(1000000);
    Query OK, 1 row affected (0.03 sec)
    
    mysql> update t1 set i=10000001 where i=1000000;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> delete from t1 where i=10000001;
    Query OK, 1 row affected (0.03 sec)

**SESSION 2**

mysql> select * from t1  limit 1;

可见，若SESSION中对表加了写锁，则同一SESSION中对该表的增删改查均可进行，但其他SESSION中的读取和修改操作会被阻塞，直至表锁被释放。

接下来，来了解加锁前表上有尚未执行完成的query会怎样？  
**SESSION 1**(记得先解锁刚刚被加锁的表)

mysql> select i,sleep(60) from t1 limit 1; +---+-----------+
    | i | sleep(60) | +---+-----------+
    | 1 | 0 | +---+-----------+
    1 row in set (1 min 0.03 sec)

**SESSION 2**

mysql> lock table t1 read;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> lock table t1 write ;
    Query OK, 0 rows affected (9.15 sec)

可见，表上有尚未完成的查询操作时可以加读锁，但写锁会被阻塞。

**SESSION 1**

mysql> begin;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> insert into t1 values(1000000);
    Query OK, 1 row affected (13.33 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

**SESSION 2**

mysql> lock table t1 read;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> lock table t1 write ;

可见，表上有完成但尚未提交的插入操作时可以立刻获取读锁，但写锁会被阻塞。

接下来，来了解FLUSH TABLES时表上有尚未执行完成的查询会怎样？  
**SESSION 1**

mysql> select i,sleep(60) from t1 limit 1;

**SESSION 2**

mysql> flush tables t1;

可见，由于将要被flush的表上有查询尚未完成，因此flush tables 操作被阻塞，直至所有表上的操作完成，flush tables操作才得以完成

**SESSION 3**

mysql> select * from t1 limit 1;

由于flush tables t1被阻塞，导致后续其他session中的对于该表的查询被阻塞

**SESSION 4**

另外的一个session中看到的线程状态。直至ID为8825的线程执行完了SQL后续的flush tables动作才得以执行，继而后续的select操作才顺利完成。  
可见执行flush tables操作或者隐含包含flush tables的操作时要小心谨慎。