CODEWITHSUNDEEP
mysqlconcurrencyinsertif-statementrace-condition
Joseph Silber
Joseph Silber

Reputation: 219938

MySQL INSERT IF (custom if statements)

First, here's the concise summary of the question:

Is it possible to run an INSERT statement conditionally? Something akin to this:

IF(expression) INSERT...

Now, I know I can do this with a stored procedure. My question is: can I do this in my query?

Now, why would I want to do that?

Let's assume we have the following 2 tables:

products: id, qty_on_hand
orders: id, product_id, qty

Now, let's say an order for 20 Voodoo Dolls (product id 2) comes in.
We first check if there's enough Quantity On Hand:

SELECT IF(
    ( SELECT SUM(qty) FROM orders WHERE product_id = 2  ) + 20
    <=
    ( SELECT qty_on_hand FROM products WHERE id = 2)
, 'true', 'false');

Then, if it evaluates to true, we run an INSERT query.
So far so good.

However, there's a problem with concurrency.
If 2 orders come in at the exact same time, they might both read the quantity-on-hand before any one of them has entered the order. They'll then both place the order, thus exceeding the qty_on_hand.

So, back to the root of the question:
Is it possible to run an INSERT statement conditionally, so that we can combine both these queries into one?

I searched around a lot, and the only type of conditional INSERT statement that I could find was ON DUPLICATE KEY, which obviously does not apply here.

Upvotes: 53

Views: 119982

Answers (5)

Andres Paul
Andres Paul

Reputation: 1068

I wanted to insert into a table using values so I found this solution to insert the values using the IF condition

DELIMITER $$

CREATE PROCEDURE insertIssue()
BEGIN
    IF (1 NOT IN (select I.issue_number from issue as I where I.series_id = 1)) THEN
        INSERT IGNORE INTO issue ( issue_number, month_published, year_published, series_id, mcs_issue_id) VALUES (1, 1, 1990, 1, 1);
    END IF;
END$$

DELIMITER ;

If you later on want to call the procedure it's as simple as 

CALL insertIssue()

You can find more information about PROCEDURES and if conditions in this site

Upvotes: 0

Bohemian
Bohemian

Reputation: 425033

INSERT INTO TABLE
SELECT value_for_column1, value_for_column2, ...
FROM wherever
WHERE your_special_condition

If no rows are returned from the select (because your special condition is false) no insert happens.

Using your schema from question (assuming your id column is auto_increment):

insert into orders (product_id, qty)
select 2, 20
where (SELECT qty_on_hand FROM products WHERE id = 2) > 20;

This will insert no rows if there's not enough stock on hand, otherwise it will create the order row.

Nice idea btw!

Upvotes: 80

Shef
Shef

Reputation: 45589

Try:

INSERT INTO orders(product_id, qty)
SELECT 2, 20 FROM products WHERE id = 2 AND qty_on_hand >= 20

If a product with id equal to 2 exists and the qty_on_hand is greater or equal to 20 for this product, then an insert will occur with the values product_id = 2, and qty = 20. Otherwise, no insert will occur.

Note: If your product ids are note unique, you might want to add a LIMIT clause at the end of the SELECT statement.

Upvotes: 25

My Other Me
My Other Me

Reputation: 5117

Not sure about concurrency, you'll need to read up on locking in mysql, but this will let you be sure that you only take 20 items if 20 items are available:

update products 
set qty_on_hand = qty_on_hand - 20 
where qty_on_hand >= 20
and id=2

You can then check how many rows were affected. If none were affected, you did not have enough stock. If 1 row was affected, you have effectively consumed the stock.

Upvotes: 3

Konerak
Konerak

Reputation: 39763

You're probably solving the problem the wrong way.

If you're afraid two read-operations will occur at the same time and thus one will work with stale data, the solution is to use locks or transactions.

Have the query do this:

  • lock table for read
  • read table
  • update table
  • release lock

Upvotes: 1

Related Questions