六、mysql锁及快速定位死锁

目录

一、什么是死锁

二、锁的类型有哪些？

按照粒度区分

共享锁和排他锁的区别

共享锁和排他锁的使用

        1、排他锁

        2、共享锁：正常的select 语句中，是不会加锁的。例如：

        3、for update 和 lock in share mode的区别：

三、如何快速定位死锁呢？

---

一、什么是死锁

        多个线程在访问某些资源的时候，需要等待对方释放彼此所需的资源，而进入了等待互斥的状态。

二、锁的类型有哪些？

1. 按照粒度区分

   1. 行锁
   2. 表锁

2. 共享锁和排他锁的区别

   1. 共享锁：在多个事务里边都可以读取共享锁所锁住的内容
   2. 排他锁：只能在一个事务里对同样的数据进行加锁，假设A事务对某一行数据加了排他锁，其他事务就无法对该行记录加排他锁。

3. 共享锁和排他锁的使用

        1、排他锁

         ：在innodb引擎中，常见的update、delete、insert，这些默认是加上了排他锁的。select默认不加。如果相加 可以使用 for update 关键字

select * from t_user_message  for update;

        2、共享锁：正常的select 语句中，是不会加锁的。例如：

select * from t_user_message;
如果相加共享锁可以使用一下关键字 lock in share mode
select * from t_user_message lock in share mode; 

        3、for update 和 lock in share mode的区别：

1、lock in share mode 测试
假如 会话A 关闭了自动提交的功能，然后执行一个查询表的操作，并加了共享锁 lock in share mode 锁住了全表
此时 会话B 也关闭了自动提交的功能，然后也执行一直查询这个表的操作，并加上共享锁。此时是可以正常查询的，不会有阻塞。因为共享锁，在多个事务中都可以读取共享锁 锁住的内容
再往后，把会话B的事务提交，然后在会话B中执行一个update 语句，因为update 默认带了排他锁，此时会话A还没提交，所以会话B的update就会阻塞。只有将会话A的事务提交了，B才能执行

2、for update 测试
来看下 for update 加锁的影响。会话A关闭自动提交 ，然后执行一条for update 的sql，但是不提交。此时会话B也开始同样的操作。但是就会卡主
因为 排他锁 只能在一个事务中，对同样的数据加锁，如果A不释放，则B会报错： Lock wait timeout exceeded; try restarting transaction
如果for update 锁住的数据，对于其他回话的写操作，也是不允许的，会话B会阻塞状态。

总结：

	lock in share mode 锁	for update 锁
多session 读	不会阻塞，多个session可以读共享锁住的记录	会阻塞，只允许一个session读取到锁住的记录，其他session等待
多session 写	会阻塞，任何相关的操作都不行	会阻塞，任何相关操作都不行

mysql5.7  5.8 死锁检测
1、查看死锁
select * from information_schema.innodb_locks;
2、查看等待锁
select * from information_schema.innodb_lock_waits;
mysql 8.0 之后
1、查看死锁
select * from performance_schema.data_locks;
2、查看等待锁
select * from performance_schema.data_lock_waits;

三、如何快速定位死锁呢？

MySQL死锁和等待锁是不同的概念，大家不要搞混了

示例：

首先我们创建一个测试表：

由于我的MySQL默认等待锁的时间为 50 秒，超过 50 秒，事务会自动回滚，建议大家把这个参数调低点，
(root@localhost) [test]> SET innodb_lock_wait_timeout = 3;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 3 |
+--------------------------+-------+
或者在配置文件添加 
[mysqld]
innodb_lock_wait_timeout = 3
重启生效
请注意，关闭等待锁超时可能会导致应用程序因为等待锁而永远挂起，因此在关闭超时之前请确保你已经充分评估了可能对系统性能造成的影响。

(root@localhost) [test]> create table t1(id decimal,v_name varchar(10));
Query OK, 0 rows affected (0.02 sec)

(root@localhost) [test]> insert into t1 values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@localhost) [test]> select * from t1;
+------+--------+
| id   | v_name |
+------+--------+
|    1 | a      |
|    2 | b      |
|    3 | c      |
+------+--------+
3 rows in set (0.01 sec)

会话 1，开启事务，更新 id=1 的数据：
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)



(root@localhost) [test]> update t1 set v_name='aa' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

会话 2，开启另一个事务，删除 id=1 的数据：

(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> 
(root@localhost) [test]> 
(root@localhost) [test]> delete from t1 where id=1;
此时会话 2 被锁定，出现锁等待。

我们再开一个会话 3，查查当前的 processlist，看看是否能发现什么？

(root@localhost) [test]> show processlist;
+----+---------------+-----------------+------+-------------+------+-----------------+---------------------------+
| Id | User          | Host            | db   | Command     | Time | State           | Info                      |
+----+---------------+-----------------+------+-------------+------+-----------------+---------------------------+

|  9 | root          | localhost       | test | Sleep       |   14 |                 | NULL                      |
| 10 | root          | localhost       | test | Query       |    4 | updating        | delete from t1 where id=1 |
| 11 | root          | localhost       | test | Sleep       |   31 |                 | NULL                      |
| 12 | root          | localhost       | test | Query       |    0 | starting        | show processlist          |
+----+---------------+-----------------+------+-------------+------+-----------------+---------------------------
6 rows in set (0.00 sec)
我们可以看到 delete 这个 SQL 的进程在执行中，并没有发现其他有价值的内容，那锁在哪里了。接下来的步骤带你一步步的定位出加锁的 SQL。

1、定位锁等待：

(root@localhost) [test]> select * from performance_schema.data_lock_waits \G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 139688434044464:2448:4:5:139688308440496    #请求的引擎锁ID
REQUESTING_ENGINE_TRANSACTION_ID: 474993   #事务ID
            REQUESTING_THREAD_ID: 49        #线程ID
             REQUESTING_EVENT_ID: 54         #事件ID
REQUESTING_OBJECT_INSTANCE_BEGIN: 139688308440496
         BLOCKING_ENGINE_LOCK_ID: 139688434042672:2448:4:5:139688308427448   #阻塞的引擎锁ID
  BLOCKING_ENGINE_TRANSACTION_ID: 474992   #事务ID
              BLOCKING_THREAD_ID: 48       #线程ID
               BLOCKING_EVENT_ID: 86       #事件ID
  BLOCKING_OBJECT_INSTANCE_BEGIN: 139688308427448
1 row in set (0.00 sec)

  
#可以看到 有一个请求锁的事务（请求的引擎锁ID、事务ID、线程ID 和事件ID），它正在等待一个锁，该锁由另一个事务持有（阻塞的引擎锁ID、事务ID、线程ID 和事件ID）。

2、定位锁

(root@localhost) [test]> select * from performance_schema.data_locks where THREAD_ID = 48 \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139688434042672:2448:4:5:139688308427448
ENGINE_TRANSACTION_ID: 474992
            THREAD_ID: 48
             EVENT_ID: 86
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139688308427448
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000002209
1 rows in set (0.00 sec)

结果显示有一个锁相关内容。

3、定位事务 在sql执行期间，通过information_schema.innodb_trx表找到这个sql的事物ID
(root@localhost) [test]> select trx_id,trx_started,trx_requested_lock_id,trx_query,trx_mysql_thread_id from information_schema.innodb_trx where trx_id in ('474993','474992');
+--------+---------------------+------------------------------------------+---------------------------+---------------------+
| trx_id | trx_started         | trx_requested_lock_id                    | trx_query                 | trx_mysql_thread_id |
+--------+---------------------+------------------------------------------+---------------------------+---------------------+
| 474993 | 2024-01-30 11:31:12 | 139688434044464:2448:4:5:139688308441184 | delete from t1 where id=1 |                  10 |
| 474992 | 2024-01-30 11:31:00 | NULL                                     | NULL                      |                   9 |
+--------+---------------------+------------------------------------------+---------------------------+---------------------+
2 rows in set (0.00 sec)

结果有两个事务，MySQL 事务线程 id 为 10 和 9，很直观的看到 10 是我们的 delete 事务，被 9 锁定。

4、定位线程
(root@localhost) [test]> select * from performance_schema.threads where processlist_id=9 \G
*************************** 1. row ***************************
          THREAD_ID: 48
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 9
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 9012
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 1974
     RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

结果找到 MySQL 事务线程 9 对应的服务器线程 48。

5、定位加锁 SQL
(root@localhost) [test]> select * from performance_schema.events_statements_current where thread_id=48  \G
*************************** 1. row ***************************
              THREAD_ID: 48
               EVENT_ID: 85
           END_EVENT_ID: 85
             EVENT_NAME: statement/sql/update
                 SOURCE: init_net_server_extension.cc:95
            TIMER_START: 5517330796401000
              TIMER_END: 5517331297524000
             TIMER_WAIT: 501123000
              LOCK_TIME: 241000000
               SQL_TEXT: update t1 set v_name='aa' where id=1
                 DIGEST: 8b0ea34f02b453ccc12d31fefb671814ac87d99fe6971fa463b4ef28d8b0095e
            DIGEST_TEXT: UPDATE `t1` SET `v_name` = ? WHERE `id` = ?
         CURRENT_SCHEMA: test
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: 00000
           MESSAGE_TEXT: Rows matched: 1  Changed: 1  Warnings: 0
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 1
              ROWS_SENT: 0
          ROWS_EXAMINED: 3
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: 84
     NESTING_EVENT_TYPE: TRANSACTION
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 173
1 row in set (0.00 sec)

 SELECT pt.INFO, ec.THREAD_ID, ec.EVENT_NAME, ec.WORK_COMPLETED, ec.WORK_ESTIMATED, pt.STATE FROM performance_schema.events_stages_current ec left join performance_schema.threads th on ec.thread_id = th.thread_id left join information_schema.PROCESSLIST pt on th.PROCESSLIST_ID = pt.ID where pt.INFO like 'ALTER%';