mysql 实验论证 innodb表级锁与行级锁

左手的ㄟ右手 2022-06-15 23:58 233阅读 0赞

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

表锁演示（无索引）

Session1:

mysql> set autocommit=0;

mysql> select \* from innodb\_test;  
\+------+-------------+  
| id   | name        |  
\+------+-------------+  
|    1 | woshiceshi  |   
|    2 | woshiceshi2 |   
|    3 | woshiceshi3 |   
\+------+-------------+

mysql> select \* from innodb\_test where id = 2 for update;  
\+------+------------+  
| id   | name       |  
\+------+------------+  
|    2 | woshiceshi2 |   
\+------+------------+

Session2:

mysql> update innodb\_test set name='sjis' where id = 1 ;  
处于等待状态....

再回到session1 commit以后，session2就出来结果了（锁定了8秒，过了8秒左右才去session1提交）。

mysql> update innodb\_test set name='sjis' where id = 1 ;  
Query OK, 1 row affected (8.11 sec)  
Rows matched: 1  Changed: 1  Warnings: 0

实验结果是：我在session1的for update 操作看似只锁定ID为2的行其实锁定了全表，以至于后面session2的对ID为1的行update 需要等待Session1锁的释放。

行锁演示（索引为ID）

Session1:  
mysql> alter table innodb\_test add index idx\_id(id);  
Query OK, 4 rows affected (0.01 sec)  
Records: 4  Duplicates: 0  Warnings: 0  
  
mysql> select \* from innodb\_test where id = 2 for update;  
\+------+------------+  
| id   | name       |  
\+------+------------+  
|    2 | woshiceshi2 |   
\+------+------------+

Session2:

mysql> update innodb\_test set name='wohaishiceshi' where id = 1 ;  
Query OK, 1 row affected (0.02 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
mysql> select \* from innodb\_test where id = 1;             
\+------+---------------+  
| id   | name          |  
\+------+---------------+  
|    1 | wohaishiceshi |   
\+------+---------------+  
1 row in set (0.00 sec)

实验结果:这次的锁定是锁定的行，所以没有被锁定的行（ID不为2的行）可以进行update..