| 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: | |
| 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: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".
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.