Problems encountered by MySQL nested transactions

MySQL supports nested transactions, but not many people will do it... Some time ago, I saw some foreigners arguing about the necessity of nested transactions in MySQL. It's killing me. What is the necessity of this nested ghost animal usage? I talked to my former dba colleagues and learned that MySQL nested transactions should not be used in any scenario.

So what are the problems with nested transactions using MySQL?

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 rollback at the end, the data showed that it was 1 2 3. Originally, everyone thought that my transaction was nested, but I felt that rollback rolled back at the end. In fact, what we want to see is that the sub-transaction is successfully executed, and the failure of the outer transaction will roll 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, it triggers commit …! ! !

begin_1  sql_1  begin_2  sql_2  sql_3 commit_1  rollback_1  .

When begin2 is executed, sql_1 is already committed. When you execute commit_1 again, sql_2 and sql_3 are committed. When you go to rollback again, rollback will not be used. Because it has been submitted before, what can you roll back …

As mentioned earlier, in architecture, very few people use transactions nested, but sometimes they are inadvertently nested. Let's take the project of python as an example. First, we use decorators to realize transaction wrapping, and then the data processing functions def a () and def b () are all wrapped by transactions. It doesn't matter if you simply use a and b, they are all single transactions. What happens if b is called in a logic? Yes, transactions are nested … I think this is a problem that most business development will encounter.

So how to avoid this risk? You can lock it …. Set up a global lock, and judge the state of the lock before the sub-transaction is created ….

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

If it is an django framework, you can use global variables using thread and local.

If it is an asynchronous io architecture such as tornado and gevent, fd can be used for associative 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):
  ''' The order is not available app End display '''
  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 committed forcibly. 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