Laravel 在执行数据库模式的队列时为什么造成死锁?

如题,下面是我 show engine innodb status; 出来前面部分

=====================================
2018-05-26 16:21:12 7fcbf27ff700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12477565 srv_active, 0 srv_shutdown, 14818180 srv_idle
srv_master_thread log flush and writes: 27295745
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 573604
OS WAIT ARRAY INFO: signal count 4084710
Mutex spin waits 5187250, rounds 7421711, OS waits 25286
RW-shared spins 4246125, rounds 145338227, OS waits 536069
RW-excl spins 179031, rounds 4839102, OS waits 9730
Spin rounds per wait: 1.43 mutex, 34.23 RW-shared, 27.03 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-05-26 15:01:07 7fcc22af0700
*** (1) TRANSACTION:
TRANSACTION 50581977, ACTIVE 4.842 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
LOCK BLOCKING MySQL thread id: 51200090 block 50805896
MySQL thread id 50805896, OS thread handle 0x7fcc22bb3700, query id 669291633 192.168.10.10 demo Creating sort index
select * from `jobs` where `queue` = 'queueOne' and ((`reserved` = '0' and `available_at` <= '1527318062') or (`reserved` = '1' and `reserved_at` <= '1527318002')) order by `id` asc limit 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 128 index `jobs_queue_reserved_reserved_at_index` of table `test`.`jobs` trx id 50581977 lock_mode X locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 10; hex 636865636b52756c6573; asc queueThree;;
 1: len 1; hex 00; asc  ;;
 2: SQL NULL;
 3: len 8; hex 000000000004b45e; asc        ^;;

*** (2) TRANSACTION:
TRANSACTION 50580027, ACTIVE 1742.755 sec
mysql tables in use 1, locked 1
11 lock struct(s), heap size 1184, 115 row lock(s), undo log entries 108
MySQL thread id 51200090, OS thread handle 0x7fcc22af0700, query id 669291782 192.168.10.10 demo Sending data
select * from `jobs` where `queue` = 'queueTwo' and ((`reserved` = '0' and `available_at` <= '1527318067') or (`reserved` = '1' and `reserved_at` <= '1527318007')) order by `id` asc limit 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 4 n bits 104 index `jobs_queue_reserved_reserved_at_index` of table `test`.`jobs` trx id 50580027 lock_mode X locks rec but not gap
Record lock, heap no 21 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 10; hex 636865636b52756c6573; asc queueThree;;
 1: len 1; hex 00; asc  ;;
 2: SQL NULL;
 3: len 8; hex 000000000004b45e; asc        ^;;

Record lock, heap no 23 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 10; hex 636865636b43616c6c73; asc queueTwo;;
 1: len 1; hex 00; asc  ;;
 2: SQL NULL;
 3: len 8; hex 000000000004b450; asc        P;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 3 n bits 112 index `PRIMARY` of table `test`.`jobs` trx id 50580027 lock_mode X locks rec but not gap waiting
Record lock, heap no 33 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 000000000004b477; asc        w;;
 1: len 6; hex 00000303d174; asc      t;;
 2: len 7; hex b2000001ee0110; asc        ;;
 3: len 14; hex 636865636b496e737572616e6365; asc queueOne;;
 4: len 30; hex 7b226a6f62223a22496c6c756d696e6174655c5c51756575655c5c43616c; asc {"job":"Illuminate\\Queue\\Cal; (total 331 bytes);
 5: len 1; hex 00; asc  ;;
 6: len 1; hex 00; asc  ;;
 7: SQL NULL;
 8: len 4; hex 5b0905f8; asc [   ;;
 9: len 4; hex 5b0905f3; asc [   ;;
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 5
leo

不加锁的话,一个任务就可能被多个 worker 同时执行

5年前 评论

我想知道为什么造成死锁,现在死锁导致队列只写入,不执行

5年前 评论

如何解决的,我的也出现了, jobs 插不进数据。等待锁

5年前 评论

@FreeMason 没有解决,可以调整锁级别,也可以换成Redis,在Redis下很畅通的~

5年前 评论
pramychao 4年前

应该是你代码里面的逻辑导致忘记解锁 比如说 DB::rollBack(); return;

代码中出现需要返回的,然后忘了先rollback

4年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!