一个gap lock引发的惨案（结帖）

justin033

最近在研究innodb index的实现形式！

如下测试：

测试1：插入的字段记录没gap的并发情况


session一，id=2：

1. 
   
2. root@localhost[test] 10:04:35>create table tbl_no_index(i int) engine=innodb;
   
3. Query OK, 0 rows affected (0.05 sec)
   
4. 
   
5. root@localhost[test] 10:05:19>insert into tbl_no_index values(1),(2),(3),(4);
   
6. Query OK, 4 rows affected (0.00 sec)
   
7. Records: 4  Duplicates: 0  Warnings: 0
   
8. 
   
9. root@localhost[test] 10:07:07>set autocommit=off;
   
10. Query OK, 0 rows affected (0.00 sec)
    
11. 
    
12. root@localhost[test] 10:07:19>select * from tbl_no_index where i=1;
    
13. +------+
    
14. | i    |
    
15. +------+
    
16. |    1 |
    
17. +------+
    
18. 1 row in set (0.00 sec)
    
19. 
    
20. root@localhost[test] 10:07:23>select * from tbl_no_index where i=1[color=Red] for update;[/color]
    
21. +------+
    
22. | i    |
    
23. +------+
    
24. |    1 |
    
25. +------+
    
26. 1 row in set (0.00 sec)
    
27. 
    
28. root@localhost[test] 10:07:26>status
    
29. --------------
    
30. mysql  Ver 14.14 Distrib 5.1.39, for Win32 (ia32)
    
31. 
    
32. Connection id:          2
    
33. SSL:                    Not in use
    
34. Using delimiter:        ;
    
35. Server version:         [color=Red]5.1.39-community-log MySQL Community Server (GPL)[/color]
    
36. Protocol version:       10
    
37. Connection:             localhost via TCP/IP
    
38. Client characterset:    gbk
    
39. Server characterset:    gbk
    
40. TCP port:               3306
    
41. Uptime:                 11 min 24 sec
    

复制代码

session 二，id=3：

1. 
   
2. C:\>mysql -uroot -p
   
3. Enter password: *
   
4. Welcome to the MySQL monitor.  Commands end with ; or \g.
   
5. Your MySQL connection id is 3
   
6. Server version: 5.1.39-community-log MySQL Community Server (GPL)
   
7. 
   
8. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
9. 
   
10. root@localhost[(none)] 10:05:52>show processlist\G
    
11. *************************** 1. row ***************************
    
12.      Id: 2
    
13.    User: root
    
14.    Host: localhost:1948
    
15.      db: test
    
16. Command: Sleep
    
17.    Time: 13
    
18.   State:
    
19.    Info: NULL
    
20. *************************** 2. row ***************************
    
21.      Id: 3
    
22.    User: root
    
23.    Host: localhost:1962
    
24.      db: NULL
    
25. Command: Query
    
26.    Time: 0
    
27.   State: NULL
    
28.    Info: show processlist
    
29. 2 rows in set (0.03 sec)
    
30. 
    
31. root@localhost[(none)] 10:05:58>use test;
    
32. Database changed
    
33. 
    
34. root@localhost[test] 10:21:48>set @@autocommit=0;
    
35. Query OK, 0 rows affected (0.00 sec)
    
36. 
    
37. root@localhost[test] 10:22:06>select * from tbl_no_index where i=4 for update;
    
38. [color=Red]ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction[/color]
    
39. root@localhost[test] 10:23:00>
    

复制代码

按道理说，innodb是用index lock/record lock，只会锁定要查询的记录。为什么会把session二，id=3给锁定了，就像加了table lock。而在show engine innodb status下也是显示record lock的，如下：

1. 
   
2. ------------
   
3. TRANSACTIONS
   
4. ------------
   
5. Trx id counter 0 3854
   
6. Purge done for trx's n:o < 0 3336 undo n:o < 0 0
   
7. History list length 7
   
8. LIST OF TRANSACTIONS FOR EACH SESSION:
   
9. ---TRANSACTION 0 0, not started, OS thread id 1760
   
10. MySQL thread id 4, query id 52 localhost 127.0.0.1 root
    
11. show engine innodb status
    
12. ---TRANSACTION 0 3853, ACTIVE 28 sec, OS thread id 3428 starting index read
    
13. mysql tables in use 1, locked 1
    
14. LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
    
15. MySQL thread id 3, query id 51 localhost 127.0.0.1 root Sending data
    
16. select * from tbl_no_index where i=4 for update
    
17. ------- TRX HAS BEEN WAITING 28 SEC FOR THIS LOCK TO BE GRANTED:
    
18. [color=Red]RECORD LOCKS space id 0 page no 197 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`tbl_no_index` trx id 0 3853 lock_mode X waiting[/color]
    
19. Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    
20. 0: len 6; hex 000000000400; asc       ;; 1: len 6; hex 000000000f01; asc       ;; 2: len 7; hex 800000002d0110; asc     -  ;; 3: len 4; hex 80000001; asc     ;;
    
21. 
    
22. ------------------
    
23. ---TRANSACTION 0 3849, ACTIVE 914 sec, OS thread id 2868
    
24. 2 lock struct(s), heap size 320, 5 row lock(s)
    
25. MySQL thread id 2, query id 45 localhost 127.0.0.1 root
    
26. Trx read view will not see trx with id >= 0 3850, sees < 0 3850
    

复制代码

如下测试：

测试2：插入的字段记录有gap的并发情况


session一：

1. 
   
2. root@localhost[test] 10:32:21>set autocommit=off;
   
3. Query OK, 0 rows affected (0.00 sec)
   
4. 
   
5. root@localhost[test] 11:29:19>select * from tbl_no_index;
   
6. +------+
   
7. | i    |
   
8. +------+
   
9. |    1 |
   
10. |    2 |
    
11. |    7 |
    
12. |   11 |
    
13. |   12 |
    
14. +------+
    
15. 5 rows in set (0.00 sec)
    
16. 
    
17. root@localhost[test] 11:29:33>select * from tbl_no_index where i=2 [color=Red]for update[/color];
    
18. +------+
    
19. | i    |
    
20. +------+
    
21. |    2 |
    
22. +------+
    
23. 1 row in set (0.00 sec)
    

复制代码

session二：

1. 
   
2. root@localhost[test] 11:29:51>set autocommit=0;
   
3. Query OK, 0 rows affected (0.02 sec)
   
4. 
   
5. root@localhost[test] 11:29:55>[color=Red]insert into tbl_no_index values(5);[/color]
   
6. Query OK, 1 row affected (0.00 sec)
   
7. 
   
8. root@localhost[test] 11:30:03>select * from tbl_no_index;
   
9. +------+
   
10. | i    |
    
11. +------+
    
12. |    1 |
    
13. |    2 |
    
14. |    3 |
    
15. |    5 |
    
16. |    7 |
    
17. |   11 |
    
18. |   12 |
    
19. +------+
    
20. 7 rows in set (0.00 sec)
    

复制代码

从上面的测试来看，gap lock是record lock行锁的，也是文档上说的！但为什么测试一，感觉是加了table lock的锁。。。我还没找合理的解释了，也问了一些朋友了。目前还没理解清楚，帮忙哈，谢谢！

justin033

经过朋友的帮助，找到原因了！测试2的情况，我忽略了一个地方，原来有在字段i建了索引了，所以测试才能正常插入！如果删除字段i上的索引，情况就和测试1是一样的。另外，我还犯了一个错，一直以为没手工对表建索引，会走InnoDB默认系统自动生成的索引！这个错误就比较低级了，这个也是没有通过explain分析语句的结果，呵呵！


总结下：

1. InnoDB行锁实现特点意味着：只有通过有索引条件查询数据，InnoDB才使用行级锁；否则，InnoDB将扫描全表，使用表锁！
2. 如果对查询的结果有疑问，可以用explain来分析下查询语句的走向，就能清楚语句执行的前前后后。
3. 坚持技术，还是需要朋友间的多多交流，感谢他们！