I have been having all sorts of trouble with a particular update query, which seems like it should be very unproblematic. I've changed the names, but the table is:
CREATE TABLE `problem_table` (
`id` int(11) NOT NULL,
`type` enum('TYPE1','TYPE2','TYPE3') NOT NULL,
`date` datetime NOT NULL,
`reference_id` int(11) DEFAULT NULL,
`value` varchar(255) NOT NULL,
`source` varchar(16) DEFAULT NULL,
`problem_field` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `type_idx` (`type`),
KEY `value_idx` (`value`(12)),
KEY `latest_id` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
and the query causing problems is:
UPDATE problem_table SET problem_field = 20000101 WHERE id = 6526153;
the values of problem_field and id here don't seems to be important.
This single update is deadlocking repeatedly with various select queries on problem_table, so my question is - what locks exactly are taken out by this simple update query? I should add that both of the deadlocking transactions just contain the single query.
I have read through the docs but they don't seem particularly comprehensive.
For reference, here is a query it deadlocks with and its INNODB STATUS report, although this is only one example out of many different queries:
INSERT INTO temp
SELECT
p.*,
DATE(p.date)
FROM
problem_table p
WHERE p.type IN ('TYPE1', 'TYPE2')
AND p.source = 'FOO';
------------------------
LATEST DETECTED DEADLOCK
------------------------
161107 0:00:00
*** (1) TRANSACTION:
TRANSACTION 3C7788A94, ACTIVE 69 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1248, 7 row lock(s), undo log entries 6
MySQL thread id 6558222, OS thread handle 0x7f44a606d700, query id 3110073624 164.55.80.105 sym_dbuser Updating
-- user=XXX progname=XXX host=XXX pid=XXX ldsn=XXX
-- DBI::db=HASH(0x1d15ecb0)
UPDATE problem_table SET problem_field = 'XXXX-XX-XX XX:XX:XX'WHERE id = 'XXXXX'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1069083 page no 313 n bits 280 index `PRIMARY` of table `XXX`.`problem_table` trx id 3C7788A94 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 3C766F450, ACTIVE 831 sec fetching rows, thread declared inside InnoDB 39
mysql tables in use 2, locked 2
47612 lock struct(s), heap size 5339576, 9395927 row lock(s), undo log entries 9194153
MySQL thread id 6558799, OS thread handle 0x7f4203cb6700, query id 3108758081 172.29.1.16 XXX Sending data
-- user=XXX progname=XXX host=XXX pid=XXX ldsn=sym@symprod
INSERT INTO temp
SELECT
p.*,
DATE(p.date)
FROM
problem_table p
WHERE p.type IN ('TYPE1', 'TYPE2')
AND p.source = 'FOO';
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1069083 page no 313 n bits 280 index `PRIMARY` of table `XXX`.`problem_table` trx id 3C766F450 lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1069083 page no 82008 n bits 280 index `PRIMARY` of table `XXX`.`problem_table` trx id 3C766F450 lock mode S waiting
*** WE ROLL BACK TRANSACTION (2)
EDIT:
For the benefit of anyone reading this down the line, I've just found out that INNODB is capable of detecting deadlocks from 3 or more transactions, but it only lists the victim and the transaction that wanted the victim's lock in the deadlock report - the remaining transactions aren't listed in there at all.
To see this, run three transactions like:
T(ransaction)1 take S lock on R(ecord) 1
T2 take S lock on R2
T2 take X lock on R1 (hangs waiting for T1)
T3 take S lock on R3
T3 take X lock on R2 (hangs waiting for T2)
T1 take X lock on R3 (deadlock detected)
To answer the basic question that was buried in the middle there, the locks taken out by
UPDATE problem_table SET problem_field = 20000101 WHERE id = 6526153;
where problem_table is defined as in the question are just:
Turns out that things weren't quite as I'd described though - tool 1 that was performing the problematic update does no transaction management, so all of its updates form their own transactions normally.
However, in this case there was a tool 2, which uses tool 1, and wraps all its actions up in its own transaction. Therefore, this single update was part of a larger transaction after all (containing an update on another row in the same table).
This makes it much clearer why a deadlock could occur - the fix being implemented is to make these two updates in order of their primary key (there's a good post on this sort of deadlocking here).
I will be considering some schema changes as suggested by @fancyPants as well, although this will take a bit more work - the example I provided is pretty stripped down, and the table design overall is fairly horrendous. It could definitely use some sprucing up...