mysql的INNODB引擎锁的原理试验_MySQL

mysql的INNODB引擎锁的原理是怎样的，来做个试验。

mysql> SELECT VERSION();

+-----------+
| VERSION() |
+-----------+
| 5.5.20 |
+-----------+
1 row in set (0.00 sec)

CREATE TABLE test
(
a INT(5),
b VARCHAR(10),
c VARCHAR(10)
);
INSERT INTO test VALUES(1,&＃39;111&＃39;,&＃39;111&＃39;);
INSERT INTO test VALUES(2,&＃39;222&＃39;,&＃39;222&＃39;);
INSERT INTO test VALUES(3,&＃39;333&＃39;,&＃39;333&＃39;);
INSERT INTO test VALUES(4,&＃39;444&＃39;,&＃39;444&＃39;);
INSERT INTO test VALUES(5,&＃39;555&＃39;,&＃39;555&＃39;);
INSERT INTO test VALUES(6,&＃39;666&＃39;,&＃39;666&＃39;);
COMMIT;

mysql> select * from test;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 111 | 111 |
| 2 | 222 | 222 |
| 3 | 333 | 333 |
| 4 | 444 | 444 |
| 5 | 555 | 555 |
| 6 | 666 | 666 |
+------+------+------+
6 rows in set (0.00 sec)

在CMD窗口完成实验，需要设置set autocommit=off;

1.在没有主键的情况下，修改不同的一条记录
session1:
mysql> update test set b=&＃39;111&＃39; where a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

session2:
mysql> update test set b=&＃39;222&＃39; where a=2;--先是hang住，过段时间后就报错
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2.在没有主键的情况下，新增一条数据，然后修改另一条数据
session1:
mysql> insert into test values(7,&＃39;777&＃39;,&＃39;777&＃39;);
Query OK, 1 row affected (0.00 sec)

session2:
mysql> update test set b=&＃39;222&＃39; where a=2;--先是hang住，过段时间后就报错
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3.在有主键的情况下，修改不同的一条记录
ALTER TABLE test ADD PRIMARY KEY(a);
当有主键时没有产生锁全表的情况
session1:
mysql> update test set b=&＃39;111&＃39; where a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

session2:
mysql> update test set b=&＃39;222&＃39; where a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

当有主键时修改同一条记录，会hang住，说明就是行锁
session1:
mysql> update test set b=&＃39;111&＃39; where a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
session2:
mysql> update test set b=&＃39;111&＃39; where a=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4.在有主键的情况下，insert和update
session1:
mysql> insert into test values(8,&＃39;888&＃39;,&＃39;888&＃39;);
Query OK, 1 row affected (0.00 sec)

session2:
mysql> update test set b=&＃39;111&＃39; where a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

5.在没有索引的情况下，修改不同的一条记录
session1:
mysql> update test set c=&＃39;111&＃39; where b=&＃39;111&＃39;;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

session2:
mysql> update test set c=&＃39;222&＃39; where b=&＃39;222&＃39;;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

6.在有索引的情况下，修改不同的一条记录
CREATE INDEX ind_t_b ON test(b);
session1:
mysql> update test set c=&＃39;111&＃39; where b=&＃39;111&＃39;;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

session2:
mysql> update test set c=&＃39;222&＃39; where b=&＃39;222&＃39;;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0

总结：当用到了索引(同时我也测试了建了索引没有用到的情况，还是行锁)，则是行锁，否则锁全表，没有Oracle中的行锁方便。