mysql innodb锁相关问题

Question

参考MySQL InnoDB存储引擎锁机制实验，做实验。
对于实验一部分.
（1）对于页级锁，mysql innodb支持么？
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁。

（2）如何查询当前锁的级别？

在客户端2最后执行update之前，查看当前锁定的事务，查询结果是空的。

执行客户端2最后执行update之后，我在客户端2等待的过程中，在另外的一个新开的命令行窗口，执行查询，看到lock_type的值是RECORD。

对于实验三部分
（3）对于实验三部分，同样的，怎么确定是页级锁？

---

这几天实验之后，如 Why does 'lock_type' of information_schema.INNODB_LOCKS always RECORD? ，实验都做过了，也尝试在安装了mysql8版本的环境中尝试，依然没有得到想要的答案。最终问题就是：如何在mysql中查看确定某某锁，就是表锁。在mysql8中，实验的结果，如下。
另外，相对来说，比较靠谱的一个介绍是https://www.yuque.com/yinjian...

---

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4697643560:1059:140516036457320
ENGINE_TRANSACTION_ID: 2070
            THREAD_ID: 55
             EVENT_ID: 19
        OBJECT_SCHEMA: test_lock
          OBJECT_NAME: mysql_table
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140516036457320
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4697643560:2:4:2:140516079667736
ENGINE_TRANSACTION_ID: 2070
            THREAD_ID: 55
             EVENT_ID: 19
        OBJECT_SCHEMA: test_lock
          OBJECT_NAME: mysql_table
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140516079667736
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: WAITING
            LOCK_DATA: 3
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4697644416:1059:140516036459288
ENGINE_TRANSACTION_ID: 2065
            THREAD_ID: 56
             EVENT_ID: 12
        OBJECT_SCHEMA: test_lock
          OBJECT_NAME: mysql_table
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140516036459288
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4697644416:2:4:2:140516079673032
ENGINE_TRANSACTION_ID: 2065
            THREAD_ID: 55
             EVENT_ID: 19
        OBJECT_SCHEMA: test_lock
          OBJECT_NAME: mysql_table
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140516079673032
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
4 rows in set (0.02 sec)

mysql> select * from information_schema.innodb_lock_waits;
ERROR 1109 (42S02): Unknown table 'INNODB_LOCK_WAITS' in information_schema
mysql> select * from information_schema.innodb_trx;
+--------+-----------+---------------------+----------------------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id            | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                   | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+----------------------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 2070   | LOCK WAIT | 2020-03-17 19:06:02 | 4697643560:2:4:2:140516079667736 | 2020-03-17 19:06:02 |          2 |                  15 | delete from mysql_table where ticket_id='3' | starting index read |                 1 |                 1 |                2 |                  1136 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 2065   | RUNNING   | 2020-03-17 18:56:46 | NULL                             | NULL                |          4 |                  16 | NULL                                        | NULL                |                 0 |                 1 |                2 |                  1136 |               3 |                 2 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+--------+-----------+---------------------+----------------------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.08 sec)

mysql> select * from information_schema.innodb_locks;
ERROR 1109 (42S02): Unknown table 'INNODB_LOCKS' in information_schema
mysql> show full processlist;
+----+-----------------+---------------------+-----------+---------+------+------------------------+---------------------------------------------+
| Id | User            | Host                | db        | Command | Time | State                  | Info                                        |
+----+-----------------+---------------------+-----------+---------+------+------------------------+---------------------------------------------+
|  4 | event_scheduler | localhost           | NULL      | Daemon  | 6072 | Waiting on empty queue | NULL                                        |
| 15 | root            | 192.168.3.106:57050 | test_lock | Query   |   24 | updating               | delete from mysql_table where ticket_id='3' |
| 16 | root            | 192.168.3.106:57442 | test_lock | Sleep   |   27 |                        | NULL                                        |
| 17 | root            | 192.168.3.106:57660 | NULL      | Query   |    0 | starting               | show full processlist                       |
+----+-----------------+---------------------+-----------+---------+------+------------------------+---------------------------------------------+
4 rows in set (0.01 sec)








mysql> create table mysql_table(
    ->  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增的主键',
    ->  `name` varchar(50) DEFAULT NULL COMMENT 'node name',
    ->  `ticket_id` varchar(50) NOT NULL,
    ->  PRIMARY KEY (`id`)
    -> );


mysql> delete from mysql_table where ticket_id='3';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


mysql>  INSERT INTO mysql_table (name, ticket_id) values ('4', '4');
Query OK, 1 row affected (0.00 sec)



从上面的结果看出来，delete是2070；insert是2065。
两个都是record锁。除意向排他锁之外，其他都是record锁。

server环境是：Server version: 8.0.19 MySQL Community Server - GPL

Answer 1

1. 按我的知识储备，innodb没听说过有页级锁，有表锁、行锁和间隙锁
2. 可以通过如下命令查看服务器有关锁的状态

show status like '%lock%';

BDB引擎没了解过，工作中主要是用innodb

---

• 有关表锁的一些实践可以参看博客：https://www.cnblogs.com/kerry...
• innodb的锁相关的入门介绍可以看：https://segmentfault.com/a/11...