2

I am using Percona MySQL 5.5.28 MySQl server on linux system with ext4 file system and 23 GB ram out of which 15 GB approx is allocated to MySQL.... I fired a query something like below

INSERT IGNORE INTO table_A SELECT field1,field2,....fieldn FROM table_B;

The above query is locking table_B.... Due to which alter query which i have fired later on the table_B locked.

Above two table are of Innodb and My entire db operations is only Innodb storage engine only.....

Can Anyone please tell me why MySQL locked table_B has locked here ?

If Possible Please let me know when and which queries will lock a table in MySQL ?

vidyadhar
  • 3,118
  • 6
  • 22
  • 31
  • Read [Does INSERT IGNORE lock the table even if it ignores the insert?](http://stackoverflow.com/questions/18170235/does-insert-ignore-lock-the-table-even-if-it-ignores-the-insert) – Marty McVry Sep 29 '13 at 18:21
  • i have read the article before but my question is why table_B locked...There are only 3 threads connected one is mine(root) other executing alter and another executing INSERT IGNORE...SELECT...Please help me out – vidyadhar Sep 29 '13 at 18:55

1 Answers1

0

This is an old question, but in case someone stumbles on it like I did, the short answer is that adding SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; before the query sometimes works.

More info: How to improve INSERT INTO ... SELECT locking behavior

Note that the mentioned innodb_locks_unsafe_for_binlog approach was deprecated as of MySQL 5.6.3. (http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog).

Community
  • 1
  • 1
JimTheFrog
  • 560
  • 1
  • 5
  • 7