Mysql – Concurrent INSERTs in XtraDB (InnoDB)

innodbinsertMySQL

I'm currently unable to insert rows concurrently in an XtraDB table on Percona Server 5.1.54. I had the same problem with a similar version of MySQL. My table definition is as follows.

CREATE TABLE `test` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM

If in on one connection, I execute something like INSERT INTO test VALUES (0, sleep(60)) and in a second process, INSERT INTO test VALUES (1, 0), the second insert will wait for the first to complete. Note that innodb_table_locks is off. Is there something I'm missing, or is this just not possible?

Update

As Randy points out below, I created a MyISAM table during testing and then assumed that concurrent inserts weren't possible for my other tables (which are InnoDB). I actually don't have a problem with InnoDB tables.

Best Answer

You might notice the problem is :

ENGINE=MyISAM

Try changing that to:

ENGINE=InnoDB