Isolation levels not being reset on error and database connections lost in unicorn processes #469
Description
We have some code that executes a transaction with an isolation level of :serializable. If an exception is raised inside the transaction, the isolation level is not being rolled back.
Due to a bit of a funny setup I can't run the test suite of the gem, but the fix we have verified is as follows:
module ActiveRecord::ConnectionAdapters
module SQLServerRealTransaction
def rollback
super
reset_starting_isolation_level
end
end
end
To replicate the issue, run this code with an isolation level of something other than serializable:
YourModel.transaction(isolation: :serializable) do
raise 'boom'
end
Check your isolation level before and after - afterward the isolation level will remain as serializable.
We also noted some strange behaviour when running the code above via unicorn:
YourModel.transaction(isolation: :serializable) do
... do some stuff that updates the database ...
end
After the above executes the database connection starts timing out. I am not a SQL Server expert and I'm only including this part here as I believe there is a potential issue that you may want to be aware of (but it is of course possible this behaviour is some kind of config quirk that I am unaware of or something). The implementation of set_transaction_isolation_level begins a transaction, this means that after a transaction is committed and set_transaction_isolation_level runs, a new transaction is opened, but nothing is ever done with it and it is never closed.
By changing the implementation of set_transaction_isolation_level to this:
module ActiveRecord::ConnectionAdapters
module SQLServer
module DatabaseStatements
def set_transaction_isolation_level(isolation_level)
do_execute "SET TRANSACTION ISOLATION LEVEL #{isolation_level}"
end
end
end
end
the above issue is resolved.