MySQL的伪行级锁

接触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> 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: 1Changed: 1Warnings: 0mysql> 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 updatemysql> create index ind_kenyon on t_kenyon（id）;Query OK, 0 rows affected （28.58 sec）Records: 0Duplicates: 0Warnings: 0mysql> update t_kenyon set id = 999 where id = 1;Query OK, 1 row affected （0.03 sec）Rows matched: 1Changed: 1Warnings: 0mysql> select * from t_kenyon;+------+| id |+------+| 78 || 78 ||123 ||345 ||789 ||999 |+------+6 rows in set （0.00 sec）4.session 2 updatemysql> 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: 1Changed: 1Warnings: 0mysql> 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: 1select_type: SIMPLEtable: t_kenyon type: refpossible_keys: ind_kenyonkey: ind_kenyonkey_len: 5ref: const rows: 1Extra: Using where; Using index1 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: 1select_type: SIMPLEtable: t_kenyon type: refpossible_keys: ind_kenyonkey: ind_kenyonkey_len: 5ref: const rows: 1Extra: Using where1 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: 1Changed: 1Warnings: 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 transactionmysql> 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: 2Changed: 2Warnings: 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: 0Duplicates: 0Warnings: 0mysql> update t_kenyon set name = "kenyon_god" where id = 789;Query OK, 2 rows affected （0.02 sec）Rows matched: 2Changed: 2Warnings: 0b.会话2 mysql> update t_kenyon set name = "koko" where name = "hu";Query OK, 1 row affected （0.00 sec）Rows matched: 1Changed: 1Warnings: 0至此对表的不同行数据也达到了更新的目的。四.总结
MYSQL的innodb引擎在查询过程中如果有基于索引扫描，可以实现行级锁定，但是该行级锁有相当的限制，查询未使用索引或表中未建索引时会触发表锁，数据量大、查询效果慢的情况下该现象将被放大，严重会导致DB奔溃。PostgreSQL的generate_series函数应用例子MySQL函数group_concat的使用相关资讯 MySQL基础教程

MySQL基础教程：关于varchar（N）（01月22日）
MySQL SELECT同时UPDATE同一张表（02/19/2013 07:20:18）
Linux修改MySQL最大并发连接数（02/15/2013 15:37:21）

高性能MySQL（第3版）中文PDF带目（10/26/2014 10:03:50）
如何在MySQL中的获取IP地址的网段（02/18/2013 12:23:33）
C++和C#访问MySQL的简单代码示例（12/21/2012 09:04:10）

本文评论查看全部评论（0）