The traditional SQL databases are used by developers as a fully safe storage. The ACID properties are intuitive for us and give us a sense of safety during application development. We know that if during a database transaction a network error occurred and we received an exception, the whole transaction would be rolled back as atomic part of process. To avoid network issues we can retry the operation later and it should solve the problem. It is true in most cases, but there is one when it is not so easy. Let me drill down this subject.
Sometimes system flexibility makes the solution complicated 😉 . Imagine a ‘simple’ situation that you have a system in which a client can define new attribute and assign multiple values to it. Later, the client can assign these values to the Customers. If you do not plan to develop new attributes’ set every time a client wants to make a change, you will need to prepare a ‘dynamic’ structure in the database.
Distributed Transaction Coordination is sometimes slow, but guarantees the system consistency. You do not need to care about infrastructure things. I will describe a problem that appears when you have no DTC and you have two independent databases.
SQL query engine is prepared to return correct results as soon as possible. But correctness means being correct from mathematical perspective. Problem that I will describe is obvious, but it is not what you sometimes expect from SQL engine.