MySQL Forums
Forum List  »  InnoDB

create table as select block my insert
Posted by: Ofir Gilboa
Date: October 05, 2014 03:25PM

Hi

i'm using mysql 5.5 and 5.6 on Amazon RDS and i'm trying to fugre out why my CREATE TABLE as SELECT block my queries.
I have a Master-Slave replication and I set the Slave to be in read/write mode.
then I issue this statement:
CREATE TABLE ofir as SELECT * FROM SOME_TABLE;

then I have noticed that the replication lag started to raise, so I run the following query and found that my CREATE TABLE is blocking the insert from the sql slave thread.
SELECT * FROM information_schema.INNODB_TRX;
results:
trx_id...........trx_state......trx_query
------------------------------------------
234557D6E.....LOCK WAIT....INSERT INTO `some_table`
2345554D4.....RUNNING......create table ofir as select * from


SELECT * FROM information_schema.INNODB_LOCKS;

lock_id................................lock_trx_id.......lock_mode......lock_type
-------------------------------------------------------------------------------
234557D6E:85:885976:13...234557D6E......X,GAP.......RECORD
2345554D4:85:885976:13...2345554D4......S..............RECORD


I have tried different isolation levels:
"set transaction isolation level read committed;"
and also "read uncommited" , but still I had replication lag growing and same table lock.
does anyone have the same problem or know how to avoid it ?

I have read about "innodb_locks_unsafe_for_binlog" but in 5.6 documentation it say it is deprecated so I don't want to use it.


Thanks,
Ofir

Options: ReplyQuote


Subject
Views
Written By
Posted
create table as select block my insert
2468
October 05, 2014 03:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.