SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Corner seat
    Posts
    1,069
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A bunch of "lock" entries

    MySQL is causing me trouble lately, and as I check log files, I see many "lock" entries looking like the following:

    lock: 1234abc:

    q1. Is this a bad sign?

    q2. If so, what can I do to not let this happen?

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Australia
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Locks occurr when someone is trying to read/write to a table at the same time as someone else. In low use systems (circa 5 or less simultaneous users) this should not happen unless you have a poorly designed database.

    Symptoms of a poorly designed system are:

    Design not in 3NF...3NF can be overkill but all designs should at least be 2.5NF. That is 2NF with the most heavily utilised sections in 3NF. For more info on normal form do a google search for "third normal form" or pick up a database design book.

    Lack of indexes, no indexes at all or poorly designed indexes. This is where you can do a quick and dirty fix. Indexes allow the database to find the row it is looking for without scanning the entire table, which, coincidently causes table locks. Look at the queries causing the locks, specifically the WHERE clause. Now create an index that is composed of all the columns in your WHERE clause. If multiple WHERE clauses are causing a problem you may need to make more indexes composed of each of the columns in the WHERE clauses.

    Disclaimer: I'm no DB guru but having to deal with tables with thousands of rows and many simultaneous users forces you to design your databases more effectively.

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Corner seat
    Posts
    1,069
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ultk
    Design not in 3NF...3NF can be overkill but all designs should at least be 2.5NF. That is 2NF with the most heavily utilised sections in 3NF. For more info on normal form do a google search for "third normal form" or pick up a database design book.
    Can you possibly describe what "third normal form" in 3 - 4 sentences? If not, it's ok.
    Quote Originally Posted by ultk
    Lack of indexes, no indexes at all or poorly designed indexes. This is where you can do a quick and dirty fix. Indexes allow the database to find the row it is looking for without scanning the entire table, which, coincidently causes table locks. Look at the queries causing the locks, specifically the WHERE clause. Now create an index that is composed of all the columns in your WHERE clause. If multiple WHERE clauses are causing a problem you may need to make more indexes composed of each of the columns in the WHERE clauses.
    How can I find queries that are causing the locks?
    Quote Originally Posted by ultk
    Disclaimer: I'm no DB guru but having to deal with tables with thousands of rows and many simultaneous users forces you to design your databases more effectively.
    To me, you are a guru ...

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Australia
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I could try to explain 3NF in a few simple sentences but i'd make a gaff of it. There are some great explanations out there already that have been well thought out.

    There's a good series on normalisation on about.com. Remember you don't need to go the whole hog and make everything 3NF it's usually totally unneccessary.

    http://databases.about.com/od/specif...malization.htm

    As for finding the offending queries. What are the most commonly used queries? If there is a web application running against it do you have access to the code to find the SELECT statements?

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Corner seat
    Posts
    1,069
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ultk
    As for finding the offending queries. What are the most commonly used queries? If there is a web application running against it do you have access to the code to find the SELECT statements?
    Honestly, I don't know what queries are the most commonly used or least used. I run two php applications:

    phpBB (which you probably know a lot about) and
    Websight

    I run jsp application, but it's totally custom.


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
  •