InnoDB行锁和表锁的分析

  1. InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。 
  2. InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁! 
  3. 在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。
  4. 下面通过一些实际例子来加以说明。 
  5. (1)在不通过索引条件查询的时候,InnoDB使用的是表锁,而不是行锁 

在如表20-9所示的例子中,开始tab_no_index表没有索引:
表20-9         InnoDB存储引擎的表在不使用索引时使用表锁例子
  1. session_1     
  2. mysql> set autocommit=0;  
  3. Query OK, 0 rows affected (0.00 sec)  
  4. mysql> select * from tab_no_index where id = 1 ;  
  5. +------+------+  
  6. | id   | name |  
  7. +------+------+  
  8. | 1    | 1    |  
  9. +------+------+  
  10. 1 row in set (0.00 sec)  
  11.   
  12. session_2  
  13. mysql> set autocommit=0;  
  14. Query OK, 0 rows affected (0.00 sec)  
  15. mysql> select * from tab_no_index where id = 2 ;  
  16. +------+------+  
  17. | id   | name |  
  18. +------+------+  
  19. | 2    | 2    |  
  20. +------+------+  
  21. 1 row in set (0.00 sec)  
  22.  
  23. session_1:   
  24. mysql> select * from tab_no_index where id = 1 for update;   
  25. +------+------+   
  26. | id   | name |   
  27. +------+------+   
  28. | 1    | 1    |   
  29. +------+------+   
  30. 1   
  31. session_2:  
  32. mysql> select * from tab_no_index where id=2 for update;  
  33. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  
在如表20-9所示的例子中,看起来session_1只给一行加了排他锁,但session_2在请求其他行的排他锁时,却出现了锁等待!原因就是在没有索引的情况下,InnoDB只能使用表锁。当我们给其增加一个索引后,InnoDB就只锁定了符合条件的行,如表20-10所示。
创建tab_with_index表,id字段有普通索引:
  1. mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb
  2. Query OK, 0 rows affected (0.15 sec) 
  3. mysql> alter table tab_with_index add index id(id); 
  4. Query OK, 4 rows affected (0.24 sec) 
  5. Records: 4  Duplicates: 0  Warnings: 0 
表20-10    InnoDB存储引擎的表在使用索引时使用行锁例子

 

  1. session_1    
  2. mysql> set autocommit=0
  3. Query OK, 0 rows affected (0.00 sec) 
  4. mysql> select * from tab_with_index where id = 1 ; 
  5. +------+------+ 
  6. | id   | name | 
  7. +------+------+ 
  8. | 1    | 1    | 
  9. +------+------+ 
  10. 1 row in set (0.00 sec) 
  11. session_2 
  12. mysql> set autocommit=0
  13. Query OK, 0 rows affected (0.00 sec) 
  14. mysql> select * from tab_with_index where id = 2 ; 
  15. +------+------+ 
  16. | id   | name | 
  17. +------+------+ 
  18. | 2    | 2    | 
  19. +------+------+ 
  20. 1 row in set (0.00 sec) 
  21. session_1 
  22. mysql> select * from tab_with_index where id = 1 for update; 
  23. +------+------+ 
  24. | id   | name | 
  25. +------+------+ 
  26. | 1    | 1    | 
  27. +------+------+ 
  28. 1 row in set (0.00 sec) 
  29.  
  30. session_2    
  31. mysql> select * from tab_with_index where id = 2 for update; 
  32. +------+------+ 
  33. | id   | name | 
  34. +------+------+ 
  35. | 2    | 2    | 
  36. +------+------+ 
  37. 1 row in set (0.00 sec) 
 
(2)由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。
在如表20-11所示的例子中,表tab_with_index的id字段有索引,name字段没有索引:

 

  1. mysql> alter table tab_with_index drop index name; 
  2. Query OK, 4 rows affected (0.22 sec) 
  3. Records: 4  Duplicates: 0  Warnings: 0 
  4. mysql> insert into tab_with_index  values(1,'4'); 
  5. Query OK, 1 row affected (0.00 sec) 
  6. mysql> select * from tab_with_index where id = 1
  7. +------+------+ 
  8. | id   | name | 
  9. +------+------+ 
  10. | 1    | 1    | 
  11. | 1    | 4    | 
  12. +------+------+ 
  13. 2 rows in set (0.00 sec) 
表20-11    InnoDB存储引擎使用相同索引键的阻塞例子
  1. session_1    
  2. mysql> set autocommit=0
  3. Query OK, 0 rows affected (0.00 sec) 
  4. session_2 
  5. mysql> set autocommit=0
  6. Query OK, 0 rows affected (0.00 sec) 
  7. session_1    
  8. mysql> select * from tab_with_index where id = 1 and name = '1' for update; 
  9. +------+------+ 
  10. | id   | name | 
  11. +------+------+ 
  12. | 1    | 1    | 
  13. +------+------+ 
  14. 1 row in set (0.00 sec) 
  15. 虽然session_2访问的是和session_1不同的记录,但是因为使用了相同的索引,所以需要等待锁: 
  16. mysql> select * from tab_with_index where id = 1 and name = '4' for update; 
  17. 等待 
(3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
在如表20-12所示的例子中,表tab_with_index的id字段有主键索引,name字段有普通索引:

 

  1. mysql> alter table tab_with_index add index name(name); 
  2. Query OK, 5 rows affected (0.23 sec) 
  3. Records: 5  Duplicates: 0  Warnings: 0 
表20-12    InnoDB存储引擎的表使用不同索引的阻塞例子

 

  1.  session_1  ·           
  2. mysql> set autocommit=0
  3. Query OK, 0 rows affected (0.00 sec) 
  4. session_2 
  5. mysql> set autocommit=0
  6. Query OK, 0 rows affected (0.00 sec) 
  7.  session_1 
  8. mysql> select * from tab_with_index where id = 1 for update; 
  9. +------+------+ 
  10. | id   | name | 
  11. +------+------+ 
  12. | 1    | 1    | 
  13. | 1    | 4    | 
  14. +------+------+ 
  15. 2 rows in set (0.00 sec) 
  16.   
  17.      
  18. Session_2使用name的索引访问记录,因为记录没有被索引,所以可以获得锁: 
  19. mysql> select * from tab_with_index where name = '2' for update; 
  20. +------+------+ 
  21. | id   | name | 
  22. +------+------+ 
  23. | 2    | 2    | 
  24. +------+------+ 
  25. 1 row in set (0.00 sec) 
  26.      
  27. 由于访问的记录已经被session_1锁定,所以等待获得锁。: 
  28. mysql> select * from tab_with_index where name = '4' for update; 
 
(4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。关于MySQL在什么情况下不使用索引的详细讨论,参见本章“索引问题”一节的介绍。
在下面的例子中,检索值的数据类型与索引字段不同,虽然MySQL能够进行数据类型转换,但却不会使用索引,从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划,我们可以清楚地看到了这一点。
例子中tab_with_index表的name字段有索引,但是name字段是varchar类型的,如果where条件中不是和varchar类型进行比较,则会对name进行类型转换,而执行的全表扫描。
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key 锁)。
举例来说,假如emp表中只有101条记录,其empid的值分别是 1,2,...,100,101,下面的SQL:

 

  1. Select * from  emp where empid > 100 for update; 
是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。
InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其他事务插入了empid大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;另外一方面,是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响,以及不同隔离级别下InnoDB使用间隙锁的情况,在后续的章节中会做进一步介绍。
很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。
还要特别说明的是,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁!
在如表20-13所示的例子中,假如emp表中只有101条记录,其empid的值分别是1,2,......,100,101。
表20-13    InnoDB存储引擎的间隙锁阻塞例子
session_1 session_2
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
 
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
 
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
当前session对不存在的记录加for update的锁:
mysql> select * from emp where empid = 102 for update;
Empty set (0.00 sec)
 
 
这时,如果其他session插入empid为201的记录(注意:这条记录并不存在),也会出现锁等待:
mysql>insert into emp(empid,...) values(201,...);
阻塞等待
Session_1 执行rollback:
mysql> rollback;
Query OK, 0 rows affected (13.04 sec)
 
 
由于其他session_1回退后释放了Next-Key锁,当前session可以获得锁并成功插入记录:
mysql>insert into emp(empid,...) values(201,...);
Query OK, 1 row affected (13.35 sec)

注:关于Innodb什么情况下使用行锁,什么情况使用表锁,上面的例子介绍的通俗易通,适合初级DBA学习。原来出自http://brilon.iteye.com/blog/433726,欢迎交流沟通,谢谢!