Hi Friends,

Yesterday we had a deadlock in one of our application which was running on mysql (Live since last 7 year and this was the first time this issue came up) and I've never worked on mysql and in our company we don't have a mysql dba as such. So the developers were seeking some inputs from me. Now I'll come on to the issue.

We've two tables here which were involved in deadlock. say t1 & t2, t2 had composite clustered pk with three columns of int datatype and t1 was the child table and all the thre columns are in t1 as fk.

So in one of the scenario there will be a record inserted in child (t1) and then a record will be updated in parent (t2) on the basis on pk columns... now this update statement was detected as deadlock and roll backing... now after doing some R& D I asked the dev team to change the order as in place of insert & update, I asked them to update the parent first and then insert into child (Although these two records are not related)..... And the issue got resolved even when we did the load testing with 100 users doing the same thing... but earlier it was returning deadlock even for one user.... And both these update & insert were in same transaction.... And I was wondering why it was getting deadlock....

Any thoughts on this???

Rohit

Mysql by default uses repeateable read transaction,
so if you are using Innodb engine then there will be row based locking
so table will be locked when the updates will be going on and transaction is not commited or rollback.
and at that time there will be no inserts possible in this table hence will show lock wait time out error.