SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Table/database lock

    I'm finding that my database tables occasionally seem to get completely locked. I have user visit logging tables which are fairly busy, and the first to cause errors if anything's locked. I've taken snapshots of the show processlist, and I can't see why they're locked:
    Code:
    12535027        dbuser 192.168.100.001:41694   mydb Query   152     Locked  INSERT INTO mytable.....
    12535028        dbuser 192.168.100.001:41697   mydb Query   149     Locked  INSERT INTO mytable.....
    12535030        dbuser 192.168.100.001:41703   mydb Query   146     Locked  INSERT INTO mytable.....
    12535036        dbuser 192.168.100.001:41733   mydb Query   126     Locked  INSERT INTO mytable.....
    12535039        dbuser 192.168.100.001:41740   mydb Query   122     Locked  INSERT INTO mytable.....
    12535045        dbuser 192.168.100.001:41756   mydb Query   112     Locked  INSERT INTO mytable.....
    12535046        dbuser 192.168.100.001:41758   mydb Query   111     Locked  INSERT INTO mytable.....
    12535047        dbuser 192.168.100.001:41759   mydb Query   111     Locked  INSERT INTO mytable.....
    12535052        dbuser 192.168.100.001:41773   mydb Query   96      Locked  INSERT INTO mytable.....
    12535054        dbuser 192.168.100.001:41775   mydb Query   90      Locked  INSERT INTO mytable.....
    12535074        dbuser 192.168.100.001:41799   mydb Query   35      Locked  INSERT INTO mytable.....
    If all of the queries are locked and none of them are running, what's causing the lock? How would I see this?

  2. #2
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Are these table MyISAM or InnoDB?

    If they are MyISAM then any reason why they could not be changed to InnoDB?

    Although this is a very general comment and your locking problems could be something very different - your post does not give much to go on - InnoDB does not suffer from the same database locking problems that MyISAM can.

    Steve
    ictus==""

  3. #3
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yes, I'm aware there's not much to go on, but I wasn't sure what else would be useful - I'm a bit stumped here!
    The tables locked here are InnoDB.

  4. #4
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi

    SHOW ENGINE INNODB STATUS

    Can be used to inspect any deadlock reasons. Maybe this can help diagnose what type or why the lock exists?
    ictus==""

  5. #5
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    I don't know if this will help http://www.xaprb.com/blog/2007/09/18...db-lock-waits/ ?

    Steve
    ictus==""

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Is it just one query that is having the issues with deadlocking or are all queries having trouble?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •