MySQL Bugs: #40737: Lock wait timeout exceeded during inserting row

Bug #40737	Lock wait timeout exceeded during inserting row
Submitted:	14 Nov 2008 12:14	Modified:	12 Aug 2009 21:43
Reporter:	NOT_FOUND NOT_FOUND	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.0.67	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In simplification is table a with create statement:
create table a 
(id int unsigned not null auto_increment,
id_foreign int unsigned not null,
a int,
Primary Key (id),
Key id_foreign(id_foreign),
Foreign Key (id_foreign) References parent(id) On Delete Cascade) Engine=Innodb;

Sometimes in application during insert statement e.g. insert into a values (null,20,1) (where id_foreign=20 is new max value for that column) MySQL gives "lock wait timeout exceeded" message. Application works with long time transactions, but every updates(deletes) on table a during transaction are done with search on id column - one number each time (it means there are updates with search on id_foreign column but after that transaction is immediately commited or rollbacked). Show Engine Innodb Status shows:
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 16394 n bits 1208 index `id_foreign` of table `testdb/a` trx id 0 172570 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
----------

There is no other statements locking rows in table a like "select ... for update" or "insert into ... select ..." and table a is not parent for any child in foreign constraints.
In application works many users.

How to repeat:
I can't repeat error (I located user which have locked insertion and I did the same things but error this time not occurred). It occurres when transaction modifies rows in table a with maximum id_foreign.
Sometimes many days everything works good.

Thank you for a problem report. Please, try to provide a repeatable test case. I need CREATE TABLE for parent table etc. 

Is it possible that two INSERTs into that a table, both with maximum id/foreign_id happens concurrently?

Hi Valeriy.

Below is full listing of create statements table a (in reality rozrachunki) and parent table (sprawy). Id_foreign in reality is id_sprawy.
Application works in that way:
To insert/update/delete rows in rozrachunki transaction starts and then "insert into sprawy values (null,.....)" or "select * from sprawy where id=x for update". Select on rozrachunki is without "for update". So only one transaction can do inserts/updates/deletes in rozrachunki with one id_sprawy (it is not possible two inserts from two transactions with the same id_sprawy). After all modifications user clicks save and transaction is commited.

In situation I have investigated (with lock wait time exceeded) user no.1 did:
insert into sprawy values (null,...);
then assign last_inset_id() to variable x and few inserts:
insert into rozrachunki (null,x,...);
commit;
Next started new transaction:
select * from sprawy where id=x for update;
select id,... from rozrachunki where id_sprawy=x and kod_etapu=y and data_zaplaty is null;
for each returnet row did updates:
update rozrachunki set kod_etapu=z where id=(id from previous select);
Next user did modifications on other tables related with sprawy and transaction was continues.
During that time another user have started transaction:
insert into sprawy values (null,...);
then assign last_inset_id() to variable x and when tried insert:
insert into rozrachunki (null,x,...);
get lock wait timeout exceeded.

Earlier updates on rozrachunki application did in one statement:
update rozrachunki set kod_etapu=z where id_sprawy=x and kod_etapu=y and data_zaplaty is null;
Then lock wait timeout exceeded was frequent and was logical (easy to reproduce). After modifications in code it should not appear.
Now there is only one update statement on rozrachunki searching id_sprawy:
update rozrachunki set data_powstania=x where id_sprawy=y and rodzaj_dok=z;
but it is done after user clicks save button and transaction after that is immediatelly commited (or rolled back in case of error).

I can't reproduce this error. I open many instances of application and do different scenarios, but error not happen. Only sometimes (rarely) users call and tell me about error.
Similar situation was on MySQL 4.1.22 and I hoped that upgrade will resolve problem.

Tables:

CREATE TABLE sprawy (
  id int(10) unsigned NOT NULL auto_increment,
  nr_ewid varchar(8) NOT NULL,
  nazwa varchar(255) NOT NULL,
  miejscowosc varchar(30) NOT NULL,
  ulica varchar(40),
  nr_domu varchar(12) NOT NULL,
  nr_lokalu varchar(10),
  kod varchar(6) NOT NULL,
  poczta varchar(30) NOT NULL,
  kto enum('indywidualny','firma lub instytucja') NOT NULL,
  plec enum('mężczyzna','kobieta','oboje','brak') NOT NULL,
  peselregon varchar(11),
  nip varchar(13),
  sygn_akt varchar(30),
  id_jednostki smallint(5) unsigned NOT NULL,
  sprzedawca enum('PGNiG','PGNiG do 2002') NOT NULL,
  id_kancelarii smallint(5) unsigned NOT NULL,  
  id_jednostki_prow smallint(5) unsigned NOT NULL,
  u_prow smallint(5) unsigned NOT NULL,
  kod_etapu smallint(5) unsigned NOT NULL,
  PRIMARY KEY (id),
  KEY nr_ewid(nr_ewid),
  KEY id_jednostki (id_jednostki),
  KEY id_kancelarii (id_kancelarii),
  KEY id_jednostki_prow (id_jednostki_prow),
  KEY u_prow (u_prow),
  KEY kod_etapu(kod_etapu),
  FOREIGN KEY (id_jednostki) REFERENCES jednostki(id),
  FOREIGN KEY (id_kancelarii) REFERENCES kancelarie(id),
  FOREIGN KEY (id_jednostki_prow) REFERENCES jednostki(id),
  FOREIGN KEY (u_prow) REFERENCES uzytkownicy(id),
  FOREIGN KEY (kod_etapu) REFERENCES etapy (kod)
) ENGINE=InnoDB;

CREATE TABLE rozrachunki (
  id int unsigned NOT NULL auto_increment,
  id_sprawy int unsigned NOT NULL,
  kwota decimal(10,2) NOT NULL,
  kwota_oryg decimal(10,2) NOT NULL,
  rodzaj_dok enum ('faktura','nota odsetkowa','naliczone odsetki','koszty sądowe','zastępstwo procesowe') NOT NULL,
  numer_dok varchar(10) NOT NULL,
  data_dok date NOT NULL,
  termin_pl date NOT NULL,
  data_nal_ods date NOT NULL,
  data_powstania date NOT NULL,
  data_zaplaty date,
  kod_etapu smallint(5) unsigned NOT NULL,
  id_oryg_rozrach int unsigned,
  PRIMARY KEY (id),
  KEY id_sprawy(id_sprawy),
  KEY kod_etapu(kod_etapu),
  KEY id_oryg_rozrach(id_oryg_rozrach),
  FOREIGN KEY (id_sprawy) REFERENCES sprawy(id) ON DELETE CASCADE,
  FOREIGN KEY (kod_etapu) REFERENCES etapy (kod)
) ENGINE=InnoDB;

thanks for quick reply and sorry for my english

Thank you for the feedback.

Could you please get output of SHOW PROCESSLIST in time when lock occurs?

It may be hard to do, because I don't know when lock will occure next time, but I think log from "show engine innodb status" gives similar information:

---TRANSACTION 0 172314, ACTIVE 513 sec, process no 2032, OS thread id 116458118
4 inserting
mysql tables in use 1, locked 1
LOCK WAIT 20 lock struct(s), heap size 3024, undo log entries 5
MySQL thread id 4662, query id 526698 gd-hp13.gdansk.psgaz.pl 10.172.2.15 egzeku
torpro update
insert into rozrachunki (id, id_sprawy, kwota, kwota_oryg, rodzaj_dok, numer_dok
, data_dok, termin_pl, data_nal_ods, data_powstania, data_zaplaty, kod_etapu) va
lues (NULL, 3459, 36.27, 71.27, 'faktura', '5020078695', '2007-08-16', '2007-09-
03', '2007-09-03', '2008-10-16', NULL, 1)
Trx read view will not see trx with id >= 0 172315, sees < 0 170679
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 16394 n bits 1208 index `id_sprawy` of table `eg
zekutorpro/rozrachunki` trx id 0 172314 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------

Thank you for the feedback.

If I understood correctly there are parallel SELECTs while you do problem INSERT. Which TRANSACTION ISOLATION LEVEL do you use?

Yes, there are parallel selects, inserts and updates with autocommit=0 and long time transactions. Transaction isolation level is repeatable read.

Thank you for the feedback.

If return back to your comment "[14 Nov 22:56] Xxx Xxx":

> Next started new transaction:
> select * from sprawy where id=x for update;
...
> During that time another user have started transaction:
> insert into sprawy values (null,...);
> then assign last_inset_id() to variable x and when tried insert:
> insert into rozrachunki (null,x,...);

rozrachunki has FOREIGN KEY (id_sprawy) REFERENCES sprawy(id). So under some circumstances you can find situation select * from sprawy where id=x for update; with update rozrachunki set kod_etapu=z where id=(id from previous select); block insert into rozrachunki (null,x,...); in other transaction.

For example with following test case:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
create table t0(id int not null auto_increment primary key, f1 int, key(f1)) engine = innodb;
create table t1(id int not null auto_increment primary key, f1 int, f2 int not null, key(f1), key(f2), FOREIGN KEY (f2) REFERENCES t0(id)) engine = innodb;
insert into t0 values(1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8);
insert into t1 (f1, f2) values(1,1), (2,2), (3,3);

SESSION 1:
begin;
select id from t0 where id = 1 for update;
id
1
update t1 set f1 = 33 where f2 = 1;
SESSION 2:
insert into t0 values(null, 9);
select last_insert_id() into @foo;
select @foo;
@foo
9
insert into t1 values(null, 4, @foo);

But if
SESSION 1:
begin;
select id from t0 where id = 1 for update;
id
1
update t1 set f1 = 33 where f2 = 3;

Last INSERT in SESSION 2 fails.

So I close the report as "Not a Bug"

> SESSION 1:
> begin;
> select id from t0 where id = 1 for update;
> id
> 1
> update t1 set f1 = 33 where f2 = 1;
> SESSION 2:
> insert into t0 values(null, 9);
> select last_insert_id() into @foo;
> select @foo;
> @foo
> 9
> insert into t1 values(null, 4, @foo);
> 
> But if
> SESSION 1:
> begin;
> select id from t0 where id = 1 for update;

I understand that in row above should be id=3

> id
> 1
> update t1 set f1 = 33 where f2 = 3;
> 
> Last INSERT in SESSION 2 fails.

The sample above of course will lock inserts, but in my application updates are mainly done in different way:
select id from t1 where f2=3;
and then for each returned id:
update t1 set f1=33 where id=x (x - returned from previous select)

in other cases when update is done like:
update t1 set f1 = 33 where f2 = 3;
transaction is immediately commited (or rolled back in case of error)

Thank you for the feedback.

So you say you issue SELECT, but not SELECT ... FOR UPDATE and get lock? I can not repeat this with test data.

So, please, try to find which exact queries you issue in the same time (as INNODB STATUS does not say this).

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi.

I can now provide test. It is not repeatable in 100%, because behavior of MySQL is very strange in this case. In my application I can provide now repeatable test, but in simulation it sometimes works, sometimes not. It is very strange because if I type in MySQL client commands by hand sometimes lock wait timeout occures and sometimes not. If I paste all commands from last block (third block) lock wait timeout always occures. Transaction isolation level in application was repeatable read and then error happens. Now I use read committed and error has disappeared (in discovered situation) and I hope it will work in whole application. In test I have changed isolation level but it not work like in application.

Below are 2 tests. First is simplification of application and second is very simple test but it works too.

First test

Session 1:
set transaction isolation level repeatable read;
set autocommit=0;

CREATE TABLE a (
  id mediumint unsigned NOT NULL auto_increment,
  a varchar(10),
  PRIMARY KEY  (id)
) ENGINE=InnoDB;

CREATE TABLE b (
  id mediumint unsigned NOT NULL auto_increment,
  id_a mediumint unsigned NOT NULL,
  b varchar(10),
  PRIMARY KEY (id),
  UNIQUE KEY id_a (id_a),
  FOREIGN KEY (id_a) REFERENCES a (id)
) ENGINE=InnoDB;

CREATE TABLE c (
  id mediumint unsigned NOT NULL auto_increment,
  id_b mediumint unsigned NOT NULL,
  c varchar(10),
  PRIMARY KEY (id),
  KEY id_b (id_b),
  FOREIGN KEY (id_b) REFERENCES b (id) ON DELETE CASCADE
) ENGINE=InnoDB;

insert into a values (null,'a1'); //last_insert_id()=1
insert into a values (null,'a2'); //last_insert_id()=2
commit;

select * from a where id=1 for update;
insert into b values (null,1,'b1'); //last_insert_id()=1

Session 2:
select * from a where id=2 for update;
insert into b values (null,2,'b2'); //last_insert_id()=2

Session 1 (paste all commands into client because if typed by hand sometimes error occures sometimes not):
insert into c values (null,1,'c1');
commit;
select * from a where id=1 for update;
delete from b where id=1;
commit;
select * from a where id=1 for update;
insert into b values (null,1,'b1');
(and now should lock wait timeout appear)

Second test

Session 1:
set transaction isolation level repeatable read;
set autocommit=0;
CREATE TABLE a (
  id mediumint unsigned NOT NULL auto_increment,
  id_a mediumint unsigned NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY id_a (id_a)
) ENGINE=InnoDB;

insert into a values(null,1); //last_insert_id()=1

Session 2:
set transaction isolation level repeatable read;
set autocommit=0;
insert into a values(null,2); //last_insert_id()=1

Session 1:
commit;
delete from a where id=1;
commit;
insert into a values(null,1);

I hope You will find out what is wrong.

with regards
Przemyslaw Malek

Thank you for the feedback.

You have additional UNIQUE key in both problem tables. This is why you get "Lock wait timeout" error. Please see http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html and http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html for details.