Django ORM and sqlalchemy clean up residual transactions before each database request

Django will execute before each request

• SET AUTOCOMMIT = 0
• SET AUTOCOMMIT = 1
• SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Then perform the CRUD operations we need

The above three-step operation can be considered as a preliminary operation (but it will not be recorded in Django's ORM log).

You may be wondering, why do you need SET AUTOCOMMIT = 0 and then SET AUTOCOMMIT = 1 ?

Another way to ask this question is: How does Django ensure clean transactions?

In fact, the reason is very simple, it is to clean up the last transaction residue when the connection is reused.

There is a implicit commit concept in Mysql transaction👇

When we use the START TRANSACTION or BEGIN statement to start another transaction before a transaction has been committed or rolled back, the previous transaction will be implicitly committed, such as this:

BEGIN;

SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其它语句

BEGIN; # 此语句会隐式的提交前边语句所属于的事务

Or the value of the current autocommit system variable is OFF. When we manually turn it ON, the transaction to which the previous statement belongs will also be implicitly committed.

Or using LOCK TABLES, UNLOCK TABLES and other locking statements will implicitly commit the transaction to which the previous statement belongs.

Of course, this operation connection reuse, but the default behavior of Django ORM does not require connection reuse, because the default is to create a separate connection for each Request.

2021-11-28T04:47:39.604596Z      307 Connect    root@192.168.31.100 on d_twitter_db using TCP/IP
2021-11-28T04:47:39.608104Z      307 Query    SET AUTOCOMMIT = 0
2021-11-28T04:47:39.612552Z      307 Query    SET AUTOCOMMIT = 1
2021-11-28T04:47:39.617432Z      307 Query    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
2021-11-28T04:47:39.622479Z      307 Query    SELECT `tweets_tweet`.`id`, `tweets_tweet`.`user_id`, `tweets_tweet`.`content`, `tweets_tweet`.`created_at` FROM `tweets_tweet` WHERE `tweets_tweet`.`user_id` = 1
2021-11-28T04:47:39.627836Z      307 Quit

What about sqlalchemy? How to deal with the remnants of the last transaction?

Django ORM is to the SET AUTOCOMMIT = 0 replacement for ROLLBACK