Unable to add an index on a field with InnoDB?

Question

hey all, I have an innodb table that I'm trying to run an index on and I'm getting the following error:

Deadlock found when trying to get lock; try restarting transaction

The syntax is:

ALTER TABLE mytable ADD INDEX (fieldtoindex);

any ideas as to why that would throw a deadlock error? thanks!

Answer 1

You are not providing enough information to answer your question.

With InnoDB, as a general rule, when there is an error, have a look at the output of SHOW ENGINE INNODB STATUS. You want to run this in the command line client, and end the command with \G (backslash upper case G) to make the output readable.

In your case, the relevant information will be at the top of the SHOW ENGINE INNODB STATUS output, where it says something like

------------------------
LATEST DETECTED DEADLOCK
------------------------
100720 14:27:32
*** (1) TRANSACTION:
TRANSACTION 3 572739397, ACTIVE 2 sec, process no 3057, OS thread id 1362831680 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1216, 3 row lock(s)
MySQL thread id 37496388, query id 1645924019 host 10.147.209.20 db Updating
update t set aid='4921978'  where id in (302885,303582,303907,303911)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 36004 page no 147 n bits 216 index `PRIMARY` of table `db`.`t` trx id 3 572739397 lock_mode X locks rec but not gap waiting
Record lock, heap no 43 PHYSICAL RECORD: n_fields 19; compact format; info bits 0

This lists the relevant table and index names, as well as the statements involved. With this information your problem becomes debuggable.

See also http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ for more information.

If that happens more often, have a look at http://freshmeat.net/projects/innotop, "D" screen (deadlock display).