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)
+-----+ | n | +-----+ | 1 | | 2 | | 3 | +-----+ When the SQL interpreter encounters start transaction, commit will be triggered... !!! 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:
|
<<: Let's talk about the v-on parameter problem in Vue
>>: How to implement hot deployment and hot start in Eclipse/tomcat
When inserting a set of data into the MySQL datab...
<br />Hello everyone! It’s my honor to chat ...
Method 1: Use Google advanced search, for example...
<br />Conditional comments are a feature uni...
1. Introduction When a web project is published o...
As one of the most commonly used and important ut...
This example requires downloading and installing ...
Introduction to influxDB influxDB is a distribute...
The CSS3 category menu effects are as follows: HT...
<br />Original text: http://blog.rexsong.com...
Drag and drop is a common function in the front e...
Table of contents 1. Background: 2. Design ideas:...
The offline installation method of MySQL_8.0.2 is...
What is an inode? To understand inode, we must st...
Table of contents Vite project build optimization...