0

While I do some changes to a table in innodb I want to lock the table. But I can't make it work. After locking the table I can't alter the table in the same transaction

SQLSTATE[HY000]: General error: 1099 Table 'account' was locked with a READ lock and can't be updated

BEGIN;
LOCK TABLES `account` READ;
ALTER TABLE `account` DROP FOREIGN KEY `account_ibfk_1`;

mysql 5.7.36

1 Answer 1

1

Don't use LOCK TABLES with InnoDB.

In 5.7, any DDL statements terminate transactions. That is, your BEGIN had no effect.

In 8.0, DDL statements can be in transactions.

In either case, if you do not have a transaction, ALTER will "lock" the "table" as much as the implementation calls for. Before 5.6, it would lock long enough to completely copy the table over; in 8.0, it is much less invasive. I don't know the 5.7 details.

Perhaps you wanted only

SET FOREIGN_KEY_CHECKS = 0;

Do read the reference manual for 5.7 for further details. (And later change it back to =1.)

2
  • How can I then lock the table while I do some changes on the table?
    – clarkk
    Dec 18, 2021 at 19:21
  • 1
    @clarkk As Rick mentioned, it will lock the table for you automatically when you execute your changes to the table, for the appropriate amount of time necessary. No need to explicitly lock the table.
    – J.D.
    Dec 18, 2021 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.