GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

too many locked tables

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • too many locked tables

    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`,`nextCont act`,`acti on`,` |
    | 10311687 | app | web2:52332 | app | Query | 42 | Locked | UPDATE D716.activity SET `action`= 'true' WHERE id='40591' |
    | 10311695 | app | web1:40532 | app | Query | 40 | Locked | INSERT INTO D566.activity (`testDrive`,`userID`,`deptID`,`salesID`,`nextCont act`,`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`,`m essage`,`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`,`nextCont act`,`acti on`,` |
    | 10311888 | app | web1:40846 | app | Query | 17 | Locked | UPDATE D882.activity SET `action`= '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' |

  • #2
    Hello Zedude,

    have not a look at the processes in the state locked. show the others.

    Comment


    • #3
      There are a couple more processes... for replication. Nothing else.

      Comment


      • #4
        The processes are locked by another process. It should be one shown in the processlist.
        Is this a slave?

        Comment


        • #5
          The mysql slave process is updating different tables other than locked ones.

          The question is, why so many unrelated queries (they are inserts and/or updates to different tables in different databases) are locked? And the number of locked queries keeps increasing.

          Hopefully I've made myself clear...

          Thanks!

          Comment

          Working...
          X