Results 1 to 2 of 2

Thread: Deadlock

  1. #1
    Join Date
    Aug 2010
    Posts
    1

    Unanswered: Deadlock

    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

  2. #2
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by rohit2900 View Post
    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.

Posting Permissions

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