Mysql全局锁和表级锁

以前对Mysql的锁的认识,只了解表锁和行锁,其实Mysql的锁的种类还是不少的,有全局锁,表级锁,行级锁,还有元数据锁,间隙锁,临界锁。

一 全局锁

Mysql的全局锁是对整个实例加锁,加锁之后,数据库整体处于只读状态,数据上不能执行增删改,元数据上不能执行新建表,更新表;不能提交事务。 命令:

# 给Mysql 加全局锁
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
# 无法执行删除操作
mysql> delete from test where id=1;
ERROR 1223 (HY000): Can\‘t execute the query because you have a conflicting read lock
# 释放全局锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test where id=1;
Query OK, 0 rows affected (0.02 sec)

使用场景:

  1. 全局锁用在逻辑备份的时候,整个库都处于只读状态,便于进行备份,如果数据库不处于只读状态,备份的时候可能会存在逻辑不一致的问题。 对于InnoDB这种支持MVCC(多版本视图)的存储引擎来说,如果数据的事务级别处于可以重复读级别,备份采用mysqldump备份,通过一个命令选项[--single-transaction ]也支持逻辑一致性备份:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --databases db1 db2 db3 > alldb.sql;
mysqldump -uxxx -p --flush-privileges --databases mysql > mysql.sql;

single-transaction 会开启一个事务,保证读到的数据是一致的。

存在问题

  1. 全局锁会造成主库上业务无法正常秩序;
  2. 备库上加全局锁无法进行binlog的同步。

其他 数据库改成只读状态,不仅可以用刚才的全局锁,还可以通过:set global read_only=true 来进行设置,这个好处是不影响备用数据库的同步主库的动作,另外,设置这个无法影响具有super权限的用户修改,试验如下:

mysql> set global read_only=true;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like ‘%read_only%‘;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+

mysql> delete from test where id=2;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> set global super_read_only=true;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=2;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

顺便说下,innodb_read_only 不能动态修改,可以在启动的时候通过:--innodb-read-only[=OFFON] 设置,打开后,会组织InnoDB引擎创建删除表,Mysql8.0后,会阻止任何引擎创建或删除表,因为字典表是采用InnoDB引擎。

transaction_read_only 可以启动时候通过: --transaction-read-only[=OFFON],也可以动态修改, 设置全局[transaction_read_only]值可设置所有后续会话的访问模式. 现有会话不受影响: 具体可以参考:https://s0dev0mysql0com.icopy.site/doc/refman/8.0/en/server-system-variables.html

二 表级锁

表锁,还分两种,一种是专门锁表,不能修改数据,一种是元数据锁(meta data lock)。

表锁优势: 开销小;加锁快;无死锁; 表锁劣势: 表级别锁的范围比较大,所以发生锁的冲突概率高,从而导致并发处理低。

mysql> lock table test read;
Query OK, 0 rows affected (0.01 sec)
mysql> delete from test where id=‘1‘;
ERROR 1099 (HY000): Table ‘test‘ was locked with a READ lock and can‘t be updated
mysql> select * from test;
+----+------+
| id | c    |
+----+------+
|  3 |    3 |
+----+------+
1 row in set (0.01 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=‘1‘;
Query OK, 0 rows affected (0.00 sec)

mysql> lock table test write;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=‘1‘;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+------+
| id | c    |
+----+------+
|  3 |    3 |
+----+------+
1 row in set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

有个需要注意的点,如果对表进行锁定之后,只能执行这个表的操作,不能操作其他表。 另外注意读锁会阻塞写,但是不会阻塞读;表的写锁,会阻塞读和写。

mysql> lock table test read;
mysql> select * from t_user;
ERROR 1100 (HY000): Table ‘t_user‘ was not locked with LOCK TABLES
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+---------+----------------+---------------+----------------------+
| USER_ID | USER_NAME      | USER_PASSWORD | USER_EMAIL           |
+---------+----------------+---------------+----------------------+
|       2 | evan           | 123           |           |

元数据锁: 元数据锁是为了防止我们在查询的时候,表字段突然发生了变化,这样查询的结果可能导致和表数据不一致,所以肯定不行,为了防止这种情况才有了元数据锁。 元数据锁在增删改查数据的时候自动加读锁,在更新表结构的时候自动加写锁,读锁之间不会发生互斥,读锁和写锁之间互斥的。

这里面有个坑,就是如果有个长时候,一个会话在查询,另外一个会话在更改这个表字段,则因为表上已经加元数据的读锁了,所以更改表字段的会话无法执行: A在查询:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+------+
| id | c    |
+----+------+
|  3 |    3 |
+----+------+
1 row in set (0.00 sec)

B在更改表字段,则一直被卡住无法操作。

mysql> alter table test add(d int);

C会话在查询test表将无法查询,也会被卡住:

mysql> use test;
Database changed
mysql> select * from test;

如果是在线程池中,长时间没有反应,可能会重新创建新连接,导致数据库连接池被打满。

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+---------------------------------+-----------------------------+
| Id | User            | Host            | db   | Command | Time | State                           | Info                        |
+----+-----------------+-----------------+------+---------+------+---------------------------------+-----------------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon  | 8021 | Waiting on empty queue          | NULL                        |
|  7 | root            | localhost:51563 | test | Query   |    0 | starting                        | show processlist            |
|  8 | root            | localhost:51641 | test | Query   |  336 | Waiting for table metadata lock | alter table test add(d int) |
|  9 | root            | localhost:51731 | test | Query   |  146 | Waiting for table metadata lock | select * from test          |
+----+-----------------+-----------------+------+---------+------+---------------------------------+-----------------------------+
4 rows in set (0.00 sec)

这样在线更改表结构的时候,需要特别注意,理解做法是等待一定时间超时了自动识别,或者不等待,如果获取不到锁也自动失败。 MariaDB 合并了ALiSQL(阿里维护的MySQL的开源分支,据说性能提升了70%),可以支持不等待或等待特定时间超时了自动识别,语法:

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

Mysql不支持,记得不支持。

相关推荐