Table locking will prevent row locking

Question

I have a table with over 160 million entries that has the wrong table engine, so I'm going to change the engine. When I do this without any preparation, I get an error due to my buffer size due to too many row locks

mysql> ALTER TABLE foobar ENGINE=MyISAM;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Now I want to lock the entire table before this action and then unlock the entire table.

mysql> LOCK TABLES foobar WRITE;

My question is: does the mysql server notice that the table lock is already active and skips row locks or has locked the table, and now it will lock each row again, and I will get the same error again?

I am also open to any other tips on how to change the engine of such a large (and larger) table faster and faster :)

Answer 1

You can run the experiment:

In the first session:

mysql> LOCK TABLE foobar WRITE;
mysql> ALTER TABLE foobar ENGINE=MyISAM;

In the second session (i.e. open the second terminal window), do:

mysql> SHOW ENGINE INNODB STATUS\G

In the "OPERATIONS" section, you will see the current ALTER sequence:

---TRANSACTION 69638, ACTIVE 45 sec fetching rows
mysql tables in use 1, locked 1
14626 lock struct(s), heap size 1898936, 5145657 row lock(s)

Whoah! This creates many separate row locks, even though LOCK TABLE is in effect.

(This is just an example; you will see that the number of row locks increases over time when ALTER TABLE works through your table.)

---

So, you need to guarantee space for a lot of row locks.

https://dev.mysql.com/doc/refman/5.6/en/innodb-error-codes.html says:

• 1206 (ER_LOCK_TABLE_FULL)

  , InnoDB . , innodb_buffer_pool_size. . , INSERT, INSERT.

( )

Innodb?

Innodb , , , , , . .

, 160 60-80 . , .

InnoDB 8 MySQL 5.1.27 . 128 MySQL 5.1.28.

---

:

ey;) - MyISAM

MyISAM Atomicity, Consistency, Isolation Durability. , .: -)

Answer 2

, InnoDB , ( ), InnoDB (innodb_buffer_pool_size).

innodb_buffer_pool_size 128 .

, MyISAM, INSERT INTO ... SELECT FROM ... InnoDB MyISAM. , .