How to design a lottery drawing under high concurrency?

The pseudocode I wrote is as follows, but there is a bug. The lottery now needs to limit the number of times the lottery results appear on a daily basis, but the actual running is not restricted during concurrency. How can this problem be solved? {Code ...} the pseudocode I wrote is as follows, but there is a bug. The lottery now needs to limit the number of times the lottery results appear on a daily basis, but the actual running is not restricted during concurrency. How can this problem be solved?

ResultDayLimitTimes = {resultA => 2 # resultB up to 2 times a day => 5 # resultC up to 5 times a day => 20 # resultD up to 20 times a day => Infinite # appear every day no limit on the number of times} Begin transactionselect * from lottery_chance where id = XX and result = null limit 1 for update # the bug is in the following loop, if resultA has already appeared once today, # There are two people (the XX of these two people is different, so the previous for update pair # cannot limit the concurrency, the previous for update is used to prevent concurrent use of the same Lucky Draw opportunity) # obtain YY = resultA at the same time, because the transaction has not yet been submitted # The yyCount is 1, less than the daily limit of 2, so jump out of the loop, the two people # Both When resultA is in progress, three resultA instances have exceeded the limit. # How can I solve this problem? While true {YY = randomIn [resultA, resultB, resultC, resultD] yyCount = select count (*) from lottery_chance where result = YY and used_time> todayDate if yyCount <resultDayLimitTimes [YY] {break} update lottery_chance set result = YY, used_time = now where id = XXCommit

Reply content:

The pseudocode I wrote is as follows, but there is a bug. The lottery now needs to limit the number of times the lottery results appear on a daily basis, but the actual running is not restricted during concurrency. How can this problem be solved?

ResultDayLimitTimes = {resultA => 2 # resultB up to 2 times a day => 5 # resultC up to 5 times a day => 20 # resultD up to 20 times a day => Infinite # appear every day no limit on the number of times} Begin transactionselect * from lottery_chance where id = XX and result = null limit 1 for update # the bug is in the following loop, if resultA has already appeared once today, # There are two people (the XX of these two people is different, so the previous for update pair # cannot limit the concurrency, the previous for update is used to prevent concurrent use of the same Lucky Draw opportunity) # obtain YY = resultA at the same time, because the transaction has not yet been submitted # The yyCount is 1, less than the daily limit of 2, so jump out of the loop, the two people # Both When resultA is in progress, three resultA instances have exceeded the limit. # How can I solve this problem? While true {YY = randomIn [resultA, resultB, resultC, resultD] yyCount = select count (*) from lottery_chance where result = YY and used_time> todayDate if yyCount <resultDayLimitTimes [YY] {break} update lottery_chance set result = YY, used_time = now where id = XXCommit

For the moment, we can think of the following:
Add another table to specifically record the number of times a result is issued on a specific day. The disadvantage is that the initial data of each result on a daily basis needs to be created in advance, in addition, the efficiency of using row locks in loops in high concurrency is very low, which is unacceptable: <

ResultDayLimitTimes = {resultA => 2 # resultB up to 2 times a day => 5 # resultC up to 5 times a day => 20 # resultD up to 20 times a day => Infinite # appear every day no limit on the number of times} Begin transactionselect * from lottery_chance where id = XX and result = null limit 1 for update todayDate = now. datewhile true {YY = randomcludebeforein [resultA, resultB, resultC, resultD] dayResultTimes = select * from result_day_times where date = todayDate and result = YY limit 1 for update if dayResultTimes ['times '] <resultDayLimitTimes [YY] {break ;}} update result_day_times set times = times + 1 where date = todayDate and result = YYupdate lottery_chance set result = YY, used_time = now where id = XXCommit

There are many points to consider about the lottery. The following three points should be taken into consideration:

• User lottery quota
• Prize quantity limit
• Prize distribution
• Controllability of the probability of winning

User abstraction limit

A user must limit the number of Lottery draws, And the concurrency probability of the same user is actually very small, so here we can use a pessimistic lock to control the number of Lottery draws.

Prize quantity limit

Because the number of concurrent modifications to a prize is very likely, especially some consolation prizes. If we use pessimistic locks here, it is easy to cause lock timeout. So here I choose to use optimistic locks to solve possible concurrent dirty reads.

Prize distribution

In order to prevent the use of scripts to draw a lottery, we need to control the distribution of prize issuance. It takes a time interval to win a prize, of course, it is easy to control by code here (of course, we also need to consider the two awards in concurrency, and we can also control it through optimistic locks)

Controllability of the probability of winning

When we started to estimate that there would be about people in the lottery, I designed the lottery with a probability, however, it was suddenly discovered that when the activity started, the number of lottery winners reached 5 million after a small hour. At this time, we needed to dynamically adjust the probability of winning the prize. The best way to do this is not to write the introduction to winning in the database, but to passNumber of winners/participantsSo that the probability of winning is changed dynamically.

Optimization

If the concurrency is too large, the database QPS is abnormal. You can add a cache in front of the database to block the data that needs to be written into the database and put it into the queue. When using this architecture, you need to consider data consistency issues, such

• How can we ensure that the data in the database is consistent with the cached data?
• If the queue crashes, how can we ensure that the cached data can be updated to the database in a timely manner. If the cache goes down, how can we ensure that the lottery can continue? (of course, we can compromise the business here. If the cache goes down, the entire lottery goes down. If it is too late to write data into the database, if these events do not happen, some people may win more than a limited number of lotteries, or some prizes may win more than a limited number of times)

I am not very familiar with some solutions to exceptions during the optimization process. If you want to know more, please kindly advise.

Appendix (simple flowchart)

Insert the winning result and then obtain the winning Quantity Based on the unique primary key.

Select count (*) from table where id <[new insert id]

If the number exceeds the returned result, the backend can obtain the first n according to the limited number of winners.

The lottery in high concurrency is similar to the flash sales system.

For example, there are only a few winners, and most won't. Therefore, the first step is to limit that only a few users' requests can be used to obtain the real lottery logic. In this way, the pressure on the server is much lower.

Users who actually participate in the lucky draw should first randomly check whether the user can win the prize or not. If the user is in the middle of the prize, check whether the user has the prize (if not, the user is not in the middle of the prize ). In this way, some database requests are reduced.

In this way, high concurrency is limited to the http server, and the pressure on the database is not great.

Let's look at the winning logic. When there is no lottery yet, it is equivalent that someone has already won the prize.

When a request is sent to the server, the winning results have been distributed in the process, and the lottery process shows a simple result. In this way, there is no pressure on the lottery. Non-winning users do not follow the lottery logic. The pressure naturally falls.

Refer to Xiaomi's mobile phone snatching

I think this is a problem of design ideas,
You should first obtain the number of prizes that have been issued on the day and add the exclusive lock.

yyCount =  select count(*) from lottery_chance where result=YY and used_time > todayDate for update

In this way, when there is concurrency, the number of prizes can ensure that there is a unique process (the prize is equal to YY) in the lucky draw, other processes are waiting to lock the status, until the last lottery ends, and then get the lock, continue the following operations

I am afraid it is not suitable for high concurrency users who want to solve problems with mysql.
We recommend that you consider the queue or put the data in the single-line data processing function such as redis.
Memcache doesn't work, so pay attention to it.
Reids is a good choice. You can consider using it or using the queue system for processing.

Under high concurrency, I think there are several steps:
1. Whether to discard the lucky draw request (some requests are directly discarded Based on the server's capacity );
2. Whether the lottery is in line with the business logic (whether there are any remaining lottery times, etc );
3. If there are multiple prizes, remove the prize that does not comply with the rules (for example, some prizes can only be one time, and the user has already won one prize );
4. select from the remaining prizes according to the rules (the rules here may be simple or complex );
5. If you get a prize, immediately try to get the prize from the prize Queue (this can be a cache queue or a database record, but you must ensure the atomicity of this operation, if the prize is obtained successfully (that is, the prize is still available), the user is notified that the prize has been received.

The best way is optimistic lock.
As long as it is a lucky draw, there must be concurrency problems.
If there are interests involved, someone must send a request through tools.