Database locks solve concurrency problems

problem Description: a coupon activity, the user can collect coupons, but a coupon activity to receive a limited number of tickets, so users need to collect at the time must first count the number of coupons to receive. Then generate this coupon to collect records. Then there will be concurrency problems, when more than one user to collect the same coupon activity, they counted the number of coupons received less than the limit can be picked up, so can be performed to generate coupons to collect records of the operation, but the remaining can be picked up less than the number of users.

How to solve this problem, first we will think, in the program using the Synchronized keyword to lock the method of collecting coupons, then you can achieve, when a person in the collection of coupons, other people wait, but the program is distributed to multiple servers, in the distributed situation, This method has no effect, because the program can only do to lock the operation on a single server.

Since the program can not be implemented, then we would like to use the database to implement, because I am now doing this project, the database is useless from the library, only a database server, if the database is also distributed, it will have to seek another method.

Think of using the database to solve, the first thought is to use the atomicity of the transaction to solve, that is, the statistics of the number of operations and generate coupon records of the operation to become a transaction, the surface feel can, but in fact, this is wrong. Because the same thing happens when multiple transactions are processed at the same time, concurrency problems also occur.

Therefore, it is not possible to use transactions alone, and the locking mechanism of the database must also be used.

InnoDB implements the following two types of row locks.

• Shared Lock (S): Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set (readable and not writable).

• Exclusive Lock (X): A transaction that allows an exclusive lock to update data to prevent other transactions from acquiring shared read and exclusive write locks of the same data set (cannot be read or written).

Lock range: Row lock: Adds a lock to a row of records; Table Lock: Lock the entire table

Here we need to use an exclusive lock, that is, when a thing in the operation of the time, other things can not be counted to collect the number of coupons, only after the completion of the collection can be counted. The range of locks is row.

The method of using exclusive locks is actually pessimistic locking mechanism, we also have optimistic lock, next we introduce the difference between the two.

Pessimistic lock: It is assumed that concurrency conflicts occur and that all operations that may violate data integrity are masked.

Pessimistic locking assumes that other users are trying to access or change the object you are accessing, changing the probability is very high, so in a pessimistic lock environment, before you begin to change this object to lock the object, and until you commit the changes before releasing the lock. The pessimistic flaw is that whether it is a page lock or a row lock, the lock-up time can be very long, which may lead to limited access to other users, that is, pessimistic lock concurrent access is not good.

Optimistic Lock: Assume that there will be no concurrency conflicts and only check for violation of data integrity when committing the operation.

Optimistic locking does not solve the problem of dirty reading. Optimistic locking holds the probability that other users are trying to change the object you are changing, so the optimistic lock locks the object until you are ready to commit the changes, and does not lock when you read and change the object. It can be seen that the optimistic lock and lock time is shorter than the pessimistic lock, and the optimistic lock may obtain better concurrent access performance with larger lock granularity. But if the second user reads the object exactly before the first user commits the change, the database will find that the object has changed when he has made his own changes, so that the second user has to reread the object and make changes. This means that in an optimistic lock environment, the number of times the object is read by the concurrent user is increased.

If we use optimistic locking, we need to give the database the number of the current coupon activity has been picked up the field, each time we modify the value of the field to judge it, if he is less than the number of recipients, then long can insert this coupon record. This judgment and update is in the same SQL, which takes advantage of the atomicity of a SQL to avoid concurrency conflicts.

Reference

• Select+update processing concurrency update problem solution in MySQL

• Transaction processing and locking statements

Database locks solve concurrency problems