Bug #3714 | Sometimes InnoDB forget auto-inc counter without restart | ||
---|---|---|---|
Submitted: | 11 May 2004 16:46 | Modified: | 14 May 2004 14:14 |
Reporter: | Marat Latypov | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 4.0.18 | OS: | Windows (WinXP, Linux) |
Assigned to: | Heikki Tuuri | CPU Architecture: | Any |
[11 May 2004 16:46]
Marat Latypov
[14 May 2004 1:56]
MySQL Verification Team
Verified on XP. mysql> insert into tbl (fld)values('asda'); ERROR 1213: Deadlock found when trying to get lock; Try restarting transaction mysql>
[14 May 2004 6:03]
Heikki Tuuri
Marat, please print the SHOW INNODB STATUS after such an unexplained deadlock. It contains detailed info of the latest deadlock in the database. I do not see how InnoDB could forget that it has already initialized the auto-inc counter for the table, unless there is memory corruption that resets the field in the table object. The relevant code is below. Regards, Heikki /************************************************************************ Reads the next autoinc value (== autoinc counter value), 0 if not yet initialized. */ ib_longlong dict_table_autoinc_read( /*====================*/ /* out: value for a new row, or 0 */ dict_table_t* table) /* in: table */ { ib_longlong value; mutex_enter(&(table->autoinc_mutex)); if (!table->autoinc_inited) { value = 0; } else { value = table->autoinc; } mutex_exit(&(table->autoinc_mutex)); return(value); }
[14 May 2004 6:45]
Marat Latypov
One of such situations LATEST DETECTED DEADLOCK ------------------------ 040511 14:01:22 *** (1) TRANSACTION: TRANSACTION 0 17005687, ACTIVE 0 sec, process no 2567, OS thread id 3162124 inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 184, query id 224345 Stn 10.10.10.27 stn update INSERT INTO doc_oper_h (docnum,docnum1,date,wp,sum_,variant,doc_mon51) VALUES (119120,'033','2004-02-10',11,42200.00,1,19809) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 11225 n bits 112 table stn/doc_oper_h index PRIMARY trx id 0 17005687 lock mode S locks rec but not gap waiting Record lock, heap no 45 RECORD: info bits 0 0: len 4; hex 0001d150; asc ...P;; 1: len 6; hex 0000010378d0; asc ....x.;; *** (2) TRANSACTION: TRANSACTION 0 17004752, ACTIVE 3 sec, process no 2285, OS thread id 1212449 setting auto-inc lock, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 32 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 65, query id 224357 snab_sklad 10.10.10.63 stn update insert into doc_oper_h (docnum1,date,wp,sum_,variant,doc_zex) values (" 259.0 ","2004-4-14",6,834,3,8473) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 11225 n bits 112 table stn/doc_oper_h index PRIMARY trx id 0 17004752 lock_mode X locks rec but not gap Record lock, heap no 45 RECORD: info bits 0 0: len 4; hex 0001d150; asc ...P;; 1: len 6; hex 0000010378d0; asc ....x.;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: TABLE LOCK table stn/doc_oper_h trx id 0 17004752 lock_mode AUTO-INC waiting *** WE ROLL BACK TRANSACTION (1) ------------ My Table >SHOW CREATE TABLE DOC_OPER_H CREATE TABLE `doc_oper_h` ( `docnum` int(10) unsigned NOT NULL auto_increment, `docnum1` char(30) NOT NULL default '', `date` date NOT NULL default '0000-00-00', `wp` int(11) default NULL, `sum_` decimal(10,2) default NULL, `variant` tinyint(4) default NULL, `doc_otgr` int(11) default NULL, `doc_mon50` int(11) default NULL, `doc_mon51` int(11) default NULL, `doc_remon` int(11) default NULL, `doc_prix60` int(11) default NULL, `doc_prix71` int(11) default NULL, `doc_zex` int(11) default NULL, `doc_spis_akt` int(11) default NULL, `doc_spis_norm` int(11) default NULL, PRIMARY KEY (`docnum`), KEY `docnum1` (`docnum1`), KEY `wp` (`wp`), KEY `doc_otgr` (`doc_otgr`), KEY `doc_mon50` (`doc_mon50`), KEY `doc_mon51` (`doc_mon51`), KEY `doc_prix60` (`doc_prix60`), KEY `doc_prix71` (`doc_prix71`), KEY `doc_zex` (`doc_zex`), KEY `doc_spis_akt` (`doc_spis_akt`), KEY `doc_spis_norm` (`doc_spis_norm`), KEY `doc_remon` (`doc_remon`), CONSTRAINT `0_7895` FOREIGN KEY (`wp`) REFERENCES `wp` (`id`), CONSTRAINT `0_7896` FOREIGN KEY (`doc_otgr`) REFERENCES `doc_otgr_h` (`docnum`) ON DELETE CASCADE, CONSTRAINT `0_7897` FOREIGN KEY (`doc_mon50`) REFERENCES `doc_mon50` (`docnum`) ON DELETE CASCADE, CONSTRAINT `0_7898` FOREIGN KEY (`doc_mon51`) REFERENCES `doc_mon51` (`docnum`) ON DELETE CASCADE, CONSTRAINT `0_7899` FOREIGN KEY (`doc_prix60`) REFERENCES `doc_prix60_h` (`docnum`) ON DELETE CASCADE, CONSTRAINT `0_7900` FOREIGN KEY (`doc_prix71`) REFERENCES `doc_prix71_h` (`docnum`) ON DELETE CASCADE, CONSTRAINT `0_7901` FOREIGN KEY (`doc_zex`) REFERENCES `doc_zex_h` (`docnum`) ON DELETE CASCADE, CONSTRAINT `0_7902` FOREIGN KEY (`doc_spis_akt`) REFERENCES `doc_spis_akt_h` (`docnum`) ON DELETE CASCADE, CONSTRAINT `0_7903` FOREIGN KEY (`doc_spis_norm`) REFERENCES `doc_spis_norm_h` (`docnum`) ON DELETE CASCADE, CONSTRAINT `0_8078` FOREIGN KEY (`doc_remon`) REFERENCES `doc_remon` (`docnum`) ON DELETE CASCADE ) TYPE=InnoDB
[14 May 2004 14:14]
Heikki Tuuri
Hi! You are probably trying to insert a duplicate primary key. INSERT INTO doc_oper_h (docnum,docnum1,date,wp,sum_,variant,doc_mon51) VALUES (119120,'033','2004-02-10',11,42200.00,1,19809) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 11225 n bits 112 table stn/doc_oper_h index PRIMARY trx id 0 17005687 lock mode S locks rec but not gap waiting Record lock, heap no 45 RECORD: info bits 0 0: len 4; hex 0001d150; asc ...P;; 1: len 6; hex 0000010378d0; asc ....x.;; You have set the value of docnum, it is not generated by InnoDB. Above we see that it is waiting for an S-lock on primary key value hex 0001d150. *** (2) TRANSACTION: TRANSACTION 0 17004752, ACTIVE 3 sec, process no 2285, OS thread id 1212449 setting auto-inc lock, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 32 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 65, query id 224357 snab_sklad 10.10.10.63 stn update insert into doc_oper_h (docnum1,date,wp,sum_,variant,doc_zex) values (" 259.0 ","2004-4-14",6,834,3,8473) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 11225 n bits 112 table stn/doc_oper_h index PRIMARY trx id 0 17004752 lock_mode X locks rec but not gap Record lock, heap no 45 RECORD: info bits 0 0: len 4; hex 0001d150; asc ...P;; 1: len 6; hex 0000010378d0; asc ....x.;; Transaction 2 has an X-lock on the same row. Probably 2 has inserted that row. If all the primary key values would be generated by InnoDB as auto-inc column values, then this deadlock would not happen. Regards, Heikki