查看了一些文章,对这个指标还是有些模糊,首先这个指标的定义是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。