无法使用 InnoDB 在字段上添加索引？

发布于 2024-09-10 16:50:56 字数 252 浏览 8 评论 0原文

大家好，我有一个 innodb 表，我试图在其上运行索引，但收到以下错误：

Deadlock found when trying to get lock; try restarting transaction

语法是：

ALTER TABLE mytable ADD INDEX (fieldtoindex);

关于为什么会引发死锁错误的任何想法？谢谢！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

盗心人 2024-09-17 16:50:56

您没有提供足够的信息来回答您的问题。

对于 InnoDB，作为一般规则，当出现错误时，请查看 SHOW ENGINE INNODB STATUS 的输出。您希望在命令行客户端中运行此命令，并以 \G（反斜杠大写 G）结束命令以使输出可读。

在您的情况下，相关信息将位于 SHOW ENGINE INNODB STATUS 输出的顶部，其中显示类似的信息，

------------------------
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

其中列出了相关的表和索引名称，以及所涉及的语句。有了这些信息，您的问题就可以调试了。

另请参阅 http://www.mysqlperformanceblog.com /2006/07/17/show-innodb-status-walk-through/ 了解更多信息。

如果这种情况经常发生，请查看 http://freshmeat.net/projects/innotop， “D”屏（死锁显示）。

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.

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

回复收藏 0 原文

~没有更多了~