preface

What is idempotency? One or more requests for a resource have the same impact on the resource itself as a single execution.

Idempotence is a promise made by a system service that the impact of multiple invocations on the system is consistent as long as the invocation interface succeeds.

Idempotent vs. repeated commits

Idempotent is more commonly used when the result of the first request is known, but is not returned due to network jitter or connection timeout. In this case, the system automatically sends a request again to confirm whether the first request is completed.

Repeated submission is more commonly used when multiple operations are performed artificially after the first request succeeds or the request result is not returned yet.

Idempotency of SQL statements

SELECT

SELECT * FROM `user` WHERE id = 1
Copy the code

No matter how many times the query is executed, it has no impact on the resource, and queries are inherently idempotent.

UPDATE

UPDATE `user` SET status = 1 WHERE id = 1;
Copy the code

The state is the same no matter how many times the execution succeeds. This scenario is an idempotent operation.

UPDATE `user` SET score = score+1 WHERE id = 1;
Copy the code

The result of each execution changes; this scenario is not an idempotent operation.

SQL > create SQL

UPDATE `user` SET score = score+1 WHERE id = 1 AND score = 59;
Copy the code

The score is the same no matter how many times the execution succeeds. This scenario is an idempotent operation.

DELETE

DELETE FROM `user` WHERE id = 1;
Copy the code

The data is consistent no matter how many times the execution succeeds. This scenario is an idempotent operation.

INSERT

INSERT INTO `user` (`name`, `status`, `score`) VALUES ('tom', 1, 80);
Copy the code

The result of each execution changes; this scenario is not an idempotent operation.

Depending on the scenario, you can create a unique index for name, or execute SQL of type:

INSERT INTO ... values ... ON DUPLICATE KEY UPDATE ... Note that to use this statement, the table must have a unique index or primary key.Copy the code

Implementation scheme

Plan a

The downstream system provides corresponding query interfaces.

After timeout, the upstream system first goes to query. If the query is found, it indicates that it has done it. If it succeeds, it does not need to do it; if it fails, it goes through the failure process.

Scheme 2

This query is handed over to the downstream system, which simply retries, and ensures that the impact of one and multiple requests is the same. At this point we say that the interface provided by the downstream system supports idempotency.

summary

Idempotent concerns whether multiple requests have side effects on the resource, not the consequences. The SELECT statement may have inconsistent data for each query, but it is idempotent.

As for the implementation scheme -> the specific implementation scheme of scheme 2, appropriate solutions are considered according to the actual situation of the business. For example, idempotent can be realized through SQL statements, so there is no need to introduce a solution with globally unique ID.

Recommended reading

  1. Understanding distributed transactions
  2. The ultimate consistency implementation scheme for distributed transactions
  3. Distributed asynchronous communication component selection
  4. Distributed configuration center