August 20, 2010 at 12:58 am
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
August 20, 2010 at 1:51 am
This is a SQL Server forum, not a MySQL forum. I'd bet that most people here have minimal experience with MySQL. I suggest that you try asking this on a dedicated MySQL forum as you'll likely get far faster, far better options.
Try http://www.dbforums.com where there's a MySQL forum. Also maybe try http://forums.mysql.com which is entirely dedicated to MySQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 1:52 am
Rohit few things .....
First,
Ramji29 (8/20/2010)
I asked them to update the parent first and then insert into child (Although these two records are not related)
how ? as you told that there is pk-fk relation ship.
Second, Is the Fk is with CASCADE feature ? then only updation will be done on the basis if parent table's INsertion. other wise first parent table should be dealt then the children tables( as you did for deadlock resolution
Additionally, Deadlock is the worst stage of locking , so there is no guarantee that you will get is always.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 20, 2010 at 2:33 am
GilaMonster (8/20/2010)
This is a SQL Server forum, not a MySQL forum. I'd bet that most people here have minimal experience with MySQL. I suggest that you try asking this on a dedicated MySQL forum as you'll likely get far faster, far better options.Try http://www.dbforums.com where there's a MySQL forum. Also maybe try http://forums.mysql.com which is entirely dedicated to MySQL.
Thanks Gial for your response.... I undst this is a sqlserver forum but as this issues is more of concept and less rdbms dependent (as far as my knowledge) and it can even happen in sql server also... thats whay I posted it here.... I'll surely post it the forumns you've suggested.
Rohit
August 20, 2010 at 2:49 am
Ramji29 (8/20/2010)
I undst this is a sqlserver forum but as this issues is more of concept and less rdbms dependent (as far as my knowledge) and it can even happen in sql server also
That's true, but locking granularities and durations aren't going to be the same between the DB engines, and the way that one debugs a deadlock on SQL Server will be completely different to how one debugs a deadlock on MySQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 3:34 am
Thanks a lot Bhuvenesh & Gila for youe suggestions & comments....
Bhuvnesh (8/20/2010)
Rohit few things .....First,
Ramji29 (8/20/2010)
I asked them to update the parent first and then insert into child (Although these two records are not related)how ?
Sorry my mistake....
Actually the parent table maintains active record and child table is for history now if any record is updated then a record is inserted in child for an existing record of parent and then same record is updated with new startime & endtime in parent.
Bhuvnesh (8/20/2010)
Second, Is the Fk is with CASCADE feature ? then only updation will be done on the basis if parent table's INsertion. other wise first parent table should be dealt then the children tables( as you did for deadlock resolutionAdditionally, Deadlock is the worst stage of locking , so there is no guarantee that you will get is always.
No we don't have cascase option enabled and we're inserting in child and then updating parent... I suggested the opther way....
Bhuvnesh (8/20/2010)
Additionally, Deadlock is the worst stage of locking , so there is no guarantee that you will get is always.
Actually once this issue was reported then on test machine even with load of one user i.e. noone was accessing th db.... then also while doing this we got deadlock error.... and once we changed the logic then we tried with 5 users initally then even 100 users load doing the same thing it was running fine....
Alhotugh it has been fixed... but I was just looking for fellow forum member's valuable advice on this.... as if I might have to look on anything else also....
Tell me if you need any more inoputs on this.....
Rohit
August 20, 2010 at 3:39 am
Thanks Rohit, Well i don't think changing sequence avoided this deadlock.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply