Deadlock in MYSQL

Question

When performing transaction with large number of devices, deadlock error is occurring. I am using MySQL. Huge number of devices will access the database at the same time. This causes Error Code 1205.

Any suggestion...??

Answer 1

from the documentation:

Message: Lock wait timeout exceeded; try restarting transaction

this says it all. another qiestion is how to find out what's causing the timeout - but thats almost impossible to tell with the given information. you'll have to provide some numbers (how much is "a huge number"; how much (and wich) statements are executed in your transaction) and some information about your table-structures that are involved.

if there's no way to speed up your transaction any more, you could edit your my.cnf and increase the timeout (innodb_lock_wait_timeout) or, if you jst want to increase the timeout for this transaction, call SET innodb_lock_wait_timeout = 120; before starting it.

Answer 2

Here are a few places you can start:

• http://www.serveradminblog.com/2011/03/tuning-mysql-performance-howto-part-1/

• http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

• http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

Your question is hopelessly broad. You need to do some homework, and get some facts. It's hard work - but it's absolutely necessary.

IMHO...