| 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 |
[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
Description: Sometimes I have deadlocks and lock wait errors when users attempt to insert into table with auto-incremental counter. It looks like InnoDB forget its auto-inc counter for some tables. How to repeat: I don't know how repeat. It's like this. Preparing table: create table tbl ( id int not null auto_increment, fld char(30), primary key (id) ) type=InnoDB; insert into tbl (fld)values('asda') insert into tbl (fld)values('asda') insert into tbl (fld)values('asda') insert into tbl (fld)values('asda') insert into tbl (fld)values('asda') Now if you restart server you can get deadlock TRX 1 begin; insert into tbl (fld)values('asda'); //Here it does initialization of counter and locks all whole table TRX 2 insert into tbl (fld)values('asda'); //waiting for lock TRX 1 insert into tbl (fld)values('asda'); // Because of Bug #1866 it cause the following deadlock TRX 2 Connectivity error: [TCX][MyODBC]Deadlock found when trying to get lock; This deadlock is described. But I have such deadlocks twice a week WITHOUT RESTARTING SERVER. Suggested fix: It will be nice if InnoDB stored counters at disk