Article catalogue

• preface
  • 1. Services
  • 2. Distributed transactions
• 1, Single database transaction
  • 1. Usage of decorator
  • 2.with statement usage:
  • 3. Transaction specific syntax
• 2, Multi database transaction
  • 1. Multi data source single database transaction
  • 2. Multi database transactions

preface

1. Services

In the computer system, transactions are mostly controlled through relational database, which is realized by using the transaction characteristics of the database itself, so it is called database transaction. Because the application mainly depends on relational database to control transactions, and the database is usually on the same server as the application, the transaction based on relational database is also called local transaction.

Four characteristics of database transaction ACID:

A (Atomic): atomicity. All operations constituting a transaction are either completed or not executed. It is impossible to have partial success and partial failure.

C (Consistency): Consistency. The Consistency constraint of the database is not destroyed before and after the transaction is executed. For example, if Zhang San transfers 100 yuan to Li Si, the data before and after the transfer is correct, which is called Consistency. If Zhang San transfers 100 yuan out and Li Si's account does not increase 100 yuan, there is a data error and Consistency is not achieved.

I (isolation): isolation. Transactions in the database are generally concurrent. Isolation means that the execution of two concurrent transactions does not interfere with each other. One transaction cannot see the intermediate state of the running process of other transactions. By configuring the transaction isolation level, problems such as dirty reads and duplicate reads can be avoided.

D (Durability): persistence. After the transaction is completed, the data changes made by the transaction will be persisted to the database and will not be rolled back.

When implementing a database transaction, all operations involved in a transaction will be incorporated into an inseparable execution unit. All operations in the execution unit will either succeed or fail. As long as any operation fails, the whole transaction will be rolled back

2. Distributed transactions

Distributed system will split an application system into multiple services that can be deployed independently, so it needs remote cooperation between services to complete the transaction operation. In this distributed system environment, the transaction completed by remote cooperation between different services through the network is called distributed transaction.

1, Single database transaction

In Django, you can use Django db. The transaction module defines a transaction based on the atomic provided by the

1. Usage of decorator

from django.db import transaction

@transaction.atomic
def viewfunc(request):
  # This code is executed in a transaction
  pass

Decorator usage: all MySQL database operations in the whole view are regarded as a transaction, and the scope is too large and not flexible enough. And it cannot act directly on the class view

2.with statement usage:

from django.db import transaction

def viewfunc(request):
  # This part of the code is not in the transaction and will be automatically submitted by Django
  pass

  with transaction.atomic():
      # This part of the code will be executed in the transaction
      pass

with statement usage: you can flexibly and selectively treat some MySQL database operations as a transaction. And don't care about the type of view.

3. Transaction specific syntax

from django.db import transaction

# Create savepoint
save_id = transaction.savepoint()

# Rollback to savepoint
transaction.savepoint_rollback(save_id)

# Commit all database transaction operations from the savepoint to the current state
transaction.savepoint_commit(save_id)

2, Multi database transaction

1. Multi data source single database transaction

# Database configuration
DATABASES = {
    "default": {
        "ENGINE": "mainsys.mysqlpool",
        "NAME": "el_product",
        "USER": "select_user",
        "PASSWORD": "select_user@321",
        "HOST": "10.32",
    },
    "bt_investment": {
        "ENGINE": "mainsys.mysqlpool",
        "NAME": "",
        "USER": "aaa",
        "PASSWORD": "aaa",
        "HOST": "10.32",
        "ATOMIC_REQUESTS":True
    },

    "industrychain": {
        "ENGINE": "mainsys.mysqlpool",
        "NAME": "industrychain",
        "USER": "select_user",
        "PASSWORD": "select_user@321",
        "HOST": "10.32",
    },
}

with transaction.atomic(using="bt_investment"):  # Specify the data source, which can be used anywhere in python code
      for obj in execel_data_formart:

2. Multi database transactions

@transaction.commit_manually(using='other_db')
@transaction.commit_manually
def test(request):    
    t3 = model.Test3()
    t1 = model.Test1()
    try:
        sid = transaction.savepoint(using='other_db')
        t3.save(using='other_db')        
        sid1 = transaction.savepoint()
        t1.save()
        transaction.savepoint_commit(sid,using='other_db')        
        transaction.commit(using='other_db')
        transaction.savepoint_commit(sid1)
        transaction.commit()
        return rest.response("success")
    except:
        transaction.savepoint_rollback(sid,using='other_db')
        transaction.savepoint_rollback(sid1)
        transaction.rollback(using='other_db')  
        transaction.rollback()
        return rest.error_response(400, "fail")