mysql_insert_id() on galera mysql cluster

Question

I have percona mysql galera cluster. It inherited a MyISAM database. In order for the galera to work it had to alter to innodb. Replication is working so far so good. However I have a php application which calls for mysql_isert_id() after every insert.

In a few words : 1. A big table with many inserts flying in. 2. On every insert (in the old MyISAM db) they lock the table, do the insert query, call mysql_insert_id(), and after that unlock table.

for the replication (innodb), lock table doesn't count anymore. My question is how, can we be sure about the id that mysql_insert_id() returns when there are hundreds of ofther inserts comming in a matter of seconds?

last_insert_id() and mysql_insert_id() return the same value, but as far as I understand (last_insert) reserves ID before the actual insert(?), and mysql_insert_id do the insert and after that calls for an ID ?

I'm lost in the documentation....

Answer 1

The "insert_id" is local to the connection. No locking is needed to get the value. This applies in all flavors of multi-processing, clustering, engines, etc.

Still, you may need "transactions" for other purposes. You have not described the code enough to say whether you need to exactly replace LOCK TABLES (etc) with BEGIN..COMMIT. Or whether some variation is needed.

One thing to learn is the following pattern:

BEGIN;
SELECT ... FOR UPDATE;  -- rows you need to look at before changing
do stuff
UPDATE those rows
COMMIT;

The "do stuff" could include a ROLLBACK and exit from the flow.

AUTO_INCREMENT:

The INSERT reserves the id before the operation. In some cases, it will reserve an id but then not use it. Example: INSERT IGNORE when it hits a dup key.

Galera (and any other Multi-Master setup) skips ids. That is, one node will assign ids 1,4,7,10,...; another node: 2,5,8,...; the third: 3,6,9,... Depending on the flow of INSERTs, you may see most of the numbers used, or you may see lots of gaps. Also you may "see" id=124 "before" id=123 shows up.

More tips on moving to Galera and InnoDB