Locking because of triggers mysql I have a memory table and I am updating it somewhere around 3000 updates per second. I setup a another table in Innodb and then created a Trigger so that all data can be send to the innodb table as a backup since memory table can disappear if server reboots.

My problem is I get errors like this Waiting for table level lock, from my show process list. I am unable to update Memory table anymore and all my operations blocked.

As soon as I delete these triggers no more locks and I can continue updating the tables.

Can anyone help with this, do I need to create triggers differently or some setting need to be changed ?
In one situation, I found it possible to ingest data faster into InnoDB than into MEMORY.  Perhaps you should simply get rid of the MEMORY table and the TRIGGER.

I discuss that an many other tidbits in my high-speed ingestion blog.

Do you have multiple inserters?  Do you insert multiple rows in a single INSERT?  Are you pulling the data out of the MEMORY table?  How often, how, when, etc?  Is Replication involved?  Is "normalization" happening?  Summarization?