Data consistency in Entity Framework Core

Theoretical

What is a transaction?

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.


Some well-known SQL transaction statements:

  • Begin transaction;

  • Commit transaction;

  • Rollback transaction;


Properties of database transactions (ACID)

  • Atomic — all or nothing

  • Consistent — no constraints violated

  • Isolation — sessions don’t effect each other

  • Durable — once data is committed, it is permanent


“A” and “D” are more or less easy to understand and are implemented out of the box by DB server. But “C” and “I” require more attention because this is our responsibility to implement them correctly. In this article, I will cover “C “— consistency.


Why can a transaction fail?

  • Constrains violated

  • Datatype mismatch

  • etc.

Examples of SQL constrains

  • NOT NULL - Ensures that a column cannot have a NULL value

  • UNIQUE - Ensures that all values in a column are different

  • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

  • FOREIGN KEY - Prevents actions that would destroy links between tables

  • CHECK - Ensures that the values in a column satisfies a specific condition

  • DEFAULT - Sets a default value for a column if no value is specified