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.
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.
Review the SQL statement that triggered Error 1179. Look for ALTER TABLE, LOCK TABLES, FLUSH, ANALYZE TABLE, OPTIMIZE TABLE, or other DDL/administrative commands:
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.
Move the DDL or LOCK statement outside the transaction block. Commit or rollback the transaction first:
BEGIN;
UPDATE users SET status = 'active';
COMMIT; -- End the transaction
ALTER TABLE users ADD COLUMN last_login DATETIME; -- Now this worksThis is the most straightforward fix for most scenarios.
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:
await sequelize.queryInterface.addColumn('users', 'last_login', {
type: DataTypes.DATE,
}, { transaction: null }); // Disable transactionTypeORM:
await queryRunner.query(`ALTER TABLE users ADD COLUMN last_login DATETIME`);
// Already outside transaction in migrationsKnex:
knex.schema.table('users', table => {
table.dateTime('last_login');
}) // Schema operations don't use transactionsIf you need to combine locks with transactional guarantees on InnoDB, use autocommit instead of explicit transactions:
SET autocommit = 0; -- Start pseudo-transaction
LOCK TABLES users WRITE;
UPDATE users SET status = 'active';
UNLOCK TABLES;
COMMIT; -- Explicit commitThis avoids Error 1179 because LOCK TABLES is not wrapped in a transaction that it cannot support.
Structure your scripts to separate concerns: transactional data changes and DDL operations:
-- 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.
After applying fixes, test your transaction with a simple verification:
BEGIN;
SELECT * FROM users LIMIT 1; -- Safe DML
COMMIT;
-- Then separately:
ALTER TABLE users MODIFY status VARCHAR(50); -- Safe DDL outside transactionBoth should execute without Error 1179.
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+.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions