mysql innodb锁相关问题

yang9527
  • 139

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

(2)如何查询当前锁的级别?

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

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

对于实验三部分
(3)对于实验三部分,同样的,怎么确定是页级锁?
WX20200315-182630@2x.png


这几天实验之后,如 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
回复
阅读 2.7k
1 个回答
  1. 按我的知识储备,innodb没听说过有页级锁,有表锁、行锁和间隙锁
  2. 可以通过如下命令查看服务器有关锁的状态
show status like '%lock%';

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


撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
宣传栏