mysql select for update, row lock, table lock

mysql select for update, row lock, table lock

 

一些前置条件

打开连个控制台模拟

 

mysql> show create table table_test;
| Table      | Create Table                                                                
| table_test | CREATE TABLE `table_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_index` int(11) DEFAULT '0',
  `id_no_index` int(11) DEFAULT '0',
  `common` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_id_index` (`id_index`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |

 

mysql> select * from table_test;
+----+----------+-------------+--------+
| id | id_index | id_no_index | common |
+----+----------+-------------+--------+
|  1 |        0 |           0 |      1 |
|  2 |        0 |           0 |      2 |
|  3 |        0 |           0 |      3 |
|  4 |        0 |           0 |      4 |
|  5 |        0 |           0 |      5 |
|  6 |        1 |           1 |      5 |
|  7 |        1 |           1 |      6 |
|  8 |        1 |           1 |      7 |
|  9 |        1 |           1 |      8 |
| 10 |        1 |           1 |      9 |
+----+----------+-------------+--------+
10 rows in set (0.00 sec)

 

mysql> set autocommit = 0;

 

 

索引字段

session1

mysql> begin;
mysql> select * from table_test where id_index=1 for update;

 

session2

mysql> select * from table_test where id_index=0 for update;

 正常执行

 

mysql> select * from table_test where id_index=1 for update;

阻塞，当session1执行 "commit" or "rollback" 释放锁，session2继续执行

 

结论：有索引的字段用row lock

 

 

无索引字段

session1

mysql> begin;
mysql> select * from table_test where id_no_index=1 for update;

 

session2

select * from table_test where id_no_index=0 for update;

此时会阻塞，当session1执行 "commit" or "rollback" 释放锁，session2继续执行

 

结论：无索引的字段用table lock

 

范围条件

mysql> select * from table_test where id>8 for update;
+----+----------+-------------+--------+
| id | id_index | id_no_index | common |
+----+----------+-------------+--------+
|  9 |        1 |           1 |      8 |
| 10 |        1 |           1 |      9 |
+----+----------+-------------+--------+

mysql 会将这两条记录加锁

也会对大于10的加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）

 

session1

mysql> begin;
mysql> select * from table_test where id_index>8 for update;

 

session2

mysql> select * from table_test where id_index<8 for update;

正常执行

mysql> insert into table_test(id,id_index,id_no_index,common) values (11,1,1,10);

此时会阻塞，当session1执行 "commit" or "rollback" 释放锁，session2继续执行

 

 

 InnoDB行锁和表锁的分析

http://haicang.blog.51cto.com/2590303/1085388