· 8 years ago · Mar 13, 2018, 04:04 PM
1CREATE TABLE IF NOT EXISTS `media` (
2 `mid` int(11) NOT NULL AUTO_INCREMENT,
3 `order_type` int(11) NOT NULL,
4 `media_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
5 `package` int(11) NOT NULL,
6 `sent` int(11) NOT NULL,
7 `timestamp` datetime NOT NULL,
8 `t_check` int(11) NOT NULL,
9 `performed_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
10 `last_run` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
11 `flag` int(11) NOT NULL,
12 `package_sent_diff` int(11) AS (package-sent) PERSISTENT,
13 PRIMARY KEY (`mid`),
14 KEY `last_run` (`last_run`),
15 KEY `performed_by` (`performed_by`),
16 KEY `sent` (`sent`),
17 KEY `package` (`package`),
18 KEY `sent_package` (`sent`,`package`),
19 KEY `package_sent_diff` (`package_sent_diff`)
20) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=238343961;
21
22UPDATE media
23SET performed_by = '71602155f18ac6001eb',
24 last_run = NOW()
25WHERE flag = 0
26 AND t_check < 75
27 AND sent < 20
28 AND last_run < NOW()
29 AND performed_by IS NULL
30 AND sent < 4377
31 AND package_sent_diff > 0
32LIMIT 10;
33
34SELECT * FROM media WHERE performed_by = '71602155f18ac6001eb'
35
36UPDATE media SET sent = sent + 1, performed_by = NULL WHERE mid = 238323961
37
38$unique = uniqid();
39$mysqli->query("UPDATE media SET performed_by = '".$unique."', last_run = NOW() WHERE flag = 0 AND t_check < 75 AND sent < 20 AND last_run < NOW() AND performed_by IS NULL AND sent < 4377 AND package_sent_diff > 0 LIMIT 10");
40$query = $mysqli->query("SELECT * FROM media WHERE performed_by = '".$unique."'");
41
42while($job = $query->fetc_assoc()) {
43 if(doJob($job)) {
44 $mysqli->query("UPDATE media SET sent = sent + 1, performed_by = NULL WHERE mid = ".$job['mid']);
45 }
46}
47
48------------------------
49LATEST DETECTED DEADLOCK
50------------------------
512015-09-10 14:09:22 7f7f27fb6700
52*** (1) TRANSACTION:
53TRANSACTION 2544765863, ACTIVE 0 sec updating or deleting
54mysql tables in use 1, locked 1
55LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
56MySQL thread id 725683, OS thread handle 0x7f7f50fb6700, query id 989929718 localhost media_queue updating
57UPDATE media SET sent = sent + 1, performed_by = NULL WHERE mid = 238323961
58*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
59RECORD LOCKS space id 2565 page no 59448 n bits 768 index `package_sent_diff` of table `media_queue`.`media` trx table locks 1 total table locks 13 trx id 2544765863 lock_mode X locks gap before rec insert intention waiting lock hold time 0 wait time before grant 0
60*** (2) TRANSACTION:
61TRANSACTION 2544765389, ACTIVE 1 sec fetching rows, thread declared inside InnoDB 2350
62mysql tables in use 1, locked 1
633029 lock struct(s), heap size 357928, 25327 row lock(s)
64MySQL thread id 725748, OS thread handle 0x7f7f27fb6700, query id 989929439 localhost media_queue updating
65UPDATE media SET performed_by = '71602155f18ac6001eb', last_run = NOW() WHERE flag = 0 AND t_check < 75 AND sent < 20 AND last_run < NOW() AND performed_by IS NULL AND sent < 4377 AND package_sent_diff > 0 LIMIT 10
66*** (2) HOLDS THE LOCK(S):
67RECORD LOCKS space id 2565 page no 59448 n bits 768 index `package_sent_diff` of table `media_queue`.`media` trx table locks 1 total table locks 13 trx id 2544765389 lock_mode X lock hold time 0 wait time before grant 0
68*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
69RECORD LOCKS space id 2565 page no 55655 n bits 1192 index `package_sent_diff` of table `media_queue`.`media` trx table locks 1 total table locks 13 trx id 2544765389 lock_mode X waiting lock hold time 0 wait time before grant 0
70*** WE ROLL BACK TRANSACTION (1)
71
72ALTER TABLE `media_queue`.`media` ADD INDEX `performed_by_package_sent_diff` ( `performed_by` , `package_sent_diff` ) COMMENT '';
73
74UPDATE media SET sent = sent + 3, performed_by = NULL WHERE mid = 243674295 AND performed_by = '92817855f2e21978b76'
75
76------------------------
77LATEST DETECTED DEADLOCK
78------------------------
792015-09-11 15:48:29 7f7f487ff700
80*** (1) TRANSACTION:
81TRANSACTION 2585082648, ACTIVE 0 sec updating or deleting
82mysql tables in use 1, locked 1
83LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
84MySQL thread id 748336, OS thread handle 0x7f7f255b7700, query id 1018467234 localhost media_queue updating
85UPDATE media SET sent = sent + 1, performed_by = NULL WHERE mid = 244045072 AND performed_by = '11514555f2f24e094e7'
86*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
87RECORD LOCKS space id 2587 page no 61522 n bits 784 index `performed_by_package_sent_diff` of table `media_queue`.`media` trx table locks 1 total table locks 41 trx id 2585082648 lock_mode X locks gap before rec insert intention waiting lock hold time 0 wait time before grant 0
88*** (2) TRANSACTION:
89TRANSACTION 2585081924, ACTIVE 2 sec fetching rows, thread declared inside InnoDB 1852
90mysql tables in use 1, locked 1
914559 lock struct(s), heap size 521768, 66353 row lock(s)
92MySQL thread id 748490, OS thread handle 0x7f7f487ff700, query id 1018466800 localhost media_queue Searching rows for update
93UPDATE media SET performed_by = '35359855f2f4a5a7eaf', last_run = NOW() WHERE flag = 0 AND t_check < 75 AND sent < 20 AND last_run < NOW() AND performed_by IS NULL AND sent < 4370 AND package_sent_diff > 0 LIMIT 10
94*** (2) HOLDS THE LOCK(S):
95RECORD LOCKS space id 2587 page no 61522 n bits 784 index `performed_by_package_sent_diff` of table `media_queue`.`media` trx table locks 1 total table locks 41 trx id 2585081924 lock_mode X lock hold time 1 wait time before grant 0
96*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
97RECORD LOCKS space id 2587 page no 73537 n bits 520 index `performed_by_package_sent_diff` of table `media_queue`.`media` trx table locks 1 total table locks 41 trx id 2585081924 lock_mode X waiting lock hold time 0 wait time before grant 0
98*** WE ROLL BACK TRANSACTION (1)
99
100KEY `sent` (`sent`),
101KEY `sent_package` (`sent`,`package`),