Distributed transactions across mysql, redis and Mongo


Mysql, redis and Mongo are very popular storage, and each has its own advantages. In practical applications, many kinds of storage are often used at the same time, and the need to ensure data consistency in many kinds of storage is also met, such as ensuring that the inventory in the database is consistent with that in redis.

This paper is based on distributed transaction frameworkhttps://github.com/dtm-labs/dtmThis paper presents a runnable distributed transaction instance across mysql, redis and Mongo storage engines, hoping to help you solve this problem.

This flexible ability to combine multiple storage engines to form a distributed transaction is also the initiative of DTM. At present, no other distributed transaction framework has such ability.

Problem scenario

Let’s look at the problem scenario first. Suppose that users now participate in an activity and recharge their balance, and the activity will give mall points. Among them, the balance is stored in mysql, the call fee is saved in redis, and the mall points are saved in Mongo. Due to the time limit of the activity, it may fail to participate in the activity, so it is necessary to support rollback.

For the above problem scenarios, Saga transactions of DTM can be used. Let’s explain the scheme in detail below.

Prepare data

The first is to prepare the data. In order to facilitate users to quickly start the relevant examples, we have prepared the relevant data at en dtm. Pub, which includes mysql, redis and Mongo. The specific connection user name and password can be found inhttps://github.com/dtm-labs/d…Found.

If you want to prepare the relevant data environment locally, you canhttps://github.com/dtm-labs/dtm/blob/main/helper/compose.store.ymlStart mysql, redis and Mongo, and then use thehttps://github.com/dtm-labs/dtm/tree/main/sqlsThe following script prepares the data of this example, wherebusi.*For business data,barrier.*Auxiliary table used for DTM

Write business code

Let’s first look at the most familiar business code of MySQL

func SagaAdjustBalance(db dtmcli.DB, uid int, amount int) error {
    _, err := dtmimp.DBExec(db, "update dtm_busi.user_account set balance = balance + ? where user_id = ?", amount, uid)
    return err

This code is mainly used to adjust the user balance in the database

For saga transaction mode, when we roll back, we need to reverse adjust the balance. For this part of processing, we can still call the above methodSagaAdjustBalance, you only need to pass in a negative amount.

For redis and Mongo, the processing of business code is similar. You only need to increase or decrease the corresponding balance

How to be idempotent

For saga transaction mode, when our sub transaction service has a temporary failure, it will retry. This failure may occur before or after the sub transaction is committed. Therefore, the sub transaction service needs to be idempotent.

DTM provides auxiliary tables and auxiliary functions to help users quickly realize idempotence. For MySQL, it will create an auxiliary table barrier in the business database. When the user starts the transaction to adjust the balance, it will first write GID in the barrier table. If this is a repeated request, it will find duplication and fail when writing GID. At this time, the balance adjustment on the user’s business will be skipped to ensure idempotency. The usage code of auxiliary function is as follows:

app.POST(BusiAPI+"/SagaBTransIn", dtmutil.WrapHandler2(func(c *gin.Context) interface{} {
    return MustBarrierFromGin(c).Call(txGet(), func(tx *sql.Tx) error {
        return SagaAdjustBalance(tx, TransInUID, reqFrom(c).Amount, reqFrom(c).TransInResult)

Mongo’s principle of dealing with idempotence is similar to that of MySQL and will not be repeated

The principle of redis dealing with idempotence is different from that of MySQL, mainly because the principle of transaction is different. Redis transactions are mainly guaranteed through Lua’s atomic execution. The auxiliary function of DTM will adjust the balance through Lua script. Before adjusting the balance, query GID in redis. If it exists, skip the balance adjustment in business; If it does not exist, the business balance adjustment is performed. The usage code of auxiliary function is as follows:

app.POST(BusiAPI+"/SagaRedisTransOut", dtmutil.WrapHandler2(func(c *gin.Context) interface{} {
    return MustBarrierFromGin(c).RedisCheckAdjustAmount(RedisGet(), GetRedisAccountKey(TransOutUID), -reqFrom(c).Amount, 7*86400)

How to make compensation

For saga, we also need to deal with the compensation operation, but the compensation operation is not a simple reverse adjustment, and there are many pits to pay attention to, otherwise it is easy to make compensation errors.
On the one hand, idempotency needs to be considered in compensation, because fault retry also needs to be considered in the compensation process, which is the same as idempotence processing in the previous section. On the other hand, null compensation needs to be considered for compensation, because the forward branch returns a failure. This failure may be after the forward data has been adjusted and submitted, or it may return a failure before it has been submitted. For the failure of data submission, we need to perform the reverse operation. For the failure of data non submission, we need to skip the reverse operation, that is, deal with null compensation.

In the auxiliary tables and functions provided by DTM, on the one hand, it will judge whether the compensation is null according to the GID inserted in the forward operation, on the other hand, it will insert GID + ‘compensate’ to judge whether the compensation is a repeated operation. If it is a normal compensation operation, the business compensation will be executed. If it is empty compensation or repeated compensation, the compensation in the compensation business will be skipped.

The MySQL code is as follows:

app.POST(BusiAPI+"/SagaBTransInCom", dtmutil.WrapHandler2(func(c *gin.Context) interface{} {
    return MustBarrierFromGin(c).Call(txGet(), func(tx *sql.Tx) error {
        return SagaAdjustBalance(tx, TransInUID, -reqFrom(c).Amount, "")

Redis code is as follows:

app.POST(BusiAPI+"/SagaRedisTransOutCom", dtmutil.WrapHandler2(func(c *gin.Context) interface{} {
    return MustBarrierFromGin(c).RedisCheckAdjustAmount(RedisGet(), GetRedisAccountKey(TransOutUID), reqFrom(c).Amount, 7*86400)

The compensation code is almost the same as the previous forward operation code, only multiplying the amount by – 1. The auxiliary function of DTM will contain the logic related to idempotence and compensation in one function at the same time

Other exceptions

When writing sub transactions and compensation for sub transactions, in fact, there is another exception: suspension, which may occur when the global transaction times out and rolls back, or when the retry reaches the online, the normal situation is to operate forward first and then compensate, but in extreme cases, it may occur to compensate first and then operate forward. Therefore, the forward operation also needs to judge whether the compensation has been executed. If it has been executed, the business operation also needs to be skipped.

For DTM users, these exceptions have been handled gracefully and properly. As users, you only need to follow the above stepsMustBarrierFromGin(c).CallJust call, and you don’t need to care about these exceptions at all. The principle of DTM handling these exceptions is described in detail here:Exceptions and sub transaction barriers

Initiate distributed transaction

All sub transaction services have been written before. The following code initiates a saga global transaction:

saga := dtmcli.NewSaga(dtmutil.DefaultHTTPServer, dtmcli.MustGenGid(dtmutil.DefaultHTTPServer)).
  Add(busi.Busi+"/SagaBTransOut", busi.Busi+"/SagaBTransOutCom", &busi.TransReq{Amount: 50}).
  Add(busi.Busi+"/SagaMongoTransIn", busi.Busi+"/SagaMongoTransInCom", &busi.TransReq{Amount: 30}).
  Add(busi.Busi+"/SagaRedisTransIn", busi.Busi+"/SagaRedisTransOutIn", &busi.TransReq{Amount: 20})
err := saga.Submit()

In this part of the code, a saga global transaction is created. The saga transaction includes three sub transactions:

  • Transfer out 50 from MySQL
  • Into Mongo 30
  • Transfer in 20 to redis

In the whole transaction process, if all sub transactions are successfully completed, the global transaction is successful; If a sub transaction returns a business failure, the global transaction is rolled back.


If you want to run the above example completely, the steps are as follows:

  1. Run DTM
git clone https://github.com/dtm-labs/dtm && cd dtm
go run main.go
  1. Examples of successful operation
git clone https://github.com/dtm-labs/dtm-examples && cd dtm-examples
go run main.go http_saga_multidb
  1. Examples of running failures
git clone https://github.com/dtm-labs/dtm-examples && cd dtm-examples
go run main.go http_saga_multidb_rollback

You can modify the example to simulate various temporary faults, null compensation and other exceptions. When the whole global transaction is finally completed, the data is consistent.


This paper gives an example of distributed transactions across mysql, redis and Mongo, and explains in detail the problems to be handled and the solutions.

The principle of this article is applicable to all storage engines that support acid transactions. You can quickly expand it to other engines, such as tikv.

Welcome to visithttps://github.com/dtm-labs/dtmAnd star supports us

Recommended Today

How Java reads XML

1.DB.java package com.bn.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Iterator; import java.util.Properties; import org.dom4j.Document; import org.dom4j.DocumentException; import org.dom4j.Element; import org.dom4j.io.SAXReader; public class DB { private static String dbServer; private static String dbName; private static String dbUser; private static String dbPwd; //java Webpage GameProject fhadmin org public void readXML(){ […]