MySQL的InnoDB引擎鎖的原理實驗

MySQL的InnoDB引擎鎖的原理實驗

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,'111','111');
INSERT INTO test VALUES(2,'222','222');
INSERT INTO test VALUES(3,'333','333');
INSERT INTO test VALUES(4,'444','444');
INSERT INTO test VALUES(5,'555','555');
INSERT INTO test VALUES(6,'666','666');
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='111' where a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

session2:
mysql>  update test set b='222' where a=2;--先是hang住，過段時間後就報錯
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2.在沒有主鍵的情況下，新增一條資料，然後修改另一條資料
session1:
mysql> insert into test values(7,'777','777');
Query OK, 1 row affected (0.00 sec)

session2:
mysql>  update test set b='222' 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='111' where a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

session2:
mysql> update test set b='222' 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='111' where a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
session2:
mysql>  update test set b='111' where a=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

session2:
mysql> update test set b='111' 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='111' where b='111';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

session2:
mysql> update test set c='222' where b='222';
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='111' where b='111';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

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

總結：當用到了索引(同時我也測試了建了索引沒有用到的情況，還是行鎖)，則是行鎖，否則鎖全表，沒有Oracle中的行鎖方便。

MySQL InnoDB儲存引擎鎖機制實驗

InnoDB儲存引擎的啟動、關閉與恢複

MySQL InnoDB獨立資料表空間的配置

MySQL Server 層和 InnoDB 引擎層 體繫結構圖

InnoDB 死結案例解析

MySQL Innodb獨立資料表空間的配置

本文永久更新連結地址：