[Status] Table_locks_immediate

最新推荐文章于 2024-04-15 11:20:45 发布

原创最新推荐文章于 2024-04-15 11:20:45 发布 · 5.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#lock #mysql #status

Mysql Management 专栏收录该内容

23 篇文章

订阅专栏

本文通过实例演示了MySQL中表锁计数器Table_locks_immediate的触发条件，包括更新操作和选择操作如何影响该计数器，并解释了InnoDB引擎下IX锁的作用。

查看了一些文章，对这个指标还是有些模糊，首先这个指标的定义是The number of times that a request for a table lock could be granted immediately请求表锁立即获得的次数。

我们可以来简单测试一下：

(root@127.0.0.1)[test]> show create table yhs\G;
*************************** 1. row ***************************
       Table: yhs
Create Table: CREATE TABLE `yhs` (
  `id` int(11) NOT NULL DEFAULT '0',
  `val` varchar(10) NOT NULL,
  `test` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR: 
No query specified

(root@127.0.0.1)[test]> show status like 'table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 0     |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

(root@127.0.0.1)[test]> update yhs set test=3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@127.0.0.1)[test]> show status like 'table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 1     |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

嗯，没有问题，update表锁table_locks_immediate + 1...此时会不会有另一个疑问，还有没有其他情况这个指标增长呢，行锁的情况呢？一起来看看

(root@127.0.0.1)[test]> show status like 'table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 1     |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

(root@127.0.0.1)[test]> update yhs set test=3 where id = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@127.0.0.1)[test]> show status like 'table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 2     |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

诶？行锁也会导致该指标加1......如果这块不理解的同学就需要去翻看锁相关的知识点了

因为innodb在对行加X锁的之前，要请求表的IX锁，当获得表IX锁之后，才会继续申请行X锁，然后修改数据

看这个测试结果，IX锁也会使该指标加1....还有没有其他情况呢.......

(root@127.0.0.1)[test]> show status like 'table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 1     |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

(root@127.0.0.1)[test]> select * from yhs;
+----+--------------------------------+------+
| id | val                            | test |
+----+--------------------------------+------+
|  1 | fdsa                           |    2 |
|  2 | fdsa                           |    1 |
|  3 | 范德萨范德萨范德萨热           |    2 |
+----+--------------------------------+------+
3 rows in set (0.00 sec)

(root@127.0.0.1)[test]> show status like 'table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 2     |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

PS:表内数据请忽略哈，随便敲的。。

select也会导致这个指标+1，因为select对表加了IS锁。

以上是Innodb下的情况，Myisam就不在赘述。

另外Table_locks_waited表示需要等待的表锁数，如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，

因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。

如果想了解行锁争夺情况，配合查看Innodb_row_lock_waits。