Rails postgresql how to set transaction isolation level to serializable

Question

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?

That was my question, actually - how to set the isolation level before the whole model save transaction starts. — eugen, Oct 27 '11 at 12:03

score 10 · Answer 1 · edited Jun 20 '20 at 09:12

10

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 21 '13 at 17:44

Peeja

13,683
11
58
77

score 8 · Accepted Answer · answered Oct 27 '11 at 13:54

8

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.

answered Oct 27 '11 at 13:54

Mike Sherrill 'Cat Recall'

91,602
17
122
185

The code would work in isolation, but the main problem is that this Topic transaction is wrapped in another transaction. Apparently, it's not possible to use nested transactions with different isolation levels, so I would need a way to make the first transaction serializable - and I would not like to make any transactions on the Comment model serializable. – eugen Oct 27 '11 at 14:05
2

@eugen: To nest a transaction with a different isolation level is a logical paradox. This cannot be done. – Erwin Brandstetter Oct 27 '11 at 16:17
@Catcall, thank you - your comments actually pointed me to another way to solve the problem, I'm going to accept the answer as is. – eugen Oct 28 '11 at 12:12
@Catcall: By using the after_commit callback - this one runs after the original transaction is committed, so it can run in its own transaction. – eugen Oct 28 '11 at 13:09
@eugen: So you basically unnested the transactions? – Mike Sherrill 'Cat Recall' Oct 28 '11 at 14:41
@Catcall: Indeed, I had to unnest the transactions. Testing is also a bit more difficult (I had to disable the transactional fixtures), but it seems to work this way. – eugen Oct 28 '11 at 15:11
@eugen: Is the best way to unnest your transactions to run one in the after_commit callback of the other? – Mike Sherrill 'Cat Recall' Oct 28 '11 at 15:19
1

@Catcall: It's one solution. I'm not sure it's the best, but it works for now. – eugen Oct 28 '11 at 20:39

score 4 · Answer 3 · answered Feb 06 '12 at 21:48

4

You may find transaction_isolation gem helpful: https://github.com/qertoip/transaction_isolation

answered Feb 06 '12 at 21:48

qertoip

1,870
1
17
29

Rails postgresql how to set transaction isolation level to serializable

3 Answers3

Linked