MySQL INNODB锁机制简单试验

使用mysql连接mysql数据库，建两个会话（连接）。每个会话中都分别

set autocommit=0;

关闭自动提交。使用INNODB表做一下试验。



会话一：

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int(11) | NO | PRI | | |
| b | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,'abcde');
Query OK, 1 row affected (0.00 sec)

会话二：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> delete from test where a=1;
这里被锁住了（会话一中commit;或者rollback;，会话二才会有响应）

过了一会儿，报错：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

上述情况下，ORACLE中不会被锁住。



下面是更加需要注意的：

会话一：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+---+-------+
| a | b |
+---+-------+
| 1 | abcde |
+---+-------+
1 row in set (0.00 sec)
mysql> insert into test values(1,'abcde');
ERROR 1062 (23000): Duplicate entry '1' for key 1

会话二：

mysql> delete from test where a=1;
还是被锁住，过一会儿，则

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

我在两个会话中执行了SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

结果仍然相同。



再做新的试验：

会话一：

mysql> create table testfk(a int, constraint fk_testfk foreign key(a) references test(a)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testfk values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+---+-------+
| a | b |
+---+-------+
| 1 | abcde |
+---+-------+
1 row in set (0.00 sec)

mysql> select * from testfk;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set a=2 where a=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/testfk`, CONSTRAINT `fk_testfk` FOREIGN KEY (`a`) REFERENCES `test` (`a`))

会话二：

mysql> update test set a=1 where a=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

因此，在mysql innodb中使用事务，如果插入或者更新出错，一定要主动显式地执行rollback，否则可能产生不必要的锁而锁住其他的操作。