MySQL ver: 5.0.51a
Mem: 16G/32G
CPU: 2 x Quad Core Xeon
OS: RHEL 5
Occasionally we see a locked-up scenario on our servers. It starts with a few locked tables, then the number keeps growing, until it reaches the max_connections.
Looking into the queries, it does not seem to have any deadlock. The queries are either inserts or updates, to tables from different databases, without competing for resources.
Any idea what could be the problem? This has annoyed us for quite a while. All tables are using InnoDB.
Thanks!
PS: here is a sample result from “show processlist” when the lockup happens.
| 10311326 | app | web1:39980 | app | Query | 95 | Locked | UPDATE D328.activity SET action= ‘true’ WHERE id=‘178731’ |
| 10311328 | app | web1:39982 | app | Query | 95 | Locked | INSERT INTO D197.activity (userID,deptID,salesID,nextContact,action,message ,ca | | 10311343 | app | web1:39998 | app | Query | 94 | Locked | INSERT INTO D101.activity (userID,deptID,salesID,nextContact,action,message ,ca |
| 10311352 | app | web1:40016 | app | Query | 93 | Locked | UPDATE D863.activity SET message=CONCAT_WS(‘’,message,'\ |
| 10311362 | app | web1:40030 | app | Query | 92 | Locked | INSERT INTO D734.activity (userID,deptID,salesID,nextContact,action,message ,ca | | 10311368 | app | web2:52174 | app | Query | 91 | Locked | UPDATE D399.activity SET action= 'true' WHERE id='110776' | | 10311369 | app | web1:40040 | app | Query | 91 | Locked | UPDATE D287.user SET Date= '1209670895' WHERE id='11875' AND responseDate=0 | | 10311384 | app | web1:40060 | app | Query | 87 | Locked | INSERT INTO D863.activity (userID,deptID,salesID,nextContact,action,message ,ca |
| 10311418 | app | web1:40114 | app | Query | 84 | Locked | INSERT INTO D925.activity (salesID,userID,deptID,type,day,message,reason ,date) V |
| 10311427 | app | web2:52202 | app | Query | 83 | Locked | UPDATE D917.activity SET action= ‘1209670903’ WHERE id=‘110472’ |
| 10311431 | app | web1:40136 | app | Query | 81 | Locked | INSERT INTO D197.activity (salesID,userID,deptID,type,day,message,reason ,date) V |
| 10311433 | app | web1:40140 | app | Query | 81 | Locked | INSERT INTO D734.activity (userID,deptID,salesID,nextContact,action,message ,ca | | 10311435 | app | web1:40142 | app | Query | 80 | Locked | INSERT INTO D970.activity (deptID,userID,salesID,folder,date,type,message ) VALUES | | 10311437 | app | web1:40146 | app | Query | 80 | Locked | INSERT INTO D951.activity (userID,deptID,salesID,nextContact,action,message ,ca |
| 10311446 | app | web1:40163 | app | Query | 78 | Locked | INSERT INTO D567.activity (salesID,userID,deptID,type,day,message,reason ,date) V |
| 10311453 | app | web1:40167 | app | Query | 77 | Locked | INSERT INTO D919.activity (userID,deptID,salesID,nextContact,action,message ,ca | | 10311454 | app | web2:52218 | app | Query | 77 | Locked | UPDATE D98.activity SET action= 'true' WHERE id='1018641' | | 10311493 | app | web1:40223 | app | Query | 67 | Locked | UPDATE D101.activity SET message= | | 10311511 | app | web1:40242 | app | Query | 65 | Locked | UPDATE D744.activity SET action= 'true' WHERE id='186131' | | 10311514 | app | web2:44165 | app | Query | 63 | Locked | INSERT INTO D197.user (first,last,email,email1,dPhone,city,state,zi p,country,pur |
| 10311516 | app | web1:40248 | app | Query | 65 | Locked | INSERT INTO D444.user (status,first,middle,last,address,city,state, zip,nickname, | | 10311534 | app | web1:40276 | app | Query | 64 | Locked | INSERT INTO D970.activity (deptID,salesID,userID,type,day,date,folder,e mailTo,e |
| 10311560 | app | web1:40314 | app | Query | 62 | Locked | INSERT INTO D824.activity (userID,deptID,salesID,nextContact,action,message ,ca | | 10311565 | app | web1:40324 | app | Query | 61 | Locked | INSERT INTO D101.activity (userID,deptID,salesID,nextContact,action,message ,ca |
| 10311605 | app | web2:52298 | app | Query | 55 | Locked | UPDATE D270.activity SET action= ‘true’ WHERE id=‘475931’ |
| 10311670 | app | web1:40490 | app | Query | 45 | Locked | UPDATE D962.activity SET action= ‘true’ WHERE id=‘57391’ |
| 10311683 | app | web2:52330 | app | Query | 44 | Locked | UPDATE D308.activity SET action= ‘true’ WHERE id=‘200661’ |
| 10311686 | app | web1:40518 | app | Query | 43 | Locked | INSERT INTO D718.activity (testDrive,userID,deptID,salesID,nextContact,acti on,| | 10311687 | app | web2:52332 | app | Query | 42 | Locked | UPDATE D716.activity SETaction= 'true' WHERE id='40591' | | 10311695 | app | web1:40532 | app | Query | 40 | Locked | INSERT INTO D566.activity (testDrive,userID,deptID,salesID,nextContact,acti on, |
| 10311712 | app | web1:40548 | app | Query | 38 | Locked | INSERT INTO D734.activity (deptID,salesID,userID,type,day,date,folder,e mailTo,e | | 10311742 | app | web1:40604 | app | Query | 34 | Locked | UPDATE D744.activity SET action= 'true' WHERE id='186131' | | 10311746 | app | web1:40608 | app | Query | 33 | Locked | UPDATE D970.activity SET action= 'true' WHERE id='738911' | | 10311771 | app | web1:40646 | app | Query | 28 | Locked | INSERT INTO D101.activity (deptID,salesID,userID,type,day,date,folder,e mailTo,e |
| 10311821 | app | web1:40726 | app | Query | 24 | Locked | INSERT INTO D1101.activity (vehicleID,userID,deptID,salesID,type,message,d ay,date | | 10311825 | app | web1:40732 | app | Query | 24 | Locked | INSERT INTO D419.user | | 10311856 | app | web1:40792 | app | Query | 20 | Locked | INSERT INTO D970.activity (deptID,salesID,userID,type,day,date,folder,e mailTo,e |
| 10311859 | app | web1:40798 | app | Query | 19 | Locked | INSERT INTO D956.activity (testDrive,userID,deptID,salesID,nextContact,acti on,| | 10311888 | app | web1:40846 | app | Query | 17 | Locked | UPDATE D882.activity SETaction= 'true' WHERE id='543171' | | 10311889 | app | web1:40848 | app | Query | 17 | Locked | INSERT INTO D905.activity (userID,deptID,salesID,nextContact,action,message ,ca |
| 10311944 | app | web1:40928 | app | Query | 12 | Locked | INSERT INTO D591.activity (day,date,userID,salesID,type,deptID,subjec | | 10311946 | app | web1:40932 | app | Query | 12 | Locked | INSERT INTO D487.activity (userID,deptID,salesID,nextContact,action,message ,ca |
| 10311973 | app | web1:40983 | app | Query | 7 | Locked | UPDATE D101.activity SET read= ‘true’ WHERE id=‘1864971’ |
| 10311978 | app | web1:40995 | app | Query | 6 | Locked | INSERT INTO D566.activity (deptID,salesID,userID,type,day,date,folder,e | | 10311993 | app | web1:41017 | app | Query | 5 | Locked | UPDATE D744.activity SET action= 'true' WHERE id='186131' | | 10312008 | app | web1:41041 | app | Query | 2 | Locked | UPDATE D938.activity SET action= 'true' WHERE id='990651' | | 10312012 | app | web1:41047 | app | Query | 1 | Locked | UPDATE D525.activity SET action`= ‘true’ WHERE id=‘269341’ |