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)
使用场景：
- 全局锁用在逻辑备份的时候，整个库都处于只读状态，便于进行备份，如果数据库不处于只读状态，备份的时候可能会存在逻辑不一致的问题。 对于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 会开启一个事务，保证读到的数据是一致的。
存在问题
- 全局锁会造成主库上业务无法正常秩序；
- 备库上加全局锁无法进行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不支持，记得不支持。