Resolve MySQL deadlock on INSERT INTO SELECT NOT EXISTS-CodePudding

I've been searching the internet for a couple hours now and I'm not sure how to resolve this at all. So brief description is a customer posts orders to our system and they can supply a Customer Reference that our system will reject if that Customer Reference already exists.

I can't make the column in MySQL UNIQUE as different clients sometimes use the same Customer Reference and we do not require the Customer Reference so sometimes it's just left blank.

Originally I was just checking if the Customer Reference existed if necessary and then inserting the row if it did not exist. This works on 99.99% of cases, but I have a client that mass sends orders and those sometimes have duplicates. Which since they're posting quickly the select can happen before the first insert and duplicates arise.

I've switched to code like this below:(Shortened for example, this only runs if customerReference is not blank)

INSERT INTO ordersTable (clientID,customerReference,deliveryName) SELECT clientID, customerReference,deliveryName
    FROM (SELECT 'clientID' as clientID, 'customerReference' as customerReference, 'deliveryName' as deliveryName) t
    WHERE NOT EXISTS (SELECT 1 FROM ordersTable u WHERE u.customerReference = t.customerReference AND u.clientID = t.clientID);

This ends in deadlocks for any processes after the original row is inserted. I was hoping to avoid deadlocks?

My options it seems are:

Live with it deadlocking because I know if it deadlocks then the row already exists and instead of looking at affected_rows ==0 make it affected_rows <= 0.
Try to come up with some column that will make a unique record hash per order based on client ID and Customer Reference? and then do an "INSERT IGNORE" for that column?

I wasn't too confident in either solution so I thought it couldn't hurt to ask for advice first.

CodePudding user response：

Have you tried using a transaction with a unique constraint on the uniqueID and clientID columns? This will prevent duplicates from being inserted, and you can catch the exception that is thrown when a replication is attempted to be inserted and handle it as needed.

INSERT INTO ordersTable (clientID,uniqueID,deliveryName)
VALUES ('clientID', 'uniqueID', 'deliveryName')
ON DUPLICATE KEY UPDATE deliveryName = VALUES(deliveryName);

CodePudding user response：

Ok, you can also use "INSERT IGNORE" statement. This statement tells the server to insert the new record, but if there is a violation of a UNIQUE index or PRIMARY KEY, ignore the error and don't insert the new record.

INSERT IGNORE INTO ordersTable (clientID,uniqueID,deliveryName)
VALUES ('clientID', 'uniqueID', 'deliveryName');