Re: Implicit locking of MyISAM tables during transaction

[Rick James]

In InnoDB, grabbing a sequence number from the SEQUENCE table should not be included in the transaction! Grab the number before doing the START.

Alternatively, you could have a separate connection to grab sequence numbers; it could be autocommit=1. (This messy alternative might be needed if you need a variable number of sequence numbers within a transaction.)

START TRANSACTION and COMMIT has zero impact on MyISAM. However, every operation on any MyISAM table takes out a "table lock" (either read or read-write) which blocks concurrency. (InnoDB, in contrast, honors START/COMMIT and does "row level locking".)

In 99% of cases, AUTO_INCREMENT is a superior alternative to a sequence table. Are you just now migrating from some non-MySQL engine have yet to wrap you head around AUTO_INCREMENT? Or does your application fall into the 1%? If so, explain it; maybe I can help you design the best way to do sequences, or maybe even avoid them. (In about half the 1% cases I have encountered, I ended up with AUTO_INCREMENT; but the code was not obvious.)

Another thing to consider... Let's see
SHOW CREATE TABLE
I want to see what the PRIMARY KEY and UNIQUE keys are. In particular, I want to discuss whether your concurrent INSERTs are going to block each other anyway because of the keys.

How fast (INSERTs/sec) are you striving for?

Can you "batch" the inserts? -- INSERT INTO tbl (...) VALUES (...), (...)...;
A batch of 100 will run 10x faster. AUTO_INCREMENT is quite happy to provide unique numbers when batching; SEQUENCE would be complicated for this.