Как эти транзакции вызывают тупик?
У меня есть 2 типа транзакций: Тип 1 включает в себя следующие Sqls:
UPDATE userdb_ingest_report SET start_time='%s', end_time='%s', is_csv_ingest=%d, success=%d, failed=%d WHERE clientid='%s' AND filename='%s' AND network_id=%d AND data_provider_id=%d;\n
or
INSERT INTO userdb_ingest_report(clientid, filename, network_id, data_provider_id, start_time, end_time, is_csv_ingest, success, failed) VALUES ('%s', '%s', %d, %d, '%s', '%s', %d, %d, %d);\n"
and
DELETE FROM userdb_ingest_report WHERE clientid<>'%s' AND filename='%s' AND network_id=%d AND data_provider_id=%d;\n
Тип 2 включает в себя следующие Sqls:
UPDATE userdb_ingest_report SET start_time=LEAST(%s, start_time), end_time=GREATEST(%s, end_time), is_csv_ingest=%d, success=success+%d, failed=failed+%d WHERE clientid='%s' AND filename='%s' AND network_id='%d' AND data_provider_id='%d'"
or
INSERT INTO userdb_ingest_report(clientid, filename, network_id, data_provider_id, start_time, end_time, is_csv_ingest, success, failed) VALUES ('%s', '%s', %d, %d, '%s', '%s', %d, %d, %d);\n
Может быть 1 экземпляр типа 1 и 1-6 экземпляров типа 2, работающих одновременно. Иногда я получаю тупиковую ошибку:
Update table userdb_ingest_report error. Sql:UPDATE userdb_ingest_report SET start_time=LEAST(20170622134845, start_time), end_time=GREATEST(20170622134846, end_time), is_csv_ingest=1, success=success+184, failed=failed+0 WHERE clientid='clienta' AND filename='file1' AND network_id='48804' AND data_provider_id='386123', Error:Error 1213: Deadlock found when trying to get lock; try restarting transaction
or
Insert table userdb_ingest_report error. Sql:INSERT INTO userdb_ingest_report(clientid, filename, network_id, data_provider_id, start_time, end_time, is_csv_ingest, success, failed) VALUES ('clientb', 'fileb', 385316, 385316, '20170622140908', '20170622140922', 1, 372521, 0); , Error:Error 1213: Deadlock found when trying to get lock; try restarting transaction
и это схема этой таблицы:
CREATE TABLE `userdb_ingest_report` (
`filename` varchar(255) NOT NULL,
`network_id` bigint(20) NOT NULL,
`data_provider_id` bigint(20) NOT NULL,
`start_time` datetime NOT NULL,
`end_time` datetime NOT NULL,
`is_csv_ingest` smallint(1) NOT NULL,
`success` bigint(20) DEFAULT '0',
`failed` bigint(20) DEFAULT '0',
`clientid` char(30) DEFAULT '',
KEY `network_data_provider_clientid_idx` (`network_id`,`data_provider_id`,`clientid`,`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Когда я использую show innodb status в mysql, я получаю следующие сообщения о последней информации о взаимоблокировке:
------------------------
LATEST DETECTED DEADLOCK
------------------------
170622 21:15:22
*** (1) TRANSACTION:
TRANSACTION 3F427F4B, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 30 lock struct(s), heap size 6960, 41 row lock(s), undo log entries 14
MySQL thread id 43053224, OS thread handle 0x2b8f80ffe940, query id 1158507552 10.1.10.42 backend_new update
INSERT INTO userdb_ingest_report(clientid, filename, network_id, data_provider_id, start_time, end_time, is_csv_ingest, success, failed) VALUES ('auingest03.fwmrm.net_1', '/mnt/sftponly/381963/files/audience/ingest/pending/visitor_attr_381963_20170622145737.csv.gz', 0, 381963, '20170622211520', '20170622211522', 1, 18203, 0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1026 page no 711706 n bits 176 index `network_data_provider_clientid_idx` of table `fwmrm_stats`.`userdb_ingest_report` trx id 3F427F4B lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 16 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000000; asc ;;
1: len 8; hex 800000000005d457; asc W;;
2: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc ;;
3: len 30; hex 2f6d6e742f736674706f6e6c792f3338323033392f66696c65732f617564; asc /mnt/sftponly/382039/files/aud; (total 92 bytes);
4: len 6; hex 00000c420d62; asc B b;;
*** (2) TRANSACTION:
TRANSACTION 3F427F4C, ACTIVE 0 sec inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
28 lock struct(s), heap size 6960, 38 row lock(s), undo log entries 13
MySQL thread id 43053207, OS thread handle 0x2b8f80bee940, query id 1158507553 10.1.10.41 backend_new update
INSERT INTO userdb_ingest_report(clientid, filename, network_id, data_provider_id, start_time, end_time, is_csv_ingest, success, failed) VALUES ('auingest02.fwmrm.net_1', '/mnt/sftponly/381963/files/audience/ingest/pending/visitor_attr_381963_20170622145737.csv.gz', 0, 381963, '20170622211520', '20170622211522', 1, 36103, 0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1026 page no 711706 n bits 176 index `network_data_provider_clientid_idx` of table `fwmrm_stats`.`userdb_ingest_report` trx id 3F427F4C lock_mode X locks gap before rec
Record lock, heap no 16 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000000; asc ;;
1: len 8; hex 800000000005d457; asc W;;
2: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc ;;
3: len 30; hex 2f6d6e742f736674706f6e6c792f3338323033392f66696c65732f617564; asc /mnt/sftponly/382039/files/aud; (total 92 bytes);
4: len 6; hex 00000c420d62; asc B b;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1026 page no 711706 n bits 176 index `network_data_provider_clientid_idx` of table `fwmrm_stats`.`userdb_ingest_report` trx id 3F427F4C lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 16 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000000; asc ;;
1: len 8; hex 800000000005d457; asc W;;
2: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc ;;
3: len 30; hex 2f6d6e742f736674706f6e6c792f3338323033392f66696c65732f617564; asc /mnt/sftponly/382039/files/aud; (total 92 bytes);
4: len 6; hex 00000c420d62; asc B b;;
так как это могло произойти, и как я могу решить этот тупик?