Spring transaction with multiple datasources

In the previous article, we learned how to connect to multiple datasources in Spring Boot. Although this is an anti-pattern, there is sometimes no easy way to get rid of it because of various reasons. Developers may often face a problem in which they must perform database operations under a single transaction in multiple datasources in a particular order. Then persist the final result if all other previous operations have succeeded. It sounds a bit daunting but don’t worry. In this article, we learn how to use Spring transaction with multiple datasources to achieve cross-database consistency.

An imaginary example

We continue with the previous Songs’ Lyrics example discussed in the previous article. If you have not read it yet, check it out here. But for this case, we want our datasources to support transactions. That means if a record successfully persisted to the lyrics table, ds1, then the code writes a song record to the songs table, ds2. However, if anywhere an error occurs, the entire transaction should roll back. For example, assume a record was successfully inserted into the lyrics table. However, due to a connection issue, it failed at the songs table. In that case, the entry in the lyrics table should roll back as the entire transaction has failed.

To understand it better, check the following flowchart,

Spring Boot multiple datasources transaction support flow

Configuring Spring Boot to handle transactions with multiple datasources

Since we already have set the multiple datasources up, we need to configure the Spring Boot to handle the transaction. To do that we need to create a @Bean called ChainedTransactionManager which manages both lyricsTransactionManager and songTransactionManager under a single umbrella transaction manager. See below,

package com.madadipouya.example.multiple.datasource.config

import org.springframework.beans.factory.annotation.Qualifier
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.springframework.data.transaction.ChainedTransactionManager
import org.springframework.transaction.PlatformTransactionManager

@Configuration
class ChainedTransactionManagerConfig {

    @Bean(name = ["chainedTransactionManager"])
    fun createChainedTransactionManager(@Qualifier("lyricsTransactionManager") lyricsEntityManagerFactory: PlatformTransactionManager,
                                        @Qualifier("songTransactionManager") songsEntityManagerFactory: PlatformTransactionManager): ChainedTransactionManager {
        return ChainedTransactionManager(lyricsEntityManagerFactory, songsEntityManagerFactory)
    }
}

After that, we need to annotate the method that we want to apply the transaction with @Transaction annotation. In this case, we have to annotate DefaultLyricsService#addLyrics like this,

package com.madadipouya.example.multiple.datasource.lyrics.service.impl

import com.madadipouya.example.multiple.datasource.lyrics.entity.Lyrics
import com.madadipouya.example.multiple.datasource.lyrics.repository.LyricsRepository
import com.madadipouya.example.multiple.datasource.lyrics.service.LyricsService
import com.madadipouya.example.multiple.datasource.song.entity.Song
import com.madadipouya.example.multiple.datasource.song.service.SongService
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional

@Service
class DefaultLyricsService(val songService: SongService, val lyricsRepository: LyricsRepository) : LyricsService {

    @Transactional(value = "chainedTransactionManager", rollbackFor = [Exception::class, java.lang.Exception::class])
    override fun addLyrics(artistName: String, songName: String, lyrics: String): Song {
        val lyricsEntity = lyricsRepository.save(Lyrics(lyrics))
        val lyricsId: Int = lyricsEntity.id!!
        return songService.addSong(artistName, songName, lyricsId)
    }
}

In the above code there are a couple of important things to note:

Use Spring @Transactional annotation. Not Jakarta. Otherwise, it will not work.
The chained transaction manager’s name should be passed as the transaction manager.
The rollbackFor can be anything. In this scenario, we roll back on any Java or Kotlin error.

To test whether the transaction works fine, once the application is started we can kill the songs database docker, MySQL one, and try to insert lyrics by calling POST /v1/lyrics. We will see that nothing will be inserted in the lyrics database either. To put it in simple words,