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?

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.

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.

Ok, after reading about partitioning it seems it will be easy to do manually, so you've solved my problem.

Many thanks to you!!