Insert Row Into 2 Tables Linked w/Foregin Key Only If The Dependable Table's Insert Succeeds (Without Transaction)

Question

I have two MySQL tables and I need to insert a record in each of them. Caveat: The insert into dependable table may fail due to duplicate key conflict. In that case none of the rows should be inserted.

CREATE TABLE `meanings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `aliases` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `meaningId` int(10) unsigned DEFAULT NULL,
  `phrase` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phrase_UNIQUE` (`phrase`),
  KEY `meaning_IDX` (`meaningId`),
  CONSTRAINT `fk_Synonyms_1` FOREIGN KEY (`meaningId`) REFERENCES `meanings` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

In a nutshell I need to do following but faster:

INSERT IGNORE INTO aliases (phrase, meaningId) VALUES ('word1', NULL);
SELECT IF(row_count() > 0, last_insert_id(), 0) INTO @aliasId;
INSERT INTO meanings (id) SELECT null FROM aliases WHERE id=@aliasId;
SELECT IFNULL(last_insert_id(), 0) INTO @meaningId;
UPDATE aliases SET meaningId=@meaningId WHERE id=@aliasId;

My constraints/conditions/wishes:

1. The SQL table scheme is given as above (although new cols can be added)
2. Avoid using transactions
3. Avoid using DELETE FROM meanings
4. Account for many processes doing the same thing simultaneously
5. Optimize for mass insert of average of ~40 aliases at the time
6. Minimize locks
7. Records are permanent and will never be removed

My thoughts:

• Comfortable way would be trigger after insert on aliases that inserts a row into meanings and updates back inserted row's aliases.meaningId but I am worried about performance
• In worst case I can do transactions if they are performant enough when compared to other solutions. This is a routine that may be called inside other transactions so avoiding transactions would be preferable.
• Performance on dozens of inserts at the time is the key factor (using PHP).

QUESTION: What is the fastest solution to insert ~40 (possibly conflicting) records into aliases at the time with corresponding records in meaning table?

It seems that this use case scenario is so common that something back in my head keeps saying that I might lack some knowledge of existing trivial solutions/inbuilt support for this particular case that is both more elegant and more efficient (then the solution I came up with bellow).

Note: The table is really 1:n even though in this example it behaves strictly 1:1. In my use case the data start their life cycle as 1:1 but later on their meaning can change...

---

The example of desired result

Before insert:

SELECT * FROM meanings;

# id
  1 
  2 
  3 

SELECT meaningId, phrase FROM aliases;

# meaningId       phrase
  1               word1
  2               word2
  3               word3

Now I need to insert in highly optimized way values word3 (note this one already exists), word4, word5 so the result is this:

SELECT * FROM meanings;

# id
  1 
  2 
  3 
  4
  5

SELECT meaningId, phrase FROM aliases;

# meaningId       phrase
  1               word1
  2               word2
  3               word3
  4               word4
  5               word5

Answer 1

The best way I could come up with given all the circumstances and constraints is this:

ALTER TABLE `meanings` 
ADD COLUMN `initialAliasId` INT(10) UNSIGNED DEFAULT NULL,
ADD UNIQUE INDEX `initialAliasId_UNIQUE` (`initialAliasId` ASC);

INSERT IGNORE INTO aliases (phrase) VALUES ('word1'), ('word2'), ('word3');

INSERT IGNORE INTO meanings (initialAliasId) SELECT id FROM aliases WHERE meaningId IS NULL;

UPDATE aliases as a LEFT JOIN meanings as m ON (m.initialAliasId = a.id) 
SET a.meaningId = m.id WHERE a.meaningId IS NULL;

• Supports multiple inserts at the time
• Minimizes both locks and overhead that comes with separate requests
• No tansactions/rollbacks