mysql的INNODB引擎鎖的原理是怎樣的，來做個(gè)試驗(yàn)。
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窗口完成實(shí)驗(yàn)，需要設(shè)置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住，過段時(shí)間后就報(bào)錯(cuò)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
2.在沒有主鍵的情況下，新增一條數(shù)據(jù)，然后修改另一條數(shù)據(jù)
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住，過段時(shí)間后就報(bào)錯(cuò)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
3.在有主鍵的情況下，修改不同的一條記錄
ALTER TABLE test ADD PRIMARY KEY(a);
當(dāng)有主鍵時(shí)沒有產(chǎn)生鎖全表的情況
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
當(dāng)有主鍵時(shí)修改同一條記錄，會(huì)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
總結(jié)：當(dāng)用到了索引(同時(shí)我也測試了建了索引沒有用到的情況，還是行鎖)，則是行鎖，否則鎖全表，沒有Oracle中的行鎖方便。
聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
