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.
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.