Bug #40147 InnoDB: All tables / databases Lock / Global Lock ??? - Please help
Submitted: 19 Oct 2008 17:29 Modified: 21 Nov 2008 7:12
Reporter: Vith Pinthapataya Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.18 OS:Linux (SuSe 10.1)
Assigned to: CPU Architecture:Any
Tags: database lock, innodb, table lock

[19 Oct 2008 17:29] Vith Pinthapataya
Description:
I am running into a problem that at some point, all tables (in same or difference databases ) were locaked up. After wait for sometime, all clients started to get an error "Lock wait timeout exceeded; Try restarting transaction".

I had to restart mysql server to free up the lock.
Too bad, I did not run "SHOW INNODB STATUS". I only have an output from mytop and you can see that INSERT/UPDATE statements are in difference tables ( and difference databases ).

It seems to be me that Mysql/InnoDb have a global lock or something?

By the way, we have an autocommit on for mysql tool. All of our applications access mysql thru a lib function. The lib always issue "COMMIT" after every SQL command requests (insert/delete/update). We do NOT use "BEGIN" or "LOCK" commands in our system. 

MyTop(when mysql/innodb locked up)
=====
MySQL on localhost (5.0.18 log)                                                                       up 4+14:28:54 [01:38:32]
 Queries: 18.9M  qps:   50 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00
             qps now:   14 Slow qps: 0.0  Threads:   87 (  17/   0) 00/00/00/00
 Key Efficiency: 90.0%  Bps in/out:   0.0/  0.4   Now in/out:  20.7/ 7.1k

6052 mandateIP dtc-tote-mgr:38    MANDATE       237  Query insert into EVENT ( eventType, locId, loginName, textData) value
6033 mandateIP dtc-sorter-mgr:    MANDATE      1645  Query insert into EVENT ( eventType, locId, loginName, textData) value
 204 mandateIP dtc-order-mgr:4    AEO_DTC      1914  Query INSERT INTO ReleaseHistory ( eventType, unocc, totalFac, priori
  28 mandateIP dtc-tote-mgr:43    AEO_DTC      1980  Query update Tote set lastSeenLoc = 'LEI_Scanner', timeSortScan = now
4588 mandateIP dtc-spk-mgr:526    AEO_DTC      1981  Query INSERT INTO SpeedPackOrder ( speedPackWS, prStation, controlNum
5937 mandateIP dtc-pick-mgr:47    MANDATE      2012  Query insert into EVENT ( eventType, locId, loginName, textData) value
6012 mandateIP dtc-spk-mgr:573    MANDATE      2013  Query insert into EVENT ( eventType, locId, loginName, textData) value
6013 mandateIP dtc-order-mgr:3    MANDATE      2013  Query insert into EVENT ( eventType, locId, loginName, textData) value
  27 mandateIP dtc-tote-mgr:43    AEO_DTC      2045  Query insert into ToteHistory set tote = '00900777770000140584', usage
 203 mandateIP dtc-order-mgr:4    AEO_DTC      2046  Query INSERT into ShippingCarton ( status, shipVia, miscField2, divis
  87 mandateIP dtc-sorter-mgr:    AEO_DTC      2049  Query update PickDetail set status="On Sorter",cellId="374",desChute=
4509 mandateIP dtc-pick-mgr:37    AEO_DTC      2049  Query UPDATE PickDetail set pickTask = "10150104144974" , status = "P
4572 mandateIP dtc-spk-mgr:525    AEO_DTC      2049  Query UPDATE SpeedPackOrder set printStatus = 'Printed', controlNumbe
4608 mandateIP dtc-spk-mgr:526    AEO_DTC      2050  Query COMMIT

Sample of table:
================
DROP TABLE IF EXISTS `SpeedPackOrder`;
CREATE TABLE `SpeedPackOrder` (
  `speedPackWS` char(6) NOT NULL,
  `slotId` char(11) NOT NULL,
  `controlNumber` char(10) NOT NULL,
  `idxSlot` tinyint(4) NOT NULL,
  `printStatus` char(10) NOT NULL,
  `specialPack` char(10) NOT NULL,
  `status` char(10) NOT NULL,
  `flags` tinyint(4) default '0',
  `prStation` tinyint(4) default '0',
  `actTime` datetime NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `idx` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`idx`),
  UNIQUE KEY `speedPackWS` (`speedPackWS`,`controlNumber`,`slotId`),
  KEY `slotIdKey` (`slotId`),
  KEY `controlKey` (`controlNumber`),
  KEY `pkpstatusKey` (`speedPackWS`,`status`,`timestamp`),
  KEY `statusKey` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How to repeat:
We do not know how to repeat this however it continue to happen 4 times within 10 days.
[19 Oct 2008 17:32] Vith Pinthapataya
Output of SHOW VAR and HOW INNODB STATUS

Attachment: mysql.txt (text/plain), 24.23 KiB.

[19 Oct 2008 17:37] Valeriy Kravchuk
Please, try to repeat with smaller innodb_thread_concurrency, 4, for example (how many CPUd do you have?), and with a newer version, 5.0.67. Inform about the results.
[20 Oct 2008 16:04] Vith Pinthapataya
Thanks for fast response.

Would you mind describe what you think the problem may be? By changing innodb_thread_concurrency to 4, will this fix the problem? or will it repeat the problem so it can be fixed later? 

We have 4 CPUs on our customer server (currently having problem) however we can not play with the server since it is in production (they won't let me).
We have 2 CPUs on our lab which we can play with.

By the way, during the lock up(which cannot repeat-yet), all SELECT statments/queries were working just fine. I saw queries in and out (on mytop ) without any problems. Just those UPDATE/INSERT (diff tables/databases) that we had problem with and hung there. There was no DELETE at the time since we only do cleanup process between 3:00-3:59am.

Other note: we have innodb_thread_concurrency set to 20. I did run multi threads(2 - 32 ) to access database/tables at the same time. I do not see a big grep of time between number of threads. Not like a result from other that it would take hours to return a simple queries when having many clients access in multiple CPUs server.

Thanks, Vith
[21 Oct 2008 7:12] Sveta Smirnova
Thank you for the feedback.

Deadlock in most cases is not a bug. Please see about How to Cope with Deadlocks at http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html

You reported this problem as bug in MySQL software and haven't provided repeatable test case. If this is truly bug in MySQL software we must know if problem is repeatable in the latest version and if yes find a way to repeat it. So, please, follow Valeriy's instructions.
[22 Nov 2008 0:00] Bugs System
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".