MySQL lock on MEMORY storage engine, when does it apply

lockingmemoryMySQLstorage-engine

I want to use MySQL MEMORY storage engine to handle my PHP session variables (actually I already implemented it but since all my tests are being done by a single user, me, I am unable to notice this).

As it says here:
http://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

MEMORY performance is constrained by contention resulting from single-thread execution and table lock overhead when processing updates. This limits scalability when load increases, particularly for statement mixes that include writes.

I don't understand this statement. It makes me think that when you insert/update/replace the table locks itself automatically and entirely so you will not be able to select anything while the write is being done. Is this correct? Can this be disabled? I need to write and read quite frequently and at the same time and I am not planning on doing transactions on this table:

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `data` varchar(64000) NULL,
  `expires` int(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=MEMORY;

Best Answer

You said in your question

It makes me think that when you insert/update/replace the table locks itself automatically and entirely so you will not be able to select anything while the write is being done. Is this correct?

You are correct.

The MEMORY storage engine performs a full table lock for every DML statement. That is why the link you mentioned shows Lock granularity : Table just like MyISAM. You cannot disable this.

In addition to this, please keep in mind that the MEMORY storage engine still imposes some mild disk I/O for both reads and writes. Why ? An open file handle is maintained on a MEMORY table to at least check on the table's existence and accessibility (See my old most I am using the MEMORY storage engine but MySQL still writes to my disk...Why?)

Please see my other old post Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM? on the pros and cons of using MEMORY tables.

You may have to resort to using InnoDB for such session data.

Related Question