How MySQL implements counters update and maintain data correctness in high concurrency scenarios

A table of two fields an ID one usecount
100 IDs are stored in the table, each with its own usecount
The business scenario is: When the ID is used each time usecount to add 1. This ID cannot be used when Usecount is greater than 1000 (in other words, it cannot be isolated from the database)

In the case of high concurrency, there is a problem: Suppose that a record in the data table is id=123456; usecount=999
A and B two connection concurrent queries this ID 123456 executes the following SQL:

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

A first execution gets the ID 123456 usecount is 999 after doing some logic judgment in the program or after the business operation executes Sql:update Usecount + 1
b also executes a query SQL Discovery Usecount is 999 after a decision is made and no update is performed sql:update Usecount +1
But in fact B should not get this ID because a is already a 1000th user

So ask me. How do I accomplish this requirement only with a database in high concurrency situations?

Reply content:

A table of two fields an ID one usecount
100 IDs are stored in the table, each with its own usecount
The business scenario is: When the ID is used each time usecount to add 1. This ID cannot be used when Usecount is greater than 1000 (in other words, it cannot be isolated from the database)

In the case of high concurrency, there is a problem: Suppose that a record in the data table is id=123456; usecount=999
A and B two connection concurrent queries this ID 123456 executes the following SQL:

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

A first execution gets the ID 123456 usecount is 999 after doing some logic judgment in the program or after the business operation executes Sql:update Usecount + 1
b also executes a query SQL Discovery Usecount is 999 after a decision is made and no update is performed sql:update Usecount +1
But in fact B should not get this ID because a is already a 1000th user

So ask me. How do I accomplish this requirement only with a database in high concurrency situations?

A typical concurrency problem. Optimistic locking mode is recommended for both performance. The steps are as follows:

1. Add the 3rd field version, type int, and default value of 0. The version value is added 1 per update.

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

2. Select to get the version value at the same time (for example, 3)

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

3, update check if the version value is the 2nd step to get the value

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

If the record number of the update is 1, it is successful;
If the update has a record number of 0, it means that it has already been update by another application (thread) and requires exception handling

Note: The above ideas refer to the JPA optimistic locking @version processing mechanism from Java EE. Here's a reference http://www.blogjava.net/sway/archive/2008/10/10/233569.html

For the way you use it now, UserCount is similar to version, some of which are similar to optimistic locking.

Just you have to strictly control the UserCount update, then add the line lock Bai!

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

Update table Set UserCount = UserCount + 1 where UserCount = UserCount and UserCount < 1000;
InnoDB environment in order to lock the table, UserCount also need to build an index.

