0

I have enabled the sql logs in a SpringBoot application and I see that even I gave an order to the way I would like the updates to be executed, the logs shows that updates are being done in other order, I have 2 tables and the method that is updating those 2 tables is annotated with @Transactional.

I am wondering about this because when I update one of those tables the other one should also be consistent with this update, so I wanted to first update the one that needs to be consistent, but I think in my case there is no problem because at the end when the method returns succesfully the "commit" will be executed and the order wont matter because it is like an snapshot being saved in the database right?

for example:

Suppose we have 2 tables, "configuration" and "versioning".

Inside @Transactional method I do as follows:

@Transactional
public void update(){
  configurationsRepository.saveAll(configurations);
  versioningRepository.saveAll(versioning);
}

I need the configurations to be saved first because versioning is the one who knows which configurations are "productive", but when I see the sql logs I see that the versionings are being save/updated first which is "bad" because maybe it could point to non existant configurations.

So what would be a bad scenario is that versioning gets called and in those milliseconds of difference configurations is not updated... So far this has not happened, but I wonder if it can happen or my assumption about the commit at the end of the transaction is right, both will be updated at the same instant? And I believe that is the purpose of this Transactional hehe that everything gets saved or nothing gets saved at some instant

BugsOverflow
  • 386
  • 3
  • 19

1 Answers1

0

@Transactional means it will run in a transaction. The purpose of transactions is that they are atomic meaning they either run completely or not at all.

The application first tells the database to start a transaction, sends all the changes to the DB and "commit" (apply the changes) when finished. If anything failed, the transaction will be "rolled back" (reverted).

As explained in this question, you can set isolation levels for configuring what other DB connections see. If you want to run the operations in a sequence, you can use ISOLATION_SERIALIZABLE. If you're using ISOLATION_READ_COMMITTED, it will either get the state before the transaction or after the transaction but nothing in between.

dan1st
  • 12,568
  • 8
  • 34
  • 67
  • thanks for your answer, i am not specifying any isolation level or propagation, it is default annotation at the moment, the last part when you say "it will either get the state before the transaction or after the transaction but nothing in between", that is good for my use case cause if versioning is read with the old value, it will be pointing to an existing configuration always. And then after this transaction, if it is read with the new value, I believe that then also configurations is ready with its new values (inserts or updates from this transaction) is this right? – BugsOverflow Jul 22 '23 at 06:46
  • The [default isolation level depends on the underlying data store](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/transaction/annotation/Isolation.html#DEFAULT) i.e. your JDBC connection/DB. Whether other connections can read intermediate results depends on the isolation level. – dan1st Jul 22 '23 at 06:54
  • Thanks mate, I am using mysql at the moment, with hikari connection and the driver is com.mysql.cj.jdbc.Driver – BugsOverflow Jul 22 '23 at 06:59
  • According to https://stackoverflow.com/a/58472480/10871900, it seems the default isolation level is `TRANSACTION_READ_COMMITTED` but it may be version dependent. – dan1st Jul 22 '23 at 07:05
  • Okay ty, I believe im good for now but going to run some stress tests trying to get this worst scenario I was saying, hopefully it doesnt happen lol, but if it does I might change the isolation to serialization, ty – BugsOverflow Jul 22 '23 at 07:14
  • 1
    You could also use use `Thread.sleep` or similar when testing (at different places) and see what happens. – dan1st Jul 22 '23 at 07:29