Disabling transaction block during migration

Published On - February 2, 2017

anjana

ActiveRecord::Base.transaction do david.withdrawal(100) mary.deposit(100) end

disable_ddl_transaction!()

DDL can’t run inside a transaction block. You can disable DDL transactions in Rails, using disable_ddl_transaction. It is used in AR(Active Record) migrations to prevent your migration from being wrapped in a transaction. The name is misleading because your entire migration will have transactions disabled, not just for DDL SQL statements. 
# File activerecord/lib/active_record/migration.rb
  def disable_ddl_transaction!
    @disable_ddl_transaction = true
  end
Normally, Postgress locks writing while creating an index on it. It may take a longer time to complete. However, PostgreSQL supports adding/dropping indexes concurrently. When this option is used, PostgreSQL must perform two scans of the table, and in addition, it must wait for all existing transactions that could potentially modify or use the index to terminate. So the migration must not be run inside a transaction. For that, use disable_ddl_tranaction! to run that on outside. Eg: 
class AddIndexToUserActive < ActiveRecord::Migration
  disable_ddl_transaction!
  def change
    add_index :users, :active, algorithm: :concurrently
  end
end
Postgres has a CONCURRENTLY option for CREATE INDEX that creates the index without preventing concurrent INSERTs, UPDATEs, or DELETEs on the table. To make this option easier to use in migrations, ActiveRecord 4 introduced an algorithm: :concurrently option for add_index. The disable_ddl_transaction method applies only to that migration file. Adjacent migrations still run in their own transactions and roll back automatically if they fail. So, if you want to disable transaction during migration use ‘disable_ddl_transaction!’. Let me know if you have any other alternatives as well.

References

]]>