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,'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引擎锁的原理试验 - mysql数据库栏目的详细内容...