MySQL的伪行级锁

接触mysql之前一直以为mysql的innodb引擎所支持的行级锁和oracle，postgresql是一样的，是对数据行上加锁。但其实是不一样的，理

接触mysql之前一直以为mysql的innodb引擎所支持的行级锁和Oracle，postgresql是一样的，是对数据行上加锁。但其实是不一样的，理解不一样，对mysql的锁机制就容易产生误解。innodb的行级锁实际上是基于索引项来锁定的。以下是测试机上的验证测试过程

一.数据准备

mysql> use test; Database changed mysql> show create table t_kenyon \G *************************** 1. row *************************** Table: t_kenyon Create Table: CREATE TABLE `t_kenyon` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 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> show variables like '%innodb_lock%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | +--------------------------------+-------+ 2 rows in set (0.00 sec) mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 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 1 update mysql> 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 2 update 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) 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 1 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 2 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: ref possible_keys: ind_kenyon key: ind_kenyon key_len: 5 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec)可以看到加了索引后，不同的数据更新并没有被阻塞，实现了真正意义上行锁
三.行级锁的扩展限制
1.相同索引阻塞
mysql> select * from t_kenyon; +------+---------+ | id | name | +------+---------+ | 1 | kenyon | | 123 | francs | | 789 | lighten | | 345 | mood | | 78 | opp | | 78 | opp | | 789 | james | +------+---------+ 7 rows in set (0.00 sec) mysql> explain select * from t_kenyon where id =1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_kenyon type: ref possible_keys: ind_kenyon key: ind_kenyon key_len: 5 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) mysql> update t_kenyon set name = 'john' where id = 789 and name = 'james'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 02.在另外的会话中同样对id=789的另一条数据进行更新 mysql> update t_kenyon set name = 'yagobu' where id = 789 and name ='lighten'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update t_kenyon set name = 'yagobu' where id = 789 and name ='xxxxx'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction这时我们可以看到即使有索引，不同数据的更新也被阻塞了,哪怕没有检索到其他数据，只要用到了同一个索引键值都会被阻塞,如上述name='xxxx'中是没有的，也被阻塞了。 所以可以引申开来，只要是在一个session中>或者
3.不同事务对表的不同索引锁定不同行
a.session 1 mysql> select * from t_kenyon; +------+------------+ | id | name | +------+------------+ | 1 | kenyon | | 123 | francs | | 345 | mood | | 78 | opp | | 78 | opp | | 789 | lighten | | 789 | james | | 899 | jiangkaish | | 902 | song | | 907 | hu | | 997 | wenjiab | +------+------------+ 11 rows in set (0.00 sec) mysql> update t_kenyon set name = 'kenyon_god' where id = 789; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 02.session 2更新另一个 mysql> update t_kenyon set name = 'koko' where name = 'hu';3.在session 1中查看进程 mysql> show processlist; +----+------+-----------+------+---------+------+----------+-----------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+-----------------------------------------------------+ | 8 | root | localhost | test | Query | 0 | NULL | show processlist | | 9 | root | localhost | test | Query | 8 | Updating | update t_kenyon set name = 'koko' where name = 'hu' | | 10 | root | localhost | NULL | Sleep | 113 | | NULL | +----+------+-----------+------+---------+------+----------+-----------------------------------------------------+ 3 rows in set (0.00 sec)可以看到被锁住了。

4.对name项加索引达到目的
a.会话1 mysql> alter table t_kenyon add index ind(name); Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update t_kenyon set name = 'kenyon_god' where id = 789; Query OK, 2 rows affected (0.02 sec) Rows matched: 2 Changed: 2 Warnings: 0b.会话2 mysql> update t_kenyon set name = 'koko' where name = 'hu'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

至此对表的不同行数据也达到了更新的目的。

四.总结
MYSQL的innodb引擎在查询过程中如果有基于索引扫描，可以实现行级锁定，但是该行级锁有相当的限制，查询未使用索引或表中未建索引时会触发表锁，数据量大、查询效果慢的情况下该现象将被放大，严重会导致DB奔溃。