Rails postgresql如何将事务隔离级别设置为可序列化 [英] Rails postgresql how to set transaction isolation level to serializable

问题描述

我有一个注释模型，属于一个主题模型。在评论模型上，我有一个before_create回调

  def on_create 
 Topic.transaction（：require_new => true ）do 
 Topic.connection.execute（'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'）
 self.topic.increment！（：comment_counter）if条件
 end 
 end 
  

问题是我得到一个 ActiveRecord :: StatementInvalid：PGError：ERROR：SET TRANSACTION ISOLATION LEVEL必须在任何查询之前调用。

是否有另一种方法设置事务隔离级别？

< PostgreSQL需要 SET TRANSACTION 语句在事务开始后，在任何DML（SELECT，INSERT， DELETE等）语句。从文档，看起来所有的东西都必须通过连接对象，而不是事务对象。类似（未测试的）

  Topic.connection.begin_db_transaction 
 Topic.connection.execute（'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE '）
＃其他事情去这里。我将用另一个文本SQL语句来测试，以使
＃确定它的工作原理，我希望它。然后可能尝试在Rails中重写。 
 Topic.connection.commit_db_transaction 
  

我真的错了。

一个令人讨厌的替代方法是更改​​PostgreSQL服务器上所有事务的默认隔离级别。 （搜索 http://www.postgresql.org/docs/current/static/runtime- config-client.html fordefault_transaction_isolation。）但是，感觉就像使用加农炮杀了苍蝇。

I have a Comment model which belongs_to a Topic model. On the Comment model, I have a before_create callback

def on_create
  Topic.transaction(:require_new => true) do
    Topic.connection.execute('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE')
    self.topic.increment!(:comment_counter) if conditions
  end
end

The problem is that I get a ActiveRecord::StatementInvalid: PGError: ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query.

Is there another way to set the transaction isolation level?

解决方案

PostgreSQL requires SET TRANSACTION statements to be executed after a transaction starts and before any DML (SELECT, INSERT, DELETE, etc.) statement. From the documentation, it looks like all that stuff will have to be done through the connection object, not the transaction object. Something like (untested)

Topic.connection.begin_db_transaction
  Topic.connection.execute('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE')
  # Other things go here. I'd test with another literal SQL statement to make
  # sure it works like I'd hope it does. Then possibly try rewriting in Rails.
Topic.connection.commit_db_transaction

I really hope I'm wrong about that.

One distasteful alternative is to change the default isolation level for all transactions on the PostgreSQL server. (Search http://www.postgresql.org/docs/current/static/runtime-config-client.html for "default_transaction_isolation ".) But that feels like using a cannon to kill a fly.

这篇关于Rails postgresql如何将事务隔离级别设置为可序列化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！