How to update and maintain data correctness in High-concurrency MySQL counters

One table, two fields, one id, and one useCount table store 100 IDs. Each id corresponds to its own useCount business scenario: When the id is used every time useCount needs to be added 1. When useCount is greater than 1000, this id cannot be used (in other words, it cannot be found from the database). In... a table, two fields, one id, and one useCount
The table contains 100 IDs. Each id corresponds to its own useCount.
The business scenario is: when the id is used every time useCount needs to add 1. When useCount is greater than 1000, this id cannot be used (in other words, it cannot be found from the database)

In the case of high concurrency, a problem occurs: assume that one record in the data table is id = 123456; useCount = 999
Join a and join B. Run the following SQL statement for the id 123456:

select * from table where id=123456 and useCount < 1000

A first executes the useCount of id 123456, Which is 999, then performs some logic judgment or business operations in the program, and then executes the SQL: update useCount + 1
Before a makes a judgment and no update, B also executes the query SQL statement and finds that after useCount is 999, it also executes the SQL: update useCount + 1.
But in fact, B should not obtain this id because a is already 1,000th users.

So I would like to ask how to use the database only to meet this requirement in high concurrency situations?

Reply content:

One table, two fields, one id, and one useCount
The table contains 100 IDs. Each id corresponds to its own useCount.
The business scenario is: when the id is used every time useCount needs to add 1. When useCount is greater than 1000, this id cannot be used (in other words, it cannot be found from the database)

In the case of high concurrency, a problem occurs: assume that one record in the data table is id = 123456; useCount = 999
Join a and join B. Run the following SQL statement for the id 123456:

select * from table where id=123456 and useCount < 1000

A first executes the useCount of id 123456, Which is 999, then performs some logic judgment or business operations in the program, and then executes the SQL: update useCount + 1
Before a makes a judgment and no update, B also executes the query SQL statement and finds that after useCount is 999, it also executes the SQL: update useCount + 1.
But in fact, B should not obtain this id because a is already 1,000th users.

So I would like to ask how to use the database only to meet this requirement in high concurrency situations?

Typical concurrency problems. We recommend that you use the optimistic lock mode and take into account the performance. The procedure is as follows:

1. Add 3rd fields of version and int type. The default value is 0. The version value is added with 1 for each update.

ALTER TABLE test_tb ADD COLUMN version INT DEFAULT '0' NOT NULL AFTER useCount;  

2. Get the version value (for example, 3) at the same time during select)

SELECT useCount, VERSION FROM test_tb WHERE id=123456 AND useCount < 1000

3. Check whether the version value is obtained in step 1 during update.

UPDATE test_tb SET VERSION=4, useCount=useCount+1 WHERE id=123456 AND VERSION=3

If the number of update records is 1, the update operation is successful;
If the number of update records is 0, it indicates that it has been updated by other applications (threads) and requires exception processing.

NOTE: For the above ideas, refer to the JPA optimistic lock @ version Processing Mechanism of JavaEE. Here is a reference http://www.blogjava.net/sway/archive/2008/10/10/233569.html

For your current usage, userCount is similar to version, and there are some optimistic locks.

You only need to strictly control the update of userCount, so add a row lock!

select * from table where id=123456 and useCount < 1000 for update;

Update table set userCount = userCount + 1 where userCount = userCount and userCount <1000;
In the InnoDB environment, userCount also needs to create an index in order not to lock the table.