Home >Database >Mysql Tutorial >mysql的INNODB引擎锁的原理试验_MySQL

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

WBOY
WBOYOriginal
2016-06-01 13:03:58921browse

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中的行锁方便。
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn