MYSQL的伪行级锁_MySQL

bitsCN.com
MYSQL的伪行级锁 之前一直以为mysql的innodb引擎所支持的行级锁和oracle，postgresql是一样的，是对数据行上加锁。但其实是不一样的，理解不一样，对mysql的锁机制就容易产生误解。innodb的行级锁实际上是基于索引项来锁定的。以下是验证测试过程  一.数据准备mysql> use test;Database changedmysql> show create table t_kenyon /G*************************** 1. row ***************************       Table: t_kenyonCreate Table: CREATE TABLE `t_kenyon` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)   mysql> show variables like '%autocommit%';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | OFF   |+---------------+-------+1 row in set (0.00 sec) mysql> select * from t_kenyon;+------+| id   |+------+|    1 ||  123 ||  789 ||  345 ||   78 ||   78 |+------+6 rows in set (0.00 sec)以上是测试表t_kenyon,设置提交方式为手动提交.  二.过程(开启两个session,分别设置autocommit=off)    1.session one updatemysql> update t_kenyon set id = 999 where id = 1;Query OK, 1 row affected (0.04 sec)Rows matched: 1  Changed: 1  Warnings: 0 mysql> select * from t_kenyon;+------+| id   |+------+|  999 ||  123 ||  789 ||  345 ||   78 ||   78 |+------+6 rows in set (0.00 sec)2.session two updatemysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | OFF   |+---------------+-------+1 row in set (0.00 sec)   mysql> select * from t_kenyon;+------+| id   |+------+|    1 ||  123 ||  789 ||  345 ||   78 ||   78 |+------+6 rows in set (0.00 sec) mysql> update t_kenyon set id = 88888 where id = 345;第二个session更新的值是345，但是也一直被阻塞，直到session1被rollback或者commit,如果session1未做回滚或者提交，session2中的该阻塞在超出mysql的锁时间限制时自动回滚,该参数为innodb_lock_wait_timeout，默认值50秒 现象如下ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction加索引后的测试 3.session one update mysql> create index ind_kenyon on t_kenyon(id);Query OK, 0 rows affected (28.58 sec)Records: 0  Duplicates: 0  Warnings: 0   mysql> update t_kenyon set id = 999 where id = 1;Query OK, 1 row affected (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 0 mysql> select * from t_kenyon;+------+| id   |+------+|   78 ||   78 ||  123 ||  345 ||  789 ||  999 |+------+6 rows in set (0.00 sec)4.session two update mysql> select * from t_kenyon;+------+| id   |+------+|    1 ||   78 ||   78 ||  123 ||  345 ||  789 |+------+6 rows in set (0.00 sec)   mysql> update t_kenyon set id = 7777 where id = 345;Query OK, 1 row affected (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 0 mysql> select * from t_kenyon;+------+| id   |+------+|    1 ||   78 ||   78 ||  123 ||  789 || 7777 |+------+6 rows in set (0.00 sec)执行计划mysql> explain select * from t_kenyon where id = 345 /G*************************** 1. row ***************************           id: 1      select_type: SIMPLE        table: t_kenyon         type: refpossible_keys: ind_kenyon          key: ind_kenyon      key_len: 5          ref: const         rows: 1        Extra: Using where; Using index1 row in set (0.00 sec)可以看到加了索引后，不同的数据更新并没有被阻塞，实现了真正意义上行锁 三.行级锁的扩展限制 参考：http:///database/201208/145888.html  作者 kenyon bitsCN.com