Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Unanswered: Deadlock for starter, simple scenario

    Hi, this is my first post and probably not the last one.

    I'm trying to use a database managed by MySQL 4.1. In my scenario there are two tables, with a many to one relationship. The table with the foreign key (the "many" side) is huge, and has a text column that makes de database quite slow when doing some operations.

    My problem is: I find a deadlock quite often when trying two operations at the same time. Now, if it were just a lock timeout I wouldn't worry, but I don't understand this deadlock.

    This happens with the following scenario:

    1 Let B be the table on the "many" side, so it would be something like B*->1A. B has a "descriptionId" which is the foreign key to A's id. On delete it will cascade, so when deleting from A I'd be deleting the proper records from B.
    2 Client C1 performs a delete from A. This takes some time.
    3 During the deletion, someone makes an insertion into A and B. The insertion in B is done with an insert...select, and it's quite long too.
    4 Each client started a transaction first of all.
    5 I'm using InnoDB tables.


    At some point during both operations, there is a deadlock.

    Now, I thought the delete would possibly take a lock on the whole tables, so the insertion would fail because of the timeout, without deadlock. As this was not the case, I tried:

    first a select ... for update from A, and then from B (using the foreign key) before starting the deletion, so I would have it in advance.

    I tried a Lock tables a write,b write before starting to delete, and the same before starting the insert ... select.

    In both cases the outcome was the same.

    Which commands should I try in order to get an insight on how are they getting into a deadlock?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi, I know that you are on an old version of MySQL from version 5.1 there is a new feature called PARTITIONS which is extremely useful for storing large amounts of data and in particular removing them later on. A partition can be viewed as a bucket in which you can place information. If all the information you need to remove exists in a single partition then you simply DROP the partition. This take miliseconds to complete. Likewise if you are adding information this may occur in a separate partition which avoids deadlock situations.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2010
    Posts
    3
    Hello, and thank you for the answer.

    Indeed it's an old version, but it's not my choice, so for now I'm stuck with this. However, I'll have a look into partitions just in case I'm facing the same problems in the future and I'm given the chance to choose the DBMS and its version.

  4. #4
    Join Date
    Apr 2010
    Posts
    3
    Ok, after reading about partitioning it seems it will be easy to do manually, so you've solved my problem.

    Many thanks to you!!

Posting Permissions

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