views:

30

answers:

1

I have heard about this problem and now I am looking for more specific information?

How does it happens, what are the reasons for that, detailed explanation of the mechanism of the deadlock to try to avoid it. How to detect the deadlock, solve it and protect the data from being corrupted because of it. The case is when using MySQL with PHP.

And can I mix the InnoDB and MyISAM? I intend to use innoDB for some majo rtables with many relationships and not that much data, as users, roles, privileges, companies, etc. and use MyISAM for tables that hold more data: customers data, actions data, etc. I would like to use only InnoDB, but the move from MyISAM scares me a bit in terms of speed and stability. And now this deadlocks :(

+1  A: 

Deadlocks can occur if you've got two or more independent queries accessing the same resources (tables/rows) at the same time. A real world example:

Two mechanics are working on two cars. At some point during the repair, they both need a screwdriver and a hammer to loosen some badly stuck part. Mechanic A grabs the screwdriver, Mechanic B grabs the hammer, and now neither can continue, as the second tool they need is not available: they're deadlocked.

Now, humans are smart and one of the mechanics will be gracious and hand over their tool to the other: both can continue working. Databases are somewhat stupid, and neither query will be gracious and unlock whatever resource is causing the deadlock. At this point, the DBMS will turn Rambo and force a roll back (or just kill) one or more of the mutually locked queries. That will let one lucky query continue and proceed to get the locks/transactions it needs, and hopefully the aborted ones have smart enough applications handling them which will restart the transactions again later. On older/simpler DBMSs, the whole system would grind to a halt until the DBA went in and did some manual cleanup.

There's plenty of methods for coping with deadlocks, and avoiding them in the first place. One big one is to never lock resources in "random" orders. In our mechanics' case, both should reach for the screwdriver first, before reaching for the hammer. That way one can successfully working immediately, while the other one will know he has to wait.

As for mixing InnodB/MyISAM - MySQL fully supports mixing/matching table types in queries. You can select/join/update/insert/delete/alter in any order you want, just remember that doing anything to a MyISAM table within an InnoDB transaction will NOT make MyISAM magically transaction aware. The MyISAM portions will execute/commit immediately, and if you roll back the InnoDB side of things, MyISAM will not roll back as well.

The only major reason to stick with MyISAM these days is its support for fulltext indexing. Other than that, InnoDB will generally be the better choice, as it's got full transaction support and row-level locking.

Marc B
Thank you that was a good answer, but how to prevent deadlocks in the application? Any PHP example will be useful. Will I get deadlock if I simple execute some updates, inserts, selects and deletes at the same time without actually locking any table from the script? I mean just simple queries, no LOCK TABLE ot stuff like that.
Yasen Zhelev
One more question. Could a deadlock happen only while 2 transactions are accessing the same row(s) simultaniously and the have locked resourses that they need to finish the transaction. I mean that I do not see how a simple, atomic queries can cause a deadlock. Am I right?
Yasen Zhelev
+1 nice answer.
nikic
If there's no locks, then there can't be a dead*LOCK*. However, if both queries are working on the same rows, then it's a race condition, and you could end up with inconsistent results, depending on what order the queries are actually executed.
Marc B