最近在一次不经意的测试中发现了innodb的行锁定与ORACLE的实现方式有很大差异,下面来研究一下innodb的行锁定机制,对于支持行锁定的数据库,我们一般的经验是更新一行数据时只是锁定了更新行的数据,对于其他数据行是不锁定的,下面来做个测试:
创建测试表插入3条数据:
mysql> show create table d \G;
*************************** 1. row ***************************
Table: d
Create Table: CREATE TABLE `d` (
`id` int(11) ,
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from d;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
| 4 | d |
+----+------+
3 rows in set (0.00 sec)
session1 | session2 |
begin;
| |
mysql> begin; mysql> select * from d where name='c' for update; | |
mysql> update d set id=6 where name='a'; | |
既然innodb支持行锁定,那前面的测试就不应该出现阻塞,查询系统的锁定信息:
mysql> select * from INNODB_LOCKS \G;
*************************** 1. row ***************************
lock_id: 41A5A:0:1862:2
lock_trx_id: 41A5A
lock_mode: X
lock_type: RECORD
lock_table: `lry`.`d`
lock_index: `GEN_CLUST_INDEX`
lock_space: 0
lock_page: 1862
lock_rec: 2
lock_data: 0x000000000300
*************************** 2. row ***************************
lock_id: 41A59:0:1862:2
lock_trx_id: 41A59
lock_mode: X
lock_type: RECORD
lock_table: `lry`.`d`
lock_index: `GEN_CLUST_INDEX`
lock_space: 0
lock_page: 1862
lock_rec: 2
lock_data: 0x000000000300
2 rows in set (0.00 sec)
难道使用的是表锁,所以导致更新被阻塞,innodb是支持行锁定的,那为什么出现这样的情况呢,查阅了innodb的资料后发现innodb的行锁是依赖索引来实现的,也就是说name列必须要有索引才能使用行锁定,否则innodb会使用表锁,这个机制和ORACLE有很大差别,oracle的行锁定是依赖数据块中的transaction slot来实现的,下面我们给name 字段加上索引。
session1:
mysql> create index idx_name on d (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from d where name='c' for update;
+----+------+
| id | name |
+----+------+
| 3 | c |
+----+------+
1 row in set (0.02 sec)
session2:
mysql> update d set id=6 where name='a';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
可以看到添加索引后,session2不再阻塞,所以在使用innodb的时候一定要创建好相关的索引,避免因为缺少索引造成的锁升级影响系统性能。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7314177/viewspace-734477/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7314177/viewspace-734477/