超赞,InnoDB调试死锁的方法!(收藏)
show global variables like "innodb_locks%";
show global variables like "autocommit";
show global variables like "tx_isolation";
set session transaction isolation level X;
read uncommitted
read committed
repeatable read
serializable
set session autocommit=0;
create table t (
id int(10) primary key
)engine=innodb;
start transaction;
insert into t values(1);
insert into t values(3);
insert into t values(10);
commit;
(-infinity, 1)
(1, 3)
(3, 10)
(10, infinity)
set session autocommit=0;
start transaction;
delete from t where id=5;
set session autocommit=0;
start transaction;
insert into t values(0);
insert into t values(2);
insert into t values(12);
insert into t values(7);
show engine innodb status;
insert into t values(7);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
set session autocommit=0;
start transaction;
insert into t values(7);
set session autocommit=0;
start transaction;
insert into t values(7);
set session autocommit=0;
start transaction;
insert into t values(7);
rollback;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
A:set session autocommit=0;
A:start transaction;
A:delete from t where id=6;
B:set session autocommit=0;
B:start transaction;
B:delete from t where id=7;
A:insert into t values(5);
B:insert into t values(8);
show engine innodb status;
WE ROLL BACK TRANSACTION (2)