MySQL 表锁以及FLUSH TABLES操作

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zyz511919766/article/details/49336101

创建测试表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

mysql> select * from processlist where db='test';
+------+------+-----------+------+---------+------+-------------------------+------------------------------------+
| ID   | USER | HOST      | DB   | COMMAND | TIME | STATE                   | INFO                               |
+------+------+-----------+------+---------+------+-------------------------+------------------------------------+
| 8827 | root | localhost | test | Query   |   31 | Waiting for table flush | select * from t1 limit 1           |
| 8825 | root | localhost | test | Query   |   60 | User sleep              | select i,sleep(60) from t1 limit 1 |
| 8826 | root | localhost | test | Query   |   45 | Waiting for table flush | flush tables t1                    |
+------+------+-----------+------+---------+------+-------------------------+------------------------------------+

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

mysql> select * from processlist where db='test';
+------+------+-----------+------+---------+------+-------+------+
| ID   | USER | HOST      | DB   | COMMAND | TIME | STATE | INFO |
+------+------+-----------+------+---------+------+-------+------+
| 8827 | root | localhost | test | Sleep   |  245 |       | NULL |
| 8825 | root | localhost | test | Sleep   |  274 |       | NULL |
| 8826 | root | localhost | test | Sleep   |  259 |       | NULL |
+------+------+-----------+------+---------+------+-------+------+
展开阅读全文

没有更多推荐了,返回首页