set session transaction isolation level read committed;
select @@autocommit;
set @@autocommit=0;
select @@transaction_isolation;
#事务1
BEGIN;
select * from crowd_1688_order where order_no = "3571794000219872909" for update; #2
UPDATE crowd_1688_order SET logistics_status=-1 WHERE order_no = "3571794000219872909"
commit;
#22
set session transaction isolation level read committed;
select @@autocommit;
set @@autocommit=0;
select @@transaction_isolation;
#事务2
BEGIN;
select * from crowd_1688_order where order_no = "3572558607122872909" for update;#1
UPDATE crowd_1688_order SET logistics_status=-1 WHERE order_no = "3572558607122872909";#3
commit;
#29
SELECT THREAD_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
执行流程:
事务2select -> 事务1select -> 事务2update -> 发生死锁
执行完事务1的select后锁状态图如下:
事务2锁住了29这一行 ,事务1锁住了22这一行并在等待事务2释放29的行锁,事务1此时处于阻塞状态。
事务2执行update语句,因为update的条件没走索引所以走主键索引进行加锁(注:RC隔离级别如果加锁行没走索引会走主键索引,先全表所有行加锁,加完后再把不满足条件的行锁取消),此时事务2走主键索引加锁时,发现事务1加了22的行锁,此时死锁算法监测到事务1正在等待事务2释放29的行锁,此时检测到环形等待锁释放,死锁发生。