create table as select block my insert
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
Subject
Views
Written By
Posted
create table as select block my insert
2468
October 05, 2014 03:25PM
1198
October 06, 2014 12:43PM
1440
October 06, 2014 02:28PM
1243
October 07, 2014 04:08PM
983
November 05, 2014 09:59AM
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.