Let’s tackle your questions head-on—this is such a common pitfall when scaling distributed ASP.NET Core apps with shared databases, so it’s great you’re digging into the details.

Is using EF Core at the application layer to validate unique constraints a common practice? #

Short answer: It’s a common initial approach, but not a recommended production practice.

Many developers start here because it feels intuitive—you query the database for the existing record pair, check if it exists, and write only if it doesn’t. But in distributed, high-concurrency scenarios (like your 100 writes/sec across multiple app instances), this method falls apart quickly. Most production-grade setups rely on database-level constraints as the single source of truth, with EF Core handling the outcome of those constraints (like catching duplicate errors) rather than trying to enforce them upfront.

Will this approach cause data inconsistencies or record anomalies? #

Absolutely—race conditions will almost certainly lead to duplicate records or data inconsistencies, even if you wrap the check-and-write in a transaction. Here’s why:

• With default transaction isolation levels (like Read Committed, standard for MariaDB), two concurrent app instances can run the "check for existing record" query at the exact same time. Both will see no existing entry, since the other’s write hasn’t been committed yet.
• Both instances will then proceed to write the duplicate record, and both transactions will commit successfully—leaving you with a violation of your intended unique constraint.

Even if you bump the isolation level to Repeatable Read or Serializable, you’ll hit performance issues (locks will hold longer, leading to timeouts) or still not fully eliminate edge-case risks.

What’s the more reliable alternative? #

The most robust and performant approach is:

1. Define a unique constraint directly on the MariaDB table for the two fields in question. This is the database’s native job to enforce, and it’s optimized for exactly this concurrency scenario.
2. Use EF Core to attempt the write, then catch the unique constraint violation exception (like DbUpdateException with an inner exception signaling a duplicate key). You can then handle this gracefully in your app (e.g., return a user-friendly error message).

This leverages the database’s built-in concurrency controls, avoids race conditions entirely, and keeps your app logic simple. Stored procedures don’t add meaningful value here unless you need to wrap additional business logic—since the core enforcement still relies on the database constraint.

If you want to add application-level checks for UX reasons (e.g., giving users immediate feedback before hitting the database), combine them with the database constraint as the final, non-negotiable guard. But never rely solely on the app-layer check for data integrity.

内容的提问来源于stack exchange，提问作者Sean Stayns