公司一台服务器原来表的存储引擎是以myisam为主,上周我把表类型都修改为innodb,并且按照innodb修改了些my.cnf的配置,然后重启了服务器,但是今天我看服务器状态时候发现,关于innodb的行级锁的参数项都为0,使用show global status like '%innodb_row_lock_%'查看的,输入如下:
mysql> show global status like '%innodb_row_lock_%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)按说运行了一周,肯定会出现行级锁的情况的啊,难道是myisam修改到innodb时候,使用的还是表级锁么?
查看所有锁的情况如下:
mysql> show global status like '%lock_%';
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| Com_lock_tables | 16 |
| Com_unlock_tables | 25 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Key_blocks_not_flushed | 204 |
| Key_blocks_unused | 55731 |
| Key_blocks_used | 2259 |
| Qcache_free_blocks | 13 |
| Qcache_total_blocks | 1091 |
| Table_locks_immediate | 1870449 |
| Table_locks_waited | 60277 |
+-------------------------------+---------+
14 rows in set (0.00 sec)
看到大量的表级锁和表级锁等待。先不说锁等待那么多的原因,我就想了解下为什么,使用的表都修改为innodb后行级锁的状态参数在运行一周后却都为0,而表级锁的状态数值却这么大。请高手帮我分析下的原因吧,表的索引情况基本都有自增长的主键。
mysql> show global status like '%innodb_row_lock_%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)按说运行了一周,肯定会出现行级锁的情况的啊,难道是myisam修改到innodb时候,使用的还是表级锁么?
查看所有锁的情况如下:
mysql> show global status like '%lock_%';
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| Com_lock_tables | 16 |
| Com_unlock_tables | 25 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Key_blocks_not_flushed | 204 |
| Key_blocks_unused | 55731 |
| Key_blocks_used | 2259 |
| Qcache_free_blocks | 13 |
| Qcache_total_blocks | 1091 |
| Table_locks_immediate | 1870449 |
| Table_locks_waited | 60277 |
+-------------------------------+---------+
14 rows in set (0.00 sec)
看到大量的表级锁和表级锁等待。先不说锁等待那么多的原因,我就想了解下为什么,使用的表都修改为innodb后行级锁的状态参数在运行一周后却都为0,而表级锁的状态数值却这么大。请高手帮我分析下的原因吧,表的索引情况基本都有自增长的主键。
2 开事务 // 你的程序是否使用了事务,比如是否auto commit
3 SELECT * FROM test_table WHERE id = ... FOR UPDATE
4 更新数据,并commit
5 检查status