更改表时出现死锁 [英] Getting a deadlock when altering table

问题描述

我正在包含许多行的表上的Rails应用程序中运行以下迁移:

I am running the following migrations in my Rails app on a table that contains a lot of rows:

rake db:migrate
*** [] rake aborted!
*** [] An error has occurred, this and all later migrations canceled:
*** [] 
*** [] PG::Error: ERROR:  deadlock detected
*** [] DETAIL:  Process 33319 waits for AccessExclusiveLock on relation 18486 of database 16948; blocked by process 29772.
*** [] Process 29772 waits for ShareLock on transaction 8652; blocked by process 33319.
*** [] HINT:  See server log for query details.
*** [] : ALTER TABLE "topics" DROP "most_recent_post_id"
*** [] 
*** [] Tasks: TOP => db:migrate
*** [] (See full trace by running task with --trace)
 ** [] ==  RemoveMostRecentPostsColumnsOnTopics: migrating 
 ** [] Updated 56875150 rows out of 568715 tries
 ** [] -- remove_column(:topics, :most_recent_post_id)

正在运行的代码是这样:

The code running is this:

def self.up
  rows_updated = 0
  rows_tried = 0

  Topic.find(:all).each do |topic|
    rows_tried += 1
    rows_updated += 1 if topic.update_attribute :updated_at, topic.most_recent_post_created_at
  end

  puts "Updated #{rows_updated} rows out of #{rows_tried} tries"

  remove_column :topics, :most_recent_post_id
  remove_column :topics, :most_recent_post_created_at
end

然后我尝试将其用作显式锁，但是在搜索有关问题的信息时，我意识到ALTER TABLE已经使用ACCESS EXCLUSIVE锁来锁定该表，具体如下:

I then tried to do it as a explicit lock, but when searching of info about the problems I realized that ALTER TABLE already is locking the table with an ACCESS EXCLUSIVE lock, according to this: http://www.postgresql.org/docs/9.1/static/explicit-locking.html

我可以做些什么来完成我的更改吗?

Is there something that I can do to get my changes done?

推荐答案

您有两个尝试获取独占访问权限的过程:

You have two processes trying to gain exclusive access:

Process 33319 waits for AccessExclusiveLock on relation 18486 of database 16948; blocked by process 29772.
Process 29772 waits for ShareLock on transaction 8652; blocked by process 33319.

其中之一是您的迁移任务.我假设另一个是您的服务器.我建议:

One of them is the your migration task. I'm assuming the other is your server. I suggest:

• 如果您正在运行开发环境，请退出服务器，运行迁移并重新启动服务器.
• 如果您正在运行生产环境并且需要运行以期望经常运行迁移，则可以在服务器应用中添加#migrate方法，使其在同一过程中运行.

(说实话，我只是在多处理环境中开始研究PostgreSQL －如果我了解更多，我会提供一个更好的答案.)

(To be honest, I'm just starting to dig into PostgreSQL in a multi processing environment -- if I learn more I'll post a better answer.)

这篇关于更改表时出现死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！