Proper way to increment a value in database

Wappler General Need Help
1

I have a table with records in it. One of the fields is seats available. I would like to increase/decrease that number on server connect edit. The only way I can get it to work is to have hidden fields with the available seats value bound to it and then update that field by 1. This works.

But the problem is, if two people are updating the same value at close to the same time, the value just get overwritten.

I would like to be able to increment that value taken directly from the database on update instead of from a hidden field. Is this possible?

2

Modify the server action api that is updating the seats to:

  1. query the the existing seats - use database single query for this.
  2. use the update database action to update the seats using the output from the database query and adding or subtracting the seats depending on your actions.
3 Likes
3

Oh wow, thanks @kkoker! I just learned something new! :beers:

1 Like
4

This way works perfect!

5

If you are happy to use a custom query.

UPDATE table_name. SET column_name = column_name + value Where ....

Is slightly more efficient

4 Likes
6

The accepted solution is vulnerable to a race condition, the only way to make this safely is to use transactions (new Wappler feature) or a custom query to perform the upsert (increment) without relying on a explicit previous database query (the previous post approach - my recommendation).

Btw the technical term for this action is ā€œupsertā€.

1 Like
7

Thanks @Apple and @Hyperbytes. I donā€™t understand how to do it in your suggestions. That seems like a very over-complicated way to do it.

What is wrong with doing it the way I just learned? Very easy to set up and works like a charm.

I should add that in my use case, I have two columns with seat counts in them. One for ā€˜Virtualā€™ and one for ā€˜In Personā€™. The one that gets updated is dependant on a select in the form. If the user selects ā€˜Virtualā€™ the virtual number gets incremented only.

8

Itā€™s all down to timing.
In the accepted solution.there is a possibility, albeit small I accept,that in the few milliseconds between the select and update that another seat transaction could start.
As a consequence, the seat count would be wrong as the other transaction was incomplete.
I know itā€™s a very small chance but it is possibly.

Examply:
Person 1 reserves a seat and the query returns, Say 10 sold
Before completion, person 2 also reserves a seat and the query returns 10 as the previous update has not taken place yet
Transaction 1 completes updating to 10+1
Transaction 2 then completes also updating to 10 +1
Sold count is then wrong.
I accept its a tiny possibility but it can happen.

9

Thanks Brian, in my use case it is very unlikely that two people would be doing the same task. This is on an internal dashboard where maybe five staff members would even be using this. The tool on the dashboard maybe gets accessed twice a day (8 hour shift).

Thank you for your explanation. Makes total sense.

1 Like