2

I have a single, unreplicated MySQL server.

We're experiencing a problem where an INSERT INTO ... SELECT statement is causing a write lock on the origin table.

The origin table is in one database. The destination table is a temporary table in another database.

Following the advice of this question's accepted answer: How to improve INSERT INTO ... SELECT locking behavior

I changed the following three configuration variables in my.cnf and restarted the server:

innodb_autoinc_lock_mode=2
binlog_format=row
transaction-isolation=READ-COMMITTED

This seemed to make the problem worse, though correlation may not be causation in this case.

How do I prevent this write lock?

Community
  • 1
  • 1
Ben Overmyer
  • 192
  • 3
  • 13
  • Possible duplicate of [How to improve INSERT INTO ... SELECT locking behavior](http://stackoverflow.com/questions/2640898/how-to-improve-insert-into-select-locking-behavior) – bummi Jan 19 '16 at 06:02

2 Answers2

0

It sounds to me as if your transaction isolation level does not allow INSERTS while a sequenced read of the same data is underway.

Understand that this might be a good thing. MySQL doesn't block here for nothing. It just means that the data being read by your one statement might not be changed during the atomic operation. So an INSERT is not given permission to insert data that would otherwise change the data you're inserting into the temporary table now.

You can take a look at this page describing everything there is to know about transaction isolation from a MySQL perspective.

0xCAFEBABE
  • 5,576
  • 5
  • 34
  • 59
  • So... I need to implement replication or some other method to work around the problem, then? – Ben Overmyer Apr 12 '13 at 18:42
  • Solving this problem via replication seems a bit overkill. If you understood what the different transaction isolation levels are doing, you could lower it to allow non-blocking reads. – 0xCAFEBABE Apr 12 '13 at 18:46
  • It appears that setting innodb_locks_unsafe_for_binlog=1 fixed the issue. I'm not sure why, but I found that as a result of @0xCAFEBABE's suggestion. – Ben Overmyer Apr 12 '13 at 19:55
0

Would this help?

If you acquire a table lock explicitly with LOCK TABLES, you can request a READ LOCAL lock rather than a READ lock to enable other sessions to perform concurrent inserts while you have the table locked.

To perform many INSERT and SELECT operations on a table real_table when concurrent inserts are not possible, you can insert rows into a temporary table temp_table and update the real table with the rows from the temporary table periodically. This can be done with the following code:

mysql> LOCK TABLES real_table WRITE, temp_table WRITE;

mysql> INSERT INTO real_table SELECT * FROM temp_table;

mysql> DELETE FROM temp_table;

mysql> UNLOCK TABLES;

InnoDB uses row locks and BDB uses page locks. Deadlocks are possible for these storage engines because they automatically acquire locks during the processing of SQL statements, not at the start of the transaction.

Community
  • 1
  • 1
Kevin Rave
  • 13,876
  • 35
  • 109
  • 173
  • Unfortunately that won't work in our case, I don't think. Unless I'm misunderstanding this, this seems geared towards allowing modification of the main table, not the temporary table. – Ben Overmyer Apr 12 '13 at 18:40