Chinaunix

标题: 一个gap lock引发的惨案（结帖） [打印本页]
作者: justin033    时间: 2010-02-24 14:05
标题: 一个gap lock引发的惨案（结帖）
本帖最后由 justin033 于 2010-02-25 10:41 编辑

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

如下测试：

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


session一，id=2：

  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)

  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

  9. root@localhost[test] 10:07:07>set autocommit=off;
  10. Query OK, 0 rows affected (0.00 sec)

  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)

  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)

  28. root@localhost[test] 10:07:26>status
  29. --------------
  30. mysql  Ver 14.14 Distrib 5.1.39, for Win32 (ia32)

  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：

  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)

  8. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  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)

  31. root@localhost[(none)] 10:05:58>use test;
  32. Database changed

  34. root@localhost[test] 10:21:48>set @@autocommit=0;
  35. Query OK, 0 rows affected (0.00 sec)

  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的，如下：

  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     ;;

  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一：

  2. root@localhost[test] 10:32:21>set autocommit=off;
  3. Query OK, 0 rows affected (0.00 sec)

  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)

  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二：

  2. root@localhost[test] 11:29:51>set autocommit=0;
  3. Query OK, 0 rows affected (0.02 sec)

  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)

  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    时间: 2010-02-24 15:52
本帖最后由 justin033 于 2010-02-25 09:04 编辑

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


总结下：

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



欢迎光临 Chinaunix (http://bbs.chinaunix.net/) Powered by Discuz! X3.2