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;














没有更多推荐了,返回首页