Under high concurrency, should the database foreign key constraint be used?

Trade-offs on database foreign key constraints in high concurrency scenarios

In projects that pursue high concurrency performance, a controversial question is often encountered: Is it necessary to use foreign key constraints in the database? Some people say that in order to improve efficiency, foreign keys need to be prohibited. What is going on?

In fact, in some high concurrency scenarios, it is indeed necessary to choose not to use the physical foreign key constraints provided by the database. This does not mean that the concept of foreign keys has been completely abandoned, but rather that it avoids relying on the foreign key features provided by the database itself to ensure data consistency. The reason is that foreign key constraints at the database level will affect the database's write operation performance, especially in high concurrency environments, foreign key inspection will become a performance bottleneck.

So, if physical foreign keys are not used, how can the consistency of data be guaranteed? The answer is: it is implemented through the business layer. For example, when the primary table data is deleted, the corresponding records in the association table also need to be deleted. Using physical foreign keys can be easily implemented through cascading deletion, but if physical foreign keys are disabled, you need to explicitly write code at the business logic layer to delete the main table and the associated table respectively. Similarly, the update operation of foreign key constraints also needs to be processed at the business layer.

In this way, the atomicity of database operations is transferred from the database layer to the application layer. The application layer needs to ensure the atomicity of operations, and usually requires a transaction mechanism to ensure the integrity of data.

It is worth noting that in many high concurrency scenarios, ultimate consistency is enough. This means that data consistency does not have to be strictly required to be completed at the same time, allowing for a short time window, and the data may be in an inconsistent state, but will eventually reach consistency. This is different from strong consistency (data must be consistent at any time). Ultimate consistency can better adapt to high concurrency environments and improve system throughput. Therefore, abandoning the physical foreign keys of the database and being responsible for data consistency is a feasible solution in many high concurrency scenarios.

The above is the detailed content of Under high concurrency, should the database foreign key constraint be used?. For more information, please follow other related articles on the PHP Chinese website!