innodb_autoinc_lock_mode=2
Posted by: Juraj Pisar
Date: August 04, 2014 06:51AM

Hello

I have table file_scan in database

| file_scan | CREATE TABLE `file_scan` (
`file_scan_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date_scanned` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`file_id` bigint(20) unsigned NOT NULL,
`scanner_id` int(10) unsigned NOT NULL DEFAULT '0',
`result` varchar(250) DEFAULT NULL,
`scansystem_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`file_scan_id`),
UNIQUE KEY `iall` (`file_id`,`scanner_id`),
KEY `idate` (`date_scanned`),
KEY `iscanner_id_result` (`scanner_id`,`result`),
KEY `iall_result` (`file_id`,`scanner_id`,`result`(1)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=747742660 DEFAULT CHARSET=latin1 |

This table is filled by triggers:

CREATE TABLE `file_scan_change` (
`file_scan_change_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date_scanned` datetime DEFAULT NULL,
`file_id` bigint(20) unsigned NOT NULL,
`scanner_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'scanner',
`result` varchar(250) DEFAULT NULL,
`scansystem_id` int(10) unsigned DEFAULT NULL COMMENT 'OBSOLETE',
`old_result` varchar(250) DEFAULT NULL COMMENT 'result before update',
PRIMARY KEY (`file_scan_change_id`)
) ENGINE=InnoDB AUTO_INCREMENT=635261638 DEFAULT CHARSET=latin1 COMMENT='Holds changes of file_scan table' |


CREATE TRIGGER `file_scan_insert` BEFORE INSERT ON `file_scan` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
INSERT INTO file_scan_change (date_scanned, file_id, scanner_id, result, old_result, scansystem_id) VALUES (NEW.date_scanned, NEW.file_id, NEW.scanner_id, NEW.result, result, NEW.scansystem_id)

CREATE TRIGGER `file_scan_update` AFTER UPDATE ON `file_scan` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
INSERT INTO file_scan_change (date_scanned, file_id, scanner_id, result, scansystem_id) VALUES (NEW.date_scanned, NEW.file_id, NEW.scanner_id, NEW.result, NEW.scansystem_id)

This is mysql version:

+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.19 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.19-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | debian6.0 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

we use innodb_autoinc_lock_mode=2 and row based replication


There were 2 different procedures running at the same time one with this insert:

INSERT INTO file_scan(file_id, scanner_id, date_scanned, result, scansystem_id)
SELECT b.file_id, 2, IFNULL(b.file_scan__date_scanned, NOW()), b.file_scan__result_release,
b.file_scan__scansystem_id
FROM bulk b WHERE b.file_id IS NOT NULL AND b.file_scan__result_release IS NOT NULL
ON DUPLICATE KEY UPDATE file_scan.date_scanned = IFNULL(b.file_scan__date_scanned, NOW()),
file_scan.result = b.file_scan__result_release, file_scan.scansystem_id = b.file_scan__scansystem_id;


other with this insert:
INSERT INTO file_scan(file_id, scanner_id, date_scanned, result, scansystem_id)
SELECT b.file_id, 1, IFNULL(b.file_scan__date_scanned, NOW()), b.file_scan__result_devel,
b.file_scan__scansystem_id
FROM bulk b WHERE b.file_id IS NOT NULL AND b.file_scan__result_devel IS NOT NULL
ON DUPLICATE KEY UPDATE file_scan.date_scanned = IFNULL(b.file_scan__date_scanned, NOW()),
file_scan.result = b.file_scan__result_devel, file_scan.scansystem_id = b.file_scan__scansystem_id;

At the same time, 2 bulk loads do database were running in 2 different threads (from 2 different source application systems)

thread 1 inserted record for file_id 17958372. It assigned file_scan_id 731658437 to this record. Thread 2 was inserting data for file_id 309441063. It inserted data for this file_id to file_scan_id 731658437 and caused, that record for file_id 17958372. It looks to me that both threads received same new file_scan_id for inserted row, and "ON DUPLICATE KEY" was used.

This is pretty big problem for us (data inconsistency always is), and we need to know how to prevent this.

I attached shrinked --verbose binlog that is showing the part that caused problem. Is this problem of innodb_autoinc_lock_mode=2 with combination with ON DUPLICATE KEY UPDATE ?

thanks for advice

Juraj

Options: ReplyQuote


Subject
Written By
Posted
innodb_autoinc_lock_mode=2
August 04, 2014 06:51AM
August 05, 2014 09:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.