Because I want to do a concurrent process (the concurrency here is small concurrency, that is, the concurrency of several people), the background administrator operates a piece of repayment data to prevent several managers from operating one piece at the same time without knowing it. Repayment, error occurred.
My approach is to add a field lock_time to the table and query the data that lock_time is equal to 0. After querying it, change lock_time to the current time (I added lock_time=0 as the where condition during update).
Question: Will there be any concurrency if I do this?
That is to say, under an ideal environment, two people can query a piece of data at the same time (querying "simultaneously" within a minute or a second),
Similarly, is it possible for two people to change a certain field of a piece of data at the same time (changed "at the same time" within a minute or a second),
为情所困2017-05-16 13:06:36
The answer to your question is completely possible. You should use locks and transactions to avoid these problems instead of using a new field
黄舟2017-05-16 13:06:36
You can take a look at the concept of optimistic locking. Add a version number field, you can ignore it when reading data, check the version number when updating data, and update the version number. The difference between the version number and your lock_time is that duplication may occur if the lock_time is not accurate enough. The version number is always +1, and there is no problem of duplicate versions
高洛峰2017-05-16 13:06:36
Locks and things should be able to meet your requirements. Let’s take a look at these two concepts of Mysql in detail.
我想大声告诉你2017-05-16 13:06:36
Your idea is close to optimistic locking. Change lock_time to a timestamp for verification. Compare the found timestamp with the one you modified and then submit. It is also possible to use InnoDB's row lock for small concurrency, and there is basically no performance difficulty.
