2

I’m trying to find a way to insert one or more rows to a table without exceeding a maximum numbers of rows at that point in time defined by the query. I need to do this efficiently and thread safely.

Say for example I have a table that holds information about different pieces of fruit. I need to be able to insert one or more pieces of fruit (all of the same kind) at the same time without exceeding a total number of that type of fruit on the table at any time. What I mean by this is no more than 100 apples, 250 oranges, etc. 

⁃   Insert 5 oranges into fruits unless there are more than 245 (250 - 5) oranges already.
⁃   Insert 1 apple into fruits unless there are more than 99 (100 - 1) apples already.

I understand that I could select first to determine if there is space, then insert my data, then select again to make sure I have exceeded the “quota” for that type, but that doesn’t seem efficient. When it comes to multiple threads it seems more like a hack/hammering method to getting it done. My concern and the reason I’d “look back” to check if too many were inserted is between the first select and the insert another connection might come along and insert rows before I get a chance to making my insert overflow those limits.

I’m still learning MySQL and if I haven’t given enough information let me know.

Any thoughts?

atmowl
  • 23
  • 3

2 Answers2

0

There are 2 ways to do this.

Method 1-

Check count of each of the fruits before inserting data into your table and insert only the differential records.

For example, let's say your table is like this.

id|fruit
1 |apple
2 |apple
3 |orange
4 |banana

Then you can first make a query to check count of each fruit like this

select fruit, count(*) from `table`
group by 1

//which gives you result like
apple  | 2
orange | 1
banana | 1

Now say you want to insert 5 apples, 3 oranges and 2 banana and you have got 8 apples, 5 oranges and 4 banana. Then you only insert the difference which is (5-2 = 3 apples), 4 oranges and 3 banana.

But for doing this simultaneously for multiple clients would require you to either lock tables or prevent your code from running concurrently for multiple clients.

Method 2-

You can check for count at each insert and insert only when count is less than the permitted max. Like this.

insert into `table`(fruit)
select (case when count(fruit) < 1 then fruit end) as fruit
from `table`
where type="Apple"
having fruit is not null; //this having condition will prevent null values from getting inserted

Both the methods are equally inefficient. You can use method 1 if No. of clients if much less than No. of inserts needed since you will have to do locking infrequently and can process a large number of inserts together.

If the inserts are less than number of clients, then go for method 2.

codeslayer1
  • 3,666
  • 3
  • 17
  • 13
  • What will happen when there’s 100 clients each trying to do exactly the same thing? Is there a risk that between client one checking the count and inserting client two will insert rows taking all of the fruits to the max, that way when client one finally gets around to it (assuming something delayed it) it will overflow? How do I manage that? I’ve read about locking but it seems really expensive with a tonne of clients. – atmowl Sep 16 '17 at 08:01
  • OP's core question is exactly to avoid the manual checks. He wants to know whether it is possible to check during inserting through query or not? – Karan Desai Sep 16 '17 at 08:10
  • @KaranDesai What exactly do you mean by manual check? OP want to insert rows most efficiently and being thread safe(read just now that it's for multiple clients running simultaneously). Checking for count of fruits on each insert query won't be efficient at all. That's why I mentioned to check once before running all insert queries and then run all the insert queries at once after check. – codeslayer1 Sep 16 '17 at 08:29
  • @atmowl Updated the answer to help with your query. – codeslayer1 Sep 16 '17 at 08:52
0

You can use select case expressions in your insert statement. Suppose your table name is food which accepts two columns name and quantity. You can make a check whether apple with the specified count exists. If not, then insert as shown.

insert into food(name, quantity)
select (case when  count(name) <1 then "apple" end) as name, 1 as quantity
from food
where name="apple"

Here the as keyword is most important. The name after as should match the column name.

Karan Desai
  • 3,012
  • 5
  • 32
  • 66