How to fix ERROR 1179: Command not allowed in transaction in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 23, 2025· 101 views

Error 1179 occurs when you attempt to run DDL (ALTER TABLE, LOCK TABLES) or administrative commands inside an active transaction. MySQL blocks these operations to maintain transaction integrity and prevent deadlocks.

Quick fix

Fixes 1179

BEGIN;
UPDATE users SET status = 'active';
ALTER TABLE users ADD COLUMN last_login DATETIME; -- ERROR 1179 here
COMMIT;
  1. Identify the problematic statement in your transaction
  2. End the transaction before running the problematic command
  3. For applications/ORMs: disable autocommit for DDL
  4. For table locks: use autocommit mode instead of BEGIN
mysql — error
BEGIN;
UPDATE users SET status = 'active';
ALTER TABLE users ADD COLUMN last_login DATETIME; -- ERROR 1179 here
COMMIT;
ERROR 1179: Command not allowed in transaction

What this error means

MySQL Error 1179 (ER_CANT_DO_THIS_DURING_AN_TRANSACTION) is triggered when you try to execute statements that modify database structure or acquire locks within a transaction. This includes ALTER TABLE, DROP TABLE, LOCK TABLES, UNLOCK TABLES, FLUSH, OPTIMIZE TABLE, and ANALYZE TABLE. MySQL enforces this restriction because these operations cannot be transactional and could violate ACID guarantees. The server needs to prevent mixing structural changes with transactional data modifications to avoid deadlocks, metadata inconsistencies, and isolation level violations.

Typical symptoms

  • Application throws "ERROR 1179" or "ER_CANT_DO_THIS_DURING_AN_TRANSACTION" when executing a query
  • Migration scripts fail when running schema changes after database operations
  • ORM transaction wrappers prevent DDL execution (e.g., Sequelize, TypeORM migrations)
  • Stored procedures fail with 1179 when they attempt ALTER TABLE after DML
  • LOCK TABLES command fails after BEGIN or START TRANSACTION

Common causes

  1. Attempting to execute ALTER TABLE, DROP TABLE, or CREATE TABLE inside a BEGIN...COMMIT block
  2. Calling LOCK TABLES or UNLOCK TABLES within an active transaction
  3. Running FLUSH, ANALYZE TABLE, or OPTIMIZE TABLE after BEGIN without a COMMIT first
  4. ORM framework automatically wrapping all database calls in transactions, including migrations
  5. Stored procedures calling DDL statements after DML operations without explicit transaction boundaries
  6. Using LOCK TABLES followed by transaction-style DML (with autocommit off)

How to fix "ERROR 1179: Command not allowed in transaction"

1Identify the problematic statement in your transaction

Review the SQL statement that triggered Error 1179. Look for ALTER TABLE, LOCK TABLES, FLUSH, ANALYZE TABLE, OPTIMIZE TABLE, or other DDL/administrative commands:

sql
BEGIN;
UPDATE users SET status = 'active';
ALTER TABLE users ADD COLUMN last_login DATETIME; -- ERROR 1179 here
COMMIT;

The error is on the ALTER TABLE line because it cannot be executed within a transaction.

2End the transaction before running the problematic command

Move the DDL or LOCK statement outside the transaction block. Commit or rollback the transaction first:

sql
BEGIN;
UPDATE users SET status = 'active';
COMMIT;  -- End the transaction

ALTER TABLE users ADD COLUMN last_login DATETIME;  -- Now this works

This is the most straightforward fix for most scenarios.

3For applications/ORMs: disable autocommit for DDL

If you're using an ORM like Sequelize, TypeORM, or Knex, migrations often wrap queries in transactions automatically. Disable the transaction wrapper for DDL operations:

Sequelize:

javascript
await sequelize.queryInterface.addColumn('users', 'last_login', {
  type: DataTypes.DATE,
}, { transaction: null });  // Disable transaction

TypeORM:

typescript
await queryRunner.query(`ALTER TABLE users ADD COLUMN last_login DATETIME`);
// Already outside transaction in migrations

Knex:

javascript
knex.schema.table('users', table => {
  table.dateTime('last_login');
})  // Schema operations don't use transactions
4For table locks: use autocommit mode instead of BEGIN

If you need to combine locks with transactional guarantees on InnoDB, use autocommit instead of explicit transactions:

sql
SET autocommit = 0;  -- Start pseudo-transaction
LOCK TABLES users WRITE;
UPDATE users SET status = 'active';
UNLOCK TABLES;
COMMIT;  -- Explicit commit

This avoids Error 1179 because LOCK TABLES is not wrapped in a transaction that it cannot support.

5For schema migrations: execute DDL before/after transactional code

Structure your scripts to separate concerns: transactional data changes and DDL operations:

sql
-- Phase 1: Schema changes (no transaction)
ALTER TABLE users ADD COLUMN last_login DATETIME DEFAULT NULL;
CREATE INDEX idx_status ON users(status);

-- Phase 2: Data migration (with transaction)
BEGIN;
UPDATE users SET last_login = NOW() WHERE status = 'active';
COMMIT;

This pattern is safe and avoids Error 1179 entirely.

6Verify the fix with a test query

After applying fixes, test your transaction with a simple verification:

sql
BEGIN;
SELECT * FROM users LIMIT 1;  -- Safe DML
COMMIT;

-- Then separately:
ALTER TABLE users MODIFY status VARCHAR(50);  -- Safe DDL outside transaction

Both should execute without Error 1179.

Advanced notes

MySQL Transaction vs. Table Locks: BEGIN implicitly releases LOCK TABLES because transactions and table locks are incompatible. Starting a transaction causes an implicit UNLOCK TABLES. However, FLUSH TABLES WITH READ LOCK (global lock) behaves differently—it is NOT released by BEGIN and can coexist with transactions.

InnoDB vs. MyISAM: Error 1179 applies to both storage engines. However, MyISAM relies heavily on LOCK TABLES for consistency since it lacks row-level locking. When migrating MyISAM tables to InnoDB, revisit code that uses LOCK TABLES, as row-level locking in transactions is preferred.

Stored Procedures: Check the procedure definition for DDL statements executed after BEGIN without an intervening COMMIT. The error might be triggered by the stored procedure logic, not your direct SQL call.

MariaDB Compatibility: MariaDB raises the same error (Error 1179) with the same message and restrictions. Solutions are identical across MySQL and MariaDB versions 5.7+.

Frequently asked questions

What does "1179" mean?
MySQL Error 1179 (ER_CANT_DO_THIS_DURING_AN_TRANSACTION) is triggered when you try to execute statements that modify database structure or acquire locks within a transaction. This includes ALTER TABLE, DROP TABLE, LOCK TABLES, UNLOCK TABLES, FLUSH, OPTIMIZE TABLE, and ANALYZE TABLE. MySQL enforces this restriction because these operations cannot be transactional and could violate ACID guarantees.
How do I fix "1179"?
Error 1179 occurs when you attempt to run DDL (ALTER TABLE, LOCK TABLES) or administrative commands inside an active transaction. MySQL blocks these operations to maintain transaction integrity and prevent deadlocks.
What causes "1179"?
Attempting to execute ALTER TABLE, DROP TABLE, or CREATE TABLE inside a BEGIN...COMMIT block Calling LOCK TABLES or UNLOCK TABLES within an active transaction Running FLUSH, ANALYZE TABLE, or OPTIMIZE TABLE after BEGIN without a COMMIT first
Is the "1179" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading