Rails postgresql如何将事务隔离级别设置为可序列化

发布于 2024-12-12 02:49:12 字数 459 浏览 0 评论 0原文

我有一个属于主题模型的评论模型。在 Comment 模型上，我有一个 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 conditions
  end
end

问题是我得到一个 ActiveRecord::StatementInvalid: PGError: ERROR: SET TRANSACTION ISOLATION LEVEL Must be call before any query。

有没有其他方法来设置事务隔离级别？

原文

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?

分享到QQ

分享到微博

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

发布评论

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

鸩远一方 2024-12-19 02:49:12

从 Rails 4 开始，#transaction 提供:isolation 选项：

如果您的数据库支持设置事务的隔离级别，您可以这样设置：
Post.transaction（隔离：：可序列化）做
  # ...
结尾

As of Rails 4, #transaction provides an :isolation option:

If your database supports setting the isolation level for a transaction, you can set it like so:
Post.transaction(isolation: :serializable) do
  # ...
end

回复收藏 0 原文

昔梦 2024-12-19 02:49:12

PostgreSQL 要求在事务开始之后、任何 DML（SELECT、INSERT、DELETE 等）语句之前执行 SET TRANSACTION 语句。从文档来看，所有这些事情似乎都必须通过连接对象完成，而不是事务对象。像（未经测试）的东西

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

我真的希望我是错的。

一种令人讨厌的替代方法是更改 PostgreSQL 服务器上所有事务的默认隔离级别。（搜索http://www.postgresql.org/docs/current/static/runtime- config-client.html 表示“default_transaction_isolation”。）但这感觉就像用大炮杀死苍蝇一样。

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.

回复收藏 0 原文