一、Innodb行锁的实现
【1】Innodb的行锁是通过给索引的索引项加锁来实现的
【2】Innodb按照辅助索引进行数据操作时,辅助索引和主键索引都将锁定指定的索引项
【3】通过索引进行数据检索时,Innodb才使用行级锁,否则Innodb将使用表锁
二、场景分析
环境： 创建一张表，ID为主键，Name为普通字段。下面通过实际场景进行说明。
【1】使用主键ID来进行数据检索时，其余行仍然可以操作。
| session1 | session2 |
|---|---|
| mysql>set autocommit=0; Query OK,0 rows affected(0.00sec) |
mysql>set autocommit=0; Query OK,0 rows affected(0.00sec) |
| mysql> select * from test_lock wehre id = 1 for update; ±-----±-----+ | id | name | | 1 | hua zi | 1 row in set(0.00sec) |
|
| mysql> select * from test_lock wehre id = 2 for update; ±-----±-----+ | id | name | | 2 | guo zi | 1 row in set(0.00sec) |
|
| 【2】用非索引的字段来进行数据检索时,此时会升级为表锁,其余列就不能操作。 | |
| session1 | session2 |
| ---- | ---- |
| mysql>set autocommit=0; Query OK,0 rows affected(0.00sec) |
mysql>set autocommit=0; Query OK,0 rows affected(0.00sec) |
| mysql> select * from test_lock wehre name = ‘hua zi’ for update; ±-----±-----+ | id | name | | 1 | hua zi | 1 row in set(0.00sec) |
|
| mysql> select * from test_lock wehre name = ‘guo zi’ for update; 等待 |
|
| 【3】由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。虽然 session_2和session_1访问的是不同的记录，但因为使用了相同的索引，所以需要等待锁。 | |
| session1 | session2 |
| ---- | ---- |
| mysql>set autocommit=0; Query OK,0 rows affected(0.00sec) |
mysql>set autocommit=0; Query OK,0 rows affected(0.00sec) |
| mysql> select * from test_lock wehre id = 1 and name = ‘hua zi’ for update; ±-----±-----+ | id | name | | 1 | hua zi | 1 row in set(0.00sec) |
|
| mysql> select * from test_lock wehre id = 1 and name = ‘guo zi’ for update; 等待 |
|
| 【4】当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。 | |
| session1 | session2 |
| ---- | ---- |
| mysql>set autocommit=0; Query OK,0 rows affected(0.00sec) |
mysql>set autocommit=0; Query OK,0 rows affected(0.00sec) |
| mysql> select * from test_lock wehre id = 1 for update; ±-----±-----+ | id | name | | 1 | hua zi | 1 row in set(0.00sec) |
|
| mysql> select * from test_lock wehre name = ‘guo zi’ for update; ±-----±-----+ | id | name | | 2 | guo zi | 1 row in set(0.00sec) |
|
| mysql> select * from test_lock wehre name = ‘hua zi’ for update; 等待 |
三、特殊场景
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
在下面的例子中，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> alter table test_lock add index name(name); Query OK, 4 rows affected (8.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain select * from test_lock where name = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_lock type: ALL possible_keys: name key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from test_lock where name = '1' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_lock type: ref possible_keys: name key: name key_len: 23 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) |
