一个InnoDB 加锁的案例


最近一直在优化各个产品的SQL语句,同时还帮一个同事解决deadlock问题,收获就是对InnoDB加锁的理解更加深入了。先来看看今天的这个案例:

  1. mysql> select version();
  2. +----------------------+
  3. | version() |
  4. +----------------------+
  5. | 5.5.13.4-log |
  6. +----------------------+
  7. mysql> show variables like'%iso%';
  8. +---------------+-----------------+
  9. | Variable_name | Value |
  10. +---------------+-----------------+
  11. | tx_isolation | REPEATABLE-READ |
  12. +---------------+-----------------+
  13. CREATETABLE `t1` (
  14. `a` int(11) NOTNULL,
  15. `b` int(11) DEFAULTNULL,
  16. `c` int(11) DEFAULTNULL,
  17. PRIMARYKEY (`a`)
  18. ) ENGINE=InnoDB;
  19. mysql> select * from t1;
  20. +----+------+------+
  21. | a | b | c |
  22. +----+------+------+
  23. | 1 | 1 | 1 |
  24. | 2 | 1 | 1 |
  25. | 3 | 1 | 1 |
  26. | 10 | 1 | 1 |
  27. | 11 | 1 | 1 |
  28. | 12 | 1 | 1 |
  29. | 14 | 1 | 1 |
  30. | 15 | 1 | 1 |
  31. +----+------+------+

测试环境就是如上,开始下面的测试:

  1. session1:
  2. mysql> begin;
  3. Query OK, 0 rows affected (0.00 sec)
  4. mysql> select * from t1 where a in (2, 10, 11, 12,14) forupdate;
  5. +----+------+------+
  6. | a | b | c |
  7. +----+------+------+
  8. | 2 | 1 | 1 |
  9. | 10 | 1 | 1 |
  10. | 11 | 1 | 1 |
  11. | 12 | 1 | 1 |
  12. | 14 | 1 | 1 |
  13. +----+------+------+
  14. 5 rowsinset (0.00 sec)

 

  1. session2:
  2. mysql> insertinto t1 values(7, 1, 1);
  3. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  4. mysql> select * from t1 where a=15 forupdate;
  5. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  6. mysql> insertinto t1 values(18,1,1);
  7. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  8. mysql> insertinto t1 values(18000,1,1);
  9. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

从上面可以看出InnoDB仿佛是将[2, 无穷大)这个区间给锁了,那么原因是什么呢? 你可以先思考下

  • 1
  • 2
  • 下一页

相关内容