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:
None 
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
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
[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