Transaction control between two different applications

Question

I am working on an application that is divided into two modules, each one in a language (.NET and Java).

Application A (.NET) will manipulate the records of a table while application B (Java) will manipulate it at the same time.

Scenario: Imagine that I have a Client and Balance structure in the account.

When the customer does not exist, both applications can register it. In order for the client not to be duplicated in the database, a Unique was created in the database (Oracle).

So far so good, there will be no client twice. The problem begins when both applications are making the moves in the customer account.

Example: Imagine that the application . Net makes a deposit of R $ 50,00. At this time the record in the table will be 50 of balance. But if at the same time this deposit, a routine was made that withdraws R $ 10,00 of this client’s balance.

I would need to control the transaction between two applications, IE, if a deposit of R $ 50,00 and a withdrawal of R $ 10,00 at the same time (hypothetically speaking), the final balance of the customer should be R $ 40,00.

The scenario that I have today, the applications recover the customer’s record, perform the calculation and return to the table the updated value, but there is an overlap of the data. For example, the transaction . NET launches balance of R $ 50,00 and the Java application launches withdrawal of R $ 10,00 at the same time. As for the application . NET recovers the client’s centralized record, it has 0 balance, as does the Java application. If the application . NET finish first, it launches 50 of balance and then the Java application launches 10 of withdrawal, getting the record in the table of -10.

Is there any way to control this between the two applications only with transaction each application with yours? I’ve been thinking and imagined a scenario where there should be a service using LOCK to treat this type of operation. Imagine another way to solve?

Note: it is a legacy system, change the way of storage of the balance would be virtually unviable.

Thanks!

Answer 1

Well, the most obvious suggestion would be to add a column to make an optimistic lock (see examples of this here and here, it’s also easy to find more examples on google).

However, since this is a legacy system where it is difficult or impossible to move the database structure, I think the best suggestion is the following:

1. Create an application (let’s call it X) that has exclusive access to the database and controls it exclusively.

2. The X application knows and guarantees the business rules of database use.

3. The X application could then provide the service provided by the database through REST, webservices, sockets, etc. The important thing is that X should expose only operations that allow the database to be changed or consulted in ways that make sense for the system’s business rules. That is, no providing a generic update service or something that gets Sqls instructions directly.

4. Modify the other applications (let’s call them A and B) so that all of them can only have access to the data through the X application. The other applications would then be summarily prohibited from connecting directly to the database.

This approach eliminates its fundamental problem, which is to have two applications accessing the database at the same time. It is true that you get a new problem, which is to need an extra application to control all of this (and in a way you can say that you have turned an A problem into another B problem only). However, in this X application, you now have full control of access to data and system business rules, something you didn’t have when the applications all went directly to the database. It also helps you centralize and unify business rules, eliminating with this, much duplication of efforts resulting from the fact of having the same business rules implemented in two or more different places and also ensuring that the implementation of these rules is the same for everyone (because it will be in a single place).

It is obvious that this approach would take a lot of work to implement completely, but it is possible to implement it in stages or only partially in the parts where this is necessary. Choose the most critical cases (for example, the one of the client balance change) and put it in the X application and change both the A and B application to use the X application in this case. Then choose another feature and do the same. Do this until all the features are within the X application (or at least all the features you think are worth the effort to do so).

Finally, depending on how your applications are, it may be easier to choose one of them (let’s say A) to be X and make the other (B) access the first one. Or it may be easier to take one of them (A) and divide it into two: the X and the original application A, making B access X.

I recommend reading this question and also my answer there.