mysql 死锁模拟测试

CREATE TABLE `test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `age` int(11) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test (age) values (1);

insert into test (age) values (2);

select * from test;

首先执行sql1：

start transaction;
select * from test where age=1;
--delete from test where age=1;

--commit;

后两行是注释掉的，这里还没有执行

再执行sql2：

start transaction;
delete from test where age=1;

--commit;

后一行是注释掉的，这里还没有执行

然后继续执行sql1里面的第三行

--start transaction;
--select * from test where age=1;
delete from test where age=1;

--commit;

一三四行注释掉了

这个时候就出现了死锁了，通过以下语句可以查看到：

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;  

show processlist;
 SHOW ENGINE INNODB STATUS;