Cause of the matter

Requirement: At 7:00 PM the day before each offline activity, send a short message to the users who sign up for the Value Study Club and remember to attend the activity on time.

Note: Since our business concurrency is not very large, some problems in the case of concurrency are not taken into account in many scenarios. This requirement happens to be implemented through crontab, and the automatic elastic scaling of the server is added, so it is equivalent to simulating a concurrent business scenario.

A brief introduction to the database table structure:

All of these schemes rely on corntab execution once a day at 7 p.m.

Plan 1

Check whether there are offline activities meeting the requirements in the activity table according to the start time. If so, filter the UID with the field of send_SMS 0 by associating the activity ID with the sign-in table, and associate the user table with the mobile phone number and other information. After sending, update the send_SMS field.

Disadvantages: In concurrent service scenarios, dirty read may occur, causing multiple SMS messages to be sent.

Scheme 2

Similar to scenario 1, the only difference is that the transaction is open when querying with SELECT… FOR UPDATE, lock the result row at SELECT time to avoid dirty reads, UPDATE send_SMS in the same transaction, and commit.

Disadvantages: Cannot be rolled back because SENDING SMS messages is not a database operation. So if a rollback occurs during execution, the SMS message is already sent, but the database is rolled back and the send_SMS field is set to 0, which creates a contradiction. And deadlocks can occur if it’s a time-consuming task.

Here is the logic to execute

BEGIN;
SELECT ... FOR UPDATE;
UPDATE ... SET send_sms = 1;
COMMIT;
Copy the code

Plan 3

Similar to scenario 2, the only difference is to lock the data one by one and update the send_SMS field.

Disadvantages: to write a loop has been to query the conditions but has not sent SMS users. The problem of dead loops and deadlocks is not handled properly.

Plan 4

This is the best solution I can think of so far, just use the SELECT statement to SELECT all the mobile phone numbers and SMS content that meet the criteria, put them in the Queue, and then implement the Queue processing. SELECT… FOR UPDATE determine the value of the send_SMS field, if it is 0, send SMS, then UPDATE the send_SMS field to 1, and COMMIT. This way, you can avoid sending text messages multiple times.

Conclusion: It is very convenient to use Queue for such business scenarios with less real-time requirements. Queue processing one by one also plays a role of peak cutting and decoupling in complex systems. What kind of applications do you have for Queue at work? Welcome to leave a message and discuss together!

Do you have any suggestions for these solutions? Welcome to comment!