Innodb的行级锁及何时表锁

 

大家都知道Innodb采用的是行级锁机制，因此，很多人在编写程序时往往会忽视它的表锁，从而导致系统性能低下。要不掉进Innodb行级锁的陷阱，只需简单记住“Innodb行级锁只对Where条件为主键时有效，其他非主键时全都为表锁”即可。为了更好的感知这一过程，你也可以按照下面测试步骤测试下。

 

 

测试数据：

 

CREATE TABLE `t_user` (                                                                                  

`id` int(11) NOT NULL AUTO_INCREMENT,                                                                  

`name` varchar(30) DEFAULT NULL,                                                                       

`email` varchar(100) DEFAULT NULL,                                                                     

`role` int(11) DEFAULT NULL,                                                                           

PRIMARY KEY (`id`)                                                                                     

)ENGINE=InnoDB;

 

+----+--------+------------------+------+

| id | name   | email            | role |

+----+--------+------------------+------+

|  1 | zhangs | zhangs@gmial.com |    1 | 

|  2 | lis    | lis@gmail.com    |    1 | 

|  3 | wange  | wange@gmail.com  |    2 | 

|  4 | zhaoq  | zhaoq@gmail.com  |    1 | 

|  5 | sunz   | sunz@gmail.com   |    2 | 

+----+--------+------------------+------+

 

 

测试过程：

 

linux中登录三个mysql客户端，分别为session1、session2、session3，前两个用于交替输入SQL语句观察锁的存在，后一个用于查看锁时mysql的相关参数。

 

0、session1和session2中关闭自动提交

mysql> set autocommit = off;

 

一、条件为主键：

 

同记录的修改：

 

1、session1——修改主键值为1的记录

mysql> update t_user set email=replace(email, '@', '#') where id=1;

 

2、session2——修改主键值同样为1的记录

A、mysql> update t_user set email=replace(email, '#', '@') where id=1;

输入该命令回车后，将进入等待的状态。

 

B、通过session3查看：

mysql> show status like '%lock%';

+-------------------------------+-----------+

| Variable_name                 | Value     |

+-------------------------------+-----------+

| Com_lock_tables               | 0         | 

| Com_unlock_tables             | 0         | 

| Innodb_row_lock_current_waits | 1         | 

| Innodb_row_lock_time          | 1283052   | 

| Innodb_row_lock_time_avg      | 1         | 

| Innodb_row_lock_time_max      | 51645     | 

| Innodb_row_lock_waits         | 645438    | 

| Key_blocks_not_flushed        | 2         | 

| Key_blocks_unused             | 7244      | 

| Key_blocks_used               | 8         | 

| Qcache_free_blocks            | 0         | 

| Qcache_total_blocks           | 0         | 

| Table_locks_immediate         | 136234099 | 

| Table_locks_waited            | 6645      | 

+-------------------------------+-----------+

 

C、在session1中输入提交命令。

mysql> commit;

输入commit回车后，可以看到session2中的等待结束，并打印相关提示信息，如下：

Query OK, 0 rows affected (27.04 sec)

Rows matched: 1  Changed: 0  Warnings: 0

 

从A、B、C三个步骤不难看出，主键值为1的记录处于编辑状态时，其他用户想修改该记录需要排队等待。

 

修改不同记录：

 

1、session1——修改主键值为1的记录

mysql> update t_user set email=replace(email, '@', '#') where id=1;

 

2、session2——修改主键值为2的记录

A、mysql> update t_user set email=replace(email, '#', '@') where id=2;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

未出现等待，直接显示提示信息和输入提示符。

 

B、mysql> commit;

提交后，数据被修改。

 

C、在session1中输入提交命令。

mysql> commit;

 

D、session3中查看：

mysql> select * from t_user;

+----+--------+------------------+------+

| id | name   | email            | role |

+----+--------+------------------+------+

|  1 | zhangs | zhangs#gmial.com |    1 | 

|  2 | lis    | lis#gmail.com    |    1 | 

|  3 | wange  | wange@gmail.com  |    2 | 

|  4 | zhaoq  | zhaoq@gmail.com  |    1 | 

|  5 | sunz   | sunz@gmail.com   |    2 | 

+----+--------+------------------+------+

 

 

二、条件为非主键

 

1、session1——修改role为1的记录

mysql> update t_user set email=replace(email, '@', '#') where role=1;

 

2、session2

mysql> update t_user set email=replace(email, '#', '@') where role=2;

或

mysql> update t_user set email=replace(email, '#', '@') where id=3;

 

当session1中以非主键为条件编辑记录时，不管执行上面的哪一条都会进入等待状态。由此可见，在条件为非主键时，innodb用的是全表锁。

 

你也可以用以下命令，查看innodb锁时的相关数据

mysql> show innodb status\G;