Background#
The title is actually an SQL statement. Today, I encountered this SQL and recorded its characteristics and issues to pay attention to. Recording the issues after encountering them will deepen my understanding...
Syntax Meaning#
The literal meaning of the SQL statement is to insert a piece of data and update certain fields when a column field is equal to xx. For example, in a shopping mall system, there is an order table called "order". When the order is identified by the order ID 1123, the name information of the order is updated. The corresponding SQL statement is:
insert order on order_id = 1123 update order_name = xxx
The order ID is a unique index, so inserting data in this way can avoid duplicate unique index columns, which may cause the order insertion to fail. This can prevent data conflicts. If the column after "on" is not a unique index, in the case of multi-threading where multiple threads are accessing, duplicate data will occur, resulting in concurrency issues.
Application Scenarios#
As mentioned above, the main application scenario is in a concurrent environment with a unique index column, which can solve concurrency issues.
Scenario Comparison#
Distributed locks can also solve concurrency issues. The differences between the two are as follows:
- Distributed locks are implemented based on Redis. The lock time can affect the system's load if not chosen properly, while using MySQL does not have this impact.