创建一个innodb引擎的表
mysql> create table test (a int, b char(10)) engine=innodb;
Query OK, 0 rows affected (0.13 sec)
mysql> insert into test values (1,'abc'),(2,'def'); --插入两条记录
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
开启两个会话,把autocommit关掉, session 1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
session 2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
在session 1中用for update 查看a为1的行
mysql> select * from test where a = 1 for update;
+------+------+
| a | b |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
在session 2中查看a为2的行
mysql> select * from test where a = 2 for update; 等待
在session 2中出现了等待,原因是在没有索引的情况下Innodb表只能使用表锁,Mysql的行锁是对索引加的锁。
创建一个索引
mysql> create index test_index on test(a);
Query OK, 2 rows affected (0.23 sec)
Records: 2 Duplicates: 0 Warnings: 0
重复上面的操作,session 2中也可以查看到数据
mysql> select * from test where a = 2 for update;
+------+------+
| a | b |
+------+------+
| 2 | def |
+------+------+
1 row in set (0.00 sec)
在插入一条
mysql> insert into test values (1,'rrr');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+------+
| a | b |
+------+------+
| 1 | abc |
| 2 | def |
| 1 | rrr |
+------+------+
3 rows in set (0.00 sec)
session 1中
mysql> select * from test where a = 1 and b = 'rrr' for update;
+------+------+
| a | b |
+------+------+
| 1 | rrr |
+------+------+
1 row in set (0.00 sec)
session 2中出现了等待,虽然两个会话查看的不是同一行,但是因为使用了相同的索引键,出现了锁冲突。
mysql> select * from test where a = 1 and b = 'abc' for update; 等待
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25361369/viewspace-709743/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25361369/viewspace-709743/