Migrations are used to modify your database. By default, all migrations run inside a transaction. You can disable the transaction during migration. Let’s have a look on how to disable transaction block!

Migrations can manage the evolution of a schema used by several physical databases. It’s a solution to the common problem of adding a field to make a new feature work in your local database, but being unsure of how to push that change to other developers and to the production server. With migrations, you can describe the transformations in self-contained classes that can be checked into version control systems and executed against another database that might be one, two, or five versions behind.

In Rails, transactions are protective blocks around SQL statements that ensure changes to the database only occur when all actions succeed together. Transactions enforce the integrity of the database and guard the data against program errors or database break-downs. So basically you should use transaction block whenever you have a number of statements that must be executed together or not at all.

Eg:

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.

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:

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