Problems encountered by MySQL nested transactions

Problems encountered by MySQL nested transactions

MySQL supports nested transactions, but not many people do it... Some time ago, I saw some foreigners arguing about the necessity of MySQL nested transactions. It's so funny, is there any scenario where this nested weird usage is necessary? I talked to my former DBA colleague and learned that MySQL nested transactions should not be used in any scenario.

So what problems will you encounter when using MySQL nested transactions?

mysql> select * from ceshi; 
+------+ 
| n | 
+------+ 
| 1 | 
+------+ 
1 row in set (0.00 sec) 
 
mysql> start transaction; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into ceshi values(2); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> start transaction; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into ceshi values(3); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> commit; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> rollback; 
Query OK, 0 rows affected (0.00 sec) 


Although I rolled back at the end, the data showed 1 2 3. Originally, everyone thought that although my transaction was in a nested state, it rolled back at the end. In fact, the result we hope to see is that the sub-transaction is executed successfully, and the failure of the outer transaction will be rolled back. But this is not the case, the final result is 1 2 3 .

+-----+ 
| n | 
+-----+ 
| 1 | 
| 2 | 
| 3 | 
+-----+ 

When the SQL interpreter encounters start transaction, commit will be triggered... !!!

begin_1 sql_1 begin_2 sql_2 sql_3 commit_1 rollback_1 .

When begin_2 is executed, sql_1 has already been committed. When you execute commit_1, sql_2 and sql_3 have already been committed. At this time, if you rollback, it will be useless... because they have already been committed, what can you roll back...

As mentioned before, in architecture, few people use nested transactions, but sometimes they are nested accidentally. Let’s take the Python project as an example. First, we use decorators to implement transaction packaging. Then, the data processing def a() and def b() functions are wrapped in transactions. It doesn’t matter if we simply use a or b, as they are all single transactions. If a calls b in the logic, what will happen? Yes, transactions are nested... I think this is a problem that most business developers will encounter.

So how to avoid this risk? You can lock it... Set up a global lock and check the lock status before creating a subtransaction...

If you are using the flask framework, you can use the flask g global variable.

If it is the Django framework, you can use thread local to use global variables.

If it is an asynchronous io architecture such as tornado or gevent, you can use fd to associate the coroutine variables.

@decorator
def with_transaction(f, *args, **kwargs):
 
  db = connection.get_db_by_table("*")
  try:
    db.begin()
    ret = f(*args, **kwargs)
    db.commit()
  except:
    db.rollback()
    raise
  return ret
 
 
@with_transaction
def hide(self):
  '''Orders are not displayed on the app'''
  if self.status not in OrderStatus.allow_deletion_statuses():
    raise OrderStatusChangeNotAllowed(self.status, OrderStatus.deleted)
...
 
 
@with_transaction
def change_receipt_info(self, address, name, phone):
  region = Region.get_by_address(address)
  ...

When we execute the following statement, the transaction will be forced to commit. Of course, the premise here is autocommit = True.

ALTER FUNCTION  
ALTER PROCEDURE  
ALTER TABLE  
BEGIN  
CREATE DATABASE  
CREATE FUNCTION  
CREATE INDEX  
CREATE PROCEDURE  
CREATE TABLE  
DROP DATABASE  
DROP FUNCTION  
DROP INDEX  
DROP PROCEDURE  
DROP TABLE  
UNLOCK TABLES  
LOAD MASTER DATA  
LOCK TABLES  
RENAME TABLE  
TRUNCATE TABLE  
SET AUTOCOMMIT=1  
START TRANSACTION  

You may also be interested in:
  • Two solutions for implementing MySQL nested transactions in PHP
  • MySQL stored procedure example (including transactions, output parameters, nested calls)

<<:  Let's talk about the v-on parameter problem in Vue

>>:  How to implement hot deployment and hot start in Eclipse/tomcat

Recommend

The process of building lamp architecture through docker container

Table of contents 1. Pull the centos image 2. Bui...

What does href=# mean in a link?

Links to the current page. ------------------- Com...

CentOS 7 set grub password and single user login example code

There are significant differences between centos7...

JavaScript implements double-ended queue

This article example shares the specific code of ...

Solution to multiple 302 responses in nginx proxy (nginx Follow 302)

Proxying multiple 302s with proxy_intercept_error...

A brief analysis of MySQL's WriteSet parallel replication

【Historical Background】 I have been working as a ...

Introduction to query commands for MySQL stored procedures

As shown below: select name from mysql.proc where...

A time-consuming troubleshooting process record of a docker error

Table of contents origin Environmental Informatio...

The neglected special effects of META tags (page transition effects)

Using js in web design can achieve many page effec...

Four ways to create objects in JS

Table of contents 1. Create objects by literal va...

Solution to 1067 when Mysql starts in Windows

I just started working a few days ago and install...

Docker data volume container creation and usage analysis

A data volume container is a container specifical...