MySQL锁问题分析-全局锁

最新推荐文章于 2023-07-08 17:34:41 发布

那只斑马不睡觉

最新推荐文章于 2023-07-08 17:34:41 发布

阅读量1.2k

点赞数 1

文章标签： java maven tomcat spring jar

原文链接：http://blog.itpub.net/29785807/viewspace-2887472/

版权

MySQL锁分类

MySQL锁的分类有很多种，其中根据影响范围来划分主要分为全局锁、表锁、行锁。

MySQL锁实现

MySQL数据库里面的锁是基于索引实现的，在Innodb中锁都是作用在索引上面的，当SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁）。

全局读锁

MySQL 全局锁会申请一个全局的读锁，对整个库加锁。

1.备份时为了得到一致性备份，可能会添加全局读锁。

2.主从复制架构下，主备切换可能会用到全局读锁。

全局锁的实现方式有两种：

//第一种方法Flush tables with read lock（FTWRL）

//第二种方法set global readonly=true

当数据库处于全局锁的状态时，其他线程的以下语句会被阻塞：

数据更新语句（数据的增删改）、数据定义语句（建表、索引变更、修改表结构等）和更新类事务的提交语句。

释放全局锁

unlock tables;

全局读锁问题分析

在MySQL5.7之前的版本，要排查谁持有全局读锁，通常在数据库层面是很难直接查询到有用数据的(innodb_locks表也只能记录InnoDB层的锁信息，而全局读锁是Server层的锁，所以无法查询到)。

从MySQL5.7版本开始提供了
performance_schema.metadata_locks表，用来记录一下Server层的锁信息(包括全局读锁和MDL锁等)。

下面通过示例演示如何找出谁持有全局锁：

数据库版本MySQL5.7.35

MySQL [cjcdb]> select version();
+------------+
| version()  |
+------------+
| 5.7.35-log |
+------------+
1 row in set (0.00 sec)

创建测试数据

MySQL [cjcdb]> create table t1(id int,age int);
Query OK, 0 rows affected (0.04 sec)
MySQL [cjcdb]> insert into t1 values(1,100),(2,30),(3,80);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

开启锁监控

performance_schema参数：

在MySQL 5.7中该参数默认关系，需要手动开启该参数并重启数据库实例生效，MySQL 8.0 开始默认开始该参数。

setup_instruments表：

事件采集配置项表，可指定具体采集项单独开启进行开启，MySQL对指定事件进行监控采集。

setup_consumers表：

事件存储配置表，可指定具体的监控采集项目进行开启，MySQL对setup_instruments表控制采集到的监控数据进行消费存储

PS可以监控哪些类别的事件信息

语句事件监控：有助于分析数据库SQL层面的资源消耗与瓶颈

等待事件监控：有助于分析所等待相关的问题排查

阶段事件监控：有助于分析数据库各事件资源消耗与瓶颈点

事务事件监控：有助于分析事务相关的信息

文件系统调用事件监控：有助于分析

内存使用事件监控：有助于分析数据库内存的消耗

针对以上各类事件的监控，数据库对于这些监控数据的存储主要包括：

当前事件记录信息表： events_xxx_current

历史事件记录信息表：events_xxx_history

长历事件记录信息表：events_xxx_history_long

各维度进行分组聚合后的事件统计表:


events_xxx_summary_by_account_by_event_name
events_xxx_summary_by_host_by_event_name
events_xxx_summary_by_instance
events_xxx_summary_by_thread_by_event_name
events_xxx_summary_by_user_by_event_name
events_xxx_summary_global_by_event_name

开启锁相关监控

检查锁监控是否启用


MySQL [(none)]> select * from performance_schema.setup_instruments where name like '%lock%' limit 20;
+---------------------------------------------------------+---------+-------+
| NAME                                                    | ENABLED | TIMED |
+---------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc               | NO      | NO    |
| wait/synch/mutex/sql/LOCK_des_key_file                  | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit         | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue   | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done           | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue    | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index          | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log            | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync           | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync_queue     | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xids           | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit       | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit_queue | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_done         | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_flush_queue  | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index        | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_log          | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync         | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync_queue   | NO      | NO    |
+---------------------------------------------------------+---------+-------+
20 rows in set (0.01 sec)

如未开启，需要手动启动

MySQL [(none)]> update performance_schema.setup_instruments set enabled = 'YES' where  name like '%lock%';
Query OK, 173 rows affected (6.27 sec)
Rows matched: 180  Changed: 173  Warnings: 0

打开一个会话

MySQL [cjcdb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              11 |
+-----------------+
1 row in set (0.00 sec)

全局锁

MySQL [cjcdb]> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

开启第 二个会话

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               8 |
+-----------------+
1 row in set (0.00 sec)

执行update语句，被阻塞

mysql> update t1 set age=200 where id=2;

查看阻塞详细信息

开启第三个会话

MySQL [(none)]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              10 |
+-----------------+
1 row in set (0.00 sec)

查看当前会话信息

MySQL [(none)]> show processlist;
+----+------+-----------+-------+---------+------+------------------------------+----------------------------------+
| Id | User | Host      | db    | Command | Time | State                        | Info                             |
+----+------+-----------+-------+---------+------+------------------------------+----------------------------------+
|  8 | root | localhost | cjcdb | Query   |   27 | Waiting for global read lock | update t1 set age=200 where id=2 |
| 10 | root | localhost | NULL  | Query   |    0 | starting                     | show processlist                 |
| 11 | root | localhost | cjcdb | Sleep   |   43 |                              | NULL                             |
+----+------+-----------+-------+---------+------+------------------------------+----------------------------------+
3 rows in set (0.00 sec)

可以看到,process id为8的线程State为Waiting for global read lock，表示正在等待全局读锁。

如何查询阻塞的源头：



MySQL [cjcdb]> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.00 sec)
MySQL [cjcdb]> select * from information_schema.innodb_lock_waits;
Empty set, 1 warning (0.00 sec)
MySQL [cjcdb]> select * from information_schema.innodb_trx\G;
Empty set (0.00 sec)
MySQL [cjcdb]> show engine innodb status\G;
......
------------
TRANSACTIONS
------------
Trx id counter 104240
Purge done for trx's n:o < 104240 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422053759651440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422053759649616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422053759650528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
......

通过上面的常规手段查询，没有任何有用的信息。

在MySQL 5.7版本开始提供了
performance_schema.metadata_locks表。


MySQL [(none)]> select * from performance_schema.metadata_locks where OWNER_THREAD_ID !=sys.ps_thread_id(connection_id())\G;
*************************** 1. row ***************************
          OBJECT_TYPE: GLOBAL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140577980262000
            LOCK_TYPE: SHARED    ###共享锁
        LOCK_DURATION: EXPLICIT  ###显示 
          LOCK_STATUS: GRANTED   ###已授权
               SOURCE: 
      OWNER_THREAD_ID: 36        ###持有锁的内部线程ID为36
       OWNER_EVENT_ID: 9
*************************** 2. row ***************************
          OBJECT_TYPE: COMMIT
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140577970655552
            LOCK_TYPE: SHARED    ###共享锁
        LOCK_DURATION: EXPLICIT  ###显示 
          LOCK_STATUS: GRANTED   ###已授权
               SOURCE: 
      OWNER_THREAD_ID: 36        ###持有锁的内部线程ID为36
       OWNER_EVENT_ID: 9
*************************** 3. row ***************************
          OBJECT_TYPE: GLOBAL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140578306233392
            LOCK_TYPE: INTENTION_EXCLUSIVE  ###意向排他锁
        LOCK_DURATION: STATEMENT            ###语句
          LOCK_STATUS: PENDING              ###状态为PENDING,表示正在等待被授权
               SOURCE: 
      OWNER_THREAD_ID: 33                   ###被阻塞的内部线程ID为33
       OWNER_EVENT_ID: 12
3 rows in set (0.00 sec)
ERROR: No query specified

通过查询可知，线程ID为33的会话，被线程ID为36的阻塞了。

那么如何通过线程ID找到对应的会话ID呢？

MySQL [(none)]> select THREAD_ID,PROCESSLIST_ID,name,type from  performance_schema.threads where PROCESSLIST_ID is not NULL;
+-----------+----------------+--------------------------------+------------+
| THREAD_ID | PROCESSLIST_ID | name                           | type       |
+-----------+----------------+--------------------------------+------------+
|        28 |              3 | thread/sql/compress_gtid_table | FOREGROUND |
|        33 |              8 | thread/sql/one_connection      | FOREGROUND |
|        35 |             10 | thread/sql/one_connection      | FOREGROUND |
|        36 |             11 | thread/sql/one_connection      | FOREGROUND |
+-----------+----------------+--------------------------------+------------+
4 rows in set (0.00 sec)

或者通过process id查找thread id。


MySQL [(none)]> select sys.ps_thread_id(8);
+---------------------+
| sys.ps_thread_id(8) |
+---------------------+
|                  33 |
+---------------------+
1 row in set (0.00 sec)
MySQL [(none)]> select sys.ps_thread_id(11);
+----------------------+
| sys.ps_thread_id(11) |
+----------------------+
|                   36 |
+----------------------+
1 row in set (0.00 sec)

可以看到，正式process id 11 阻塞了 process id 8，当前process id的Command为Sleep。


MySQL [(none)]> show processlist;
+----+------+-----------+-------+---------+------+------------------------------+----------------------------------+
| Id | User | Host      | db    | Command | Time | State                        | Info                             |
+----+------+-----------+-------+---------+------+------------------------------+----------------------------------+
|  8 | root | localhost | cjcdb | Query   |  934 | Waiting for global read lock | update t1 set age=200 where id=2 |
| 10 | root | localhost | NULL  | Query   |    0 | starting                     | show processlist                 |
| 11 | root | localhost | cjcdb | Sleep   |  950 |                              | NULL                             |
+----+------+-----------+-------+---------+------+------------------------------+----------------------------------+
3 rows in set (0.00 sec)

那么如何知道process id 11执行了什么操作？

通过
performance_schema.events_statements_current可以找到process id 11正在执行和最后一次执行完成的语句信息。

这个信息并不一定可靠，因为该表只记录每个线程正在执行和最近一次执行完成的语句信息，一旦这个线程有新的语句执行，原来的语句会被覆盖。


MySQL [(none)]> select * from performance_schema.events_statements_current where thread_id=36\G;
*************************** 1. row ***************************
              THREAD_ID: 36
               EVENT_ID: 8
           END_EVENT_ID: 8
             EVENT_NAME: statement/sql/flush
                 SOURCE: 
            TIMER_START: 3250383126081000
              TIMER_END: 3250383282239000
             TIMER_WAIT: 156158000
              LOCK_TIME: 0
               SQL_TEXT: flush table with read lock
                 DIGEST: 172f9471494a101656e6cb75da3e2726
            DIGEST_TEXT: FLUSH TABLE WITH READ LOCK 
         CURRENT_SCHEMA: cjcdb
            OBJECT_TYPE: NULL

在process id 11里执行一次select version();后。

再次查询
performance_schema.events_statements_current，SQL信息已经变了。

MySQL [(none)]> select * from performance_schema.events_statements_current where thread_id=36\G;
*************************** 1. row ***************************
              THREAD_ID: 36
               EVENT_ID: 9
           END_EVENT_ID: 9
             EVENT_NAME: statement/sql/select
                 SOURCE: 
            TIMER_START: 4631392503744000
              TIMER_END: 4631392573894000
             TIMER_WAIT: 70150000
              LOCK_TIME: 0
               SQL_TEXT: select version()
                 DIGEST: 54cf1693fbf9ac7057b89a6958fe9519
            DIGEST_TEXT: SELECT `version` ( ) 
         CURRENT_SCHEMA: cjcdb
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL

释放全局读锁：

在产生阻塞的会话内释放全局读锁。

MySQL [cjcdb]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

如果本文对你有帮助，别忘记给我个3连，点赞，转发，评论，，咱们下期见。

收藏等于白嫖，点赞才是真情。

那只斑马不睡觉

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL锁问题分析-全局锁

MySQL锁分类MySQL锁的分类有很多种，其中根据影响范围来划分主要分为全局锁、表锁、行锁。MySQL锁实现MySQL数据库里面的锁是基于索引实现的，在Innodb中锁都是作用在索引上面的，当SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁）。全局读锁MySQL 全局锁会申请一个全局的读锁，对整个库加锁。1.备份时为了得到一致性备份，可能会添加全局读锁。2.主从复制架构下，主备切换可能会用到全局读锁。全局锁的
复制链接

扫一扫