登录注册
最新博客
最新资源下载
Innodb实践总结（三）
把生命浪费在美好事物上2018-07-11 10:547.4 唯一索引
- session1: 第一个事务首先去根据phone（辅助索引的唯一索引）为123去执行当前读查询操作，并没有提交；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where phone=123 for update;
+----+------+-------+-------+
| id | age | phone | name |
+----+------+-------+-------+
| 8 | 12 | 123 | zhang |
+----+------+-------+-------+
1 row in set (0.00 sec)
- session2：第二个事务去删除phone为123的记录：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from user where phone=123;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- session3: 第三个事务去根据该记录的主键id为8去执行更新该记录的操作：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='zzz' where id=8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
分析
首先看innodb_trx表，查看具体事务情况，可以看到执行了三个事务，通过查看trx_query得知，581为根据唯一索引进行删除操作，582为根据主键索引来进行更新操作，这两个事务的trx_started都未LOCK WAIT状态，说明都给堵塞住了。
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 582
trx_state: LOCK WAIT
trx_started: 2017-11-12 04:35:59
trx_requested_lock_id: 582:0:327:13
trx_wait_started: 2017-11-12 04:35:59
trx_weight: 2
trx_mysql_thread_id: 65
trx_query: update user set name='zzz' where id=8
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
......
*************************** 2. row ***************************
trx_id: 581
trx_state: LOCK WAIT
trx_started: 2017-11-12 04:35:34
trx_requested_lock_id: 581:0:334:7
trx_wait_started: 2017-11-12 04:35:34
trx_weight: 2
trx_mysql_thread_id: 63
trx_query: delete from user where phone=123
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
......
*************************** 3. row ***************************
trx_id: 580
trx_state: RUNNING
trx_started: 2017-11-12 04:32:42
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 71
trx_query: NULL
trx_operation_state: NULL
......
3 rows in set (0.00 sec)
然后看innodb_lock_waits表，查看锁的等待情况，可以看出事务id为580持有的锁将581有582都给堵塞住了；
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 582
requested_lock_id: 582:0:327:13
blocking_trx_id: 580
blocking_lock_id: 580:0:327:13
*************************** 2. row ***************************
requesting_trx_id: 581
requested_lock_id: 581:0:334:7
blocking_trx_id: 580
blocking_lock_id: 580:0:334:7
2 rows in set (0.00 sec)
接下来看innodb_locks表，分析造成阻塞的具体锁的类型，可以看到580在唯一索引phone上加了X锁，所以将581给堵塞住了，于此同时580还在PRIMARY上加了排它锁，将582堵塞住了。
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 582:0:327:13
lock_trx_id: 582
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 327
lock_rec: 13
lock_data: 8
*************************** 2. row ***************************
lock_id: 580:0:327:13
lock_trx_id: 580
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 327
lock_rec: 13
lock_data: 8
*************************** 3. row ***************************
lock_id: 581:0:334:7
lock_trx_id: 581
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `phone`
lock_space: 0
lock_page: 334
lock_rec: 7
lock_data: 123
*************************** 4. row ***************************
lock_id: 580:0:334:7
lock_trx_id: 580
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `phone`
lock_space: 0
lock_page: 334
lock_rec: 7
lock_data: 123
4 rows in set (0.00 sec)
最后分析：唯一索引属于辅助索引，根据innodb的索引规则，使用辅助索引定位记录，需要使用两次索引：首先使用辅助索引找到主键的值，根据主键的值，使用主键索引找到记录。所以在加锁的时候，首先对唯一索引项加X锁，然后找到主键后，对主键索引项加了X锁。这样根据唯一索引或主键索引对该记录进行操作的时候都会被堵塞掉。
结论:唯一索引加两把锁，唯一索引项加X锁，对应的主键索引项加X锁。
7.5 非唯一索引
- session1: 第一个事务首先去根据age（辅助索引的非唯一索引）为12去执行当前读查询操作，并没有提交；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 12 for update ;
+----+------+-------+------+
| id | age | phone | name |
+----+------+-------+------+
| 8 | 12 | 123 | zzz |
+----+------+-------+------+
1 row in set (0.00 sec)
- session2：第二个事务去删除age为12的记录：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from user where age=12;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- session3: 第三个事务去根据该记录的唯一索引phone为123去执行删除该记录：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>delete from user where phone = 123;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- session4: 第四个事务去根据该记录的主键id为8去执行删除该记录的操作：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysqldelete from user where id = 8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- session5: 第五个事务插入一条age为10不存在的记录；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user values(4,10,111,'wang');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- session6：第六个事务插入一条age为13不存在的记录；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from user where age=12;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
分析
首先看innodb_trx表，查看具体事务情况，可以看到585事务将后面所有的事务都进行堵塞了。
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 58F
trx_state: LOCK WAIT
trx_started: 2017-11-12 05:27:14
trx_requested_lock_id: 58F:0:345:3
trx_wait_started: 2017-11-12 05:27:14
trx_weight: 3
trx_mysql_thread_id: 72
trx_query: insert into user values(9,13,133,'zhang')
trx_operation_state: inserting
......
*************************** 2. row ***************************
trx_id: 58E
trx_state: LOCK WAIT
trx_started: 2017-11-12 05:27:11
trx_requested_lock_id: 58E:0:345:4
trx_wait_started: 2017-11-12 05:27:11
trx_weight: 3
trx_mysql_thread_id: 73
trx_query: insert into user values(4,10,111,'wang')
trx_operation_state: inserting
......
*************************** 3. row ***************************
trx_id: 58D
trx_state: LOCK WAIT
trx_started: 2017-11-12 05:27:08
trx_requested_lock_id: 58D:0:327:13
trx_wait_started: 2017-11-12 05:27:08
trx_weight: 2
trx_mysql_thread_id: 74
trx_query: delete from user where id=8
trx_operation_state: starting index read
......
*************************** 4. row ***************************
trx_id: 588
trx_state: LOCK WAIT
trx_started: 2017-11-12 05:03:32
trx_requested_lock_id: 588:0:327:13
trx_wait_started: 2017-11-12 05:26:43
trx_weight: 3
trx_mysql_thread_id: 65
trx_query: delete from user where phone = 123
trx_operation_state: starting index read
......
*************************** 5. row ***************************
trx_id: 586
trx_state: LOCK WAIT
trx_started: 2017-11-12 05:03:03
trx_requested_lock_id: 586:0:345:4
trx_wait_started: 2017-11-12 05:26:40
trx_weight: 2
trx_mysql_thread_id: 63
trx_query: delete from user where age=12
trx_operation_state: starting index read
......
*************************** 6. row ***************************
trx_id: 585
trx_state: RUNNING
trx_started: 2017-11-12 05:02:37
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 71
trx_query: NULL
......
6 rows in set (0.00 sec)
然后看innodb_lock_waits表，查看锁的等待情况，首先是585堵塞了所有的锁，于此同时，588是根据辅助唯一索引删除该记录，该操作是要获取主键锁，所以该操作同时也堵塞了58D，根据主键id删除该记录。还有就是58E插入的操作中的age与非唯一索引的age相同，而586操作是根据age来进行记录删除操作，而586先执行，要获得age的X锁，而58E要获得X与GAP锁，所以586也堵塞了58E。
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 58F
requested_lock_id: 58F:0:345:3
blocking_trx_id: 585
blocking_lock_id: 585:0:345:3
*************************** 2. row ***************************
requesting_trx_id: 58E
requested_lock_id: 58E:0:345:4
blocking_trx_id: 586
blocking_lock_id: 586:0:345:4
*************************** 3. row ***************************
requesting_trx_id: 58E
requested_lock_id: 58E:0:345:4
blocking_trx_id: 585
blocking_lock_id: 585:0:345:4
*************************** 4. row ***************************
requesting_trx_id: 58D
requested_lock_id: 58D:0:327:13
blocking_trx_id: 588
blocking_lock_id: 588:0:327:13
*************************** 5. row ***************************
requesting_trx_id: 58D
requested_lock_id: 58D:0:327:13
blocking_trx_id: 585
blocking_lock_id: 585:0:327:13
*************************** 6. row ***************************
requesting_trx_id: 588
requested_lock_id: 588:0:327:13
blocking_trx_id: 585
blocking_lock_id: 585:0:327:13
*************************** 7. row ***************************
requesting_trx_id: 586
requested_lock_id: 586:0:345:4
blocking_trx_id: 585
blocking_lock_id: 585:0:345:4
7 rows in set (0.00 sec)
接下来看innodb_locks表，分析造成阻塞的具体锁的类型，分析可知，age进行了非唯一索引加了X锁，其对应的主键索引加了X锁，同时其前后的两个间隙都分别加了间隙锁。
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 58F:0:345:3
lock_trx_id: 58F
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `idx_k`
lock_space: 0
lock_page: 345
lock_rec: 3
lock_data: 15, 14
*************************** 2. row ***************************
lock_id: 585:0:345:3
lock_trx_id: 585
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `idx_k`
lock_space: 0
lock_page: 345
lock_rec: 3
lock_data: 15, 14
*************************** 3. row ***************************
lock_id: 58E:0:345:4
lock_trx_id: 58E
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `idx_k`
lock_space: 0
lock_page: 345
lock_rec: 4
lock_data: 12, 8
*************************** 4. row ***************************
lock_id: 586:0:345:4
lock_trx_id: 586
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `idx_k`
lock_space: 0
lock_page: 345
lock_rec: 4
lock_data: 12, 8
*************************** 5. row ***************************
lock_id: 585:0:345:4
lock_trx_id: 585
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `idx_k`
lock_space: 0
lock_page: 345
lock_rec: 4
lock_data: 12, 8
*************************** 6. row ***************************
lock_id: 58D:0:327:13
lock_trx_id: 58D
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 327
lock_rec: 13
lock_data: 8
*************************** 7. row ***************************
lock_id: 588:0:327:13
lock_trx_id: 588
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 327
lock_rec: 13
lock_data: 8
*************************** 8. row ***************************
lock_id: 585:0:327:13
lock_trx_id: 585
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 327
lock_rec: 13
lock_data: 8
8 rows in set (0.00 sec)
结论:非唯一索引加三把锁，非唯一索引项加X锁，对应的主键索引项加X锁，其前后的间隙都加了间隙锁。如果表中存在辅助索引的唯一索，那么该唯一索引项没有加锁，要根据其进行操作，需要获的主键索引对应的X锁。
7.6 索引间隙
以主键间隙为例：
- session1: 第一个事务根据主键id为4（该主键不存在）去执行当前读查询操作，并没有提交；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='www' where id=4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
- session2：第二个事务在id为4所在的间隙中插入数据：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user values(5,6,111,'www');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- session3: 第三个事务在id为4所在间隙外的其他间隙插入数据：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into user values(15,22,23423,'zhang');
Query OK, 1 row affected (0.00 sec)
可以看到第二个事务出现锁等待现象，而第三个事务插入成功。通过innodb_locks表可以发现，事务1加了间隙锁与与排它锁，而事务二是在该间隙插入数据，根据间隙锁的特性会出现锁等待现象。
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 592:0:327:13
lock_trx_id: 592
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 327
lock_rec: 13
lock_data: 8
*************************** 2. row ***************************
lock_id: 591:0:327:13
lock_trx_id: 591
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`user`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 327
lock_rec: 13
lock_data: 8
2 rows in set (0.00 sec)
辅助索引的唯一索引与非唯一索引与主键相同，当对其间隙进行当前读操作时，会对该间隙加间隙锁与排它锁，并不对其他记录或间隙加锁。
7.7 总结
主键索引：记录存在，对主键索引项加记录锁、排它锁；记录不存在，对其所在间隙加间隙锁，操作行加记录锁、排它锁。
唯一索引：记录存在，对唯一索引项、主键索引项加记录锁、排它锁；对其所在间隙加间隙锁，操作行加记录锁、排它锁。
非唯一索引：记录存在，对非唯一索引项、主键索引项加记录锁排它锁、对非唯一索引项的前后间隙加间隙锁；对其所在间隙加间隙锁，操作行加记录锁、排它锁。
非索引记录：对表加锁，表锁包括对表中所有记录加排它锁，同时对所有记录的间隙加间隙锁。、
这里对非唯一索引注意：非唯一索引的前后间隙的范围age为12进行解释：
mysql> select * from user;
+----+------+-------+-------+
| id | age | phone | name |
+----+------+-------+-------+
| 3 | 10 | 122 | wang |
| 8 | 12 | 123 | zhang |
| 14 | 15 | 128 | zhang |
+----+------+-------+-------+
操作 age=12的记录阻塞了age=11、13、14记录的插入操作，事实上，除了对于age=12这条记录上record lock之外，innoDB对于delete和update在辅助索引(非主键索引)上的条件时会对扫过的记录上间隙锁，为了防止幻读，会锁住age=12这条记录的前面一条记录（id=3,age=10）到后面一条记录(id=14,age=15)之间的区间，即锁住age在区间(10,15)的范围(如果没有后一条记录，一直锁到正无穷)，至于在边界age=10及age=15上，由于索引内是按照主键排序的，不会锁住(id<3,age=10)但是会锁住(id>3,age=10)，同理不会锁住(id>14,age=15)但是会锁住(id<14,age=15).
相关阅读：
本文来自网易实践者社区，经作者王志泳授权发布。
分享至：
