Problems with MySQL nested Firms

Problems with MySQL nested Firms

MySQL supports nested transactions, but not many people will do this .... Some time ago, some foreigners outside China were arguing about the necessity of MySQL nested transactions. Make me laugh. What are the necessity of this nested ghost animal usage. I talked with my former dba colleagues and learned that MySQL nested transactions are not used in any scenario.

So what problems will MySQL nested transactions encounter?

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 displayed is 1 2 3. we thought that although my transaction was nested, I felt that the rollback was rolled back. In fact, we wanted to see that the sub-transaction was successfully executed, 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, the commit... !!!

begin_1  sql_1  begin_2  sql_2  sql_3 commit_1  rollback_1  .

When begin_2 is executed, SQL _1 is submitted. When you run commit_1 again, SQL _2 and SQL _3 are submitted. at this time, you will go to rollback again, so it will never be used .... Because you have submitted all of them before, why can you roll back...

As mentioned above, transactions are usually nested in the architecture, but sometimes they are accidentally nested. Let's take the python project as an example. First, we use the decorator to encapsulate transactions, and then the data processing functions def a () and def B () are encapsulated by transactions, simply using a and B does not matter. It is a single transaction. If a calls B again in logic, what will happen? Yes, the transaction is nested... I think this is a problem that most business development will encounter.

So how can we avoid this risk? You can lock it .... Sets up a global lock. Before a sub-transaction is created, it determines the lock status ....

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

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

For Asynchronous io architectures such as tornado and gevent, you can use fd to associate coroutine variables.

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

When we execute the following statement, the transaction will be forcibly committed. Of course, the premise here is that 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