Detailed code examples of problems encountered in MySQL nested transactions

This article mainly introduces relevant information about the problems encountered by MySQL nested transactions. Friends who need it can refer to it

MySQL supports nested transactions, but not many people will do this. …. Some time ago, I saw some foreigners arguing about the necessity of MySQL nested transactions abroad. It amuses me to death. Why is this nested ghost usage necessary for the scene? I talked to my former DBA colleagues 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 display is 1 2 3 . Originally, everyone thought that although my transaction was in a nested state, it felt like it was rolled back in the end. In fact, what we want 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 submitted. When you execute commit_1 again, Then sql_2 and sql_3 have been submitted. If you go to rollback at this time, it will be of no use... Because they have been submitted before, what can you roll back...

As mentioned before, there are generally very few architectures. 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 by transactions. It doesn't matter if you simply use a and b. Single transaction. If a logic calls b again, what will happen? Yes, transactions are nested... I think this is a problem that most business development will encounter.

So how to avoid this risk? You can add a lock... Set up a global lock, and the status of the lock will be judged before the sub-transaction is created...

If you are a flask framework, you can use flask g global variables.

If it is a django framework, you can use thread local to use global variables.

If it is an asynchronous IO architecture such as tornado and gevent, you can use fd to associate 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):
  '''订单不在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

The above is a detailed explanation of the code examples of problems encountered by MySQL nested transactions. For more related content, please pay attention to the PHP Chinese website (www.php .cn)!