Welcome to GreatSQL Community shared MySQL Technical articles , If you have questions or want to learn , You can leave a message in the comment area below , When you see it, you will answer

problem

Run the following paragraph Python Code , But always unable to update the data ：

import pymysql
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4')
cur = conn.cursor()
sql = "update t1 set c3 = rand()*10240 where c1 = rand()*1024"
cur.execute(sql)
cur.close()
conn.close()
 And run the following code that looks the same , But you can delete the table normally ：

import pymysql
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4')
cur = conn.cursor()
sql = "drop table tmp1"
cur.execute(sql)
cur.close()
conn.close()
 Copy code 

answer

In fact, the problem is not complicated , There are several reasons ：

• 1. The table to be written is InnoDB engine , and InnoDB The engine supports transactions , That is, after writing , To commit a transaction is to actually complete the write .

• 2. When connecting to the database , You need to set the transaction auto commit mode by yourself , Is it on or off .

• 3.pymysql In the module , Auto submit mode is not enabled by default .

So the table is DML In operation , The transaction needs to be committed before it can succeed .

• 4. Deleting a table is DDL operation , at present DDL The operation does not yet support transactions , So even if Auto submit is not turned on , It can also be successful .

It's easy to know the above reasons . Let's take a look first pymysql The setting of automatic submission in the source code ：

[[email protected] pymysql]# cat /usr/lib/python2.7/site-packages/pymysql/connections.py
...
# about 158 Near the line 
 158     :param autocommit: Autocommit mode. None means use server default. (default: False)
...
 Copy code 

therefore , There are several solutions ：

1. Turn on auto submit mode during connection initialization , for example ：

# Set properties autocommit=1 Yes 
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4', autocommit=True)
 Copy code 

2. Or after the execution DML After the operation , Execute it again commit request , for example ：

sql = "update t1 ...
cur.execute(sql)
cur.execute("commit")
 Copy code 

3. Or after creating the connection , modify autocommit Pattern , for example ：

conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4')
cur = conn.cursor()
cur.execute("set autocommit=1")
 Copy code 

Come here , The problem of automatic submission has been solved .

But we need to go further , On or off autocommit What are the pros and cons ？ in short , Here are some suggestions ：

• 1. When there is a large number of data updates , You can turn it off first autocommit, When the business is over , And then manually submit . Business commit Refresh when redo log、binlog etc. , The price is still relatively high .

• 2. close autocommit The disadvantage of is , When you forget to actively commit a transaction , It may cause the corresponding row lock to be held all the time and not released , Other transactions will be blocked for a long time , If it's an online production environment , May cause serious consequences （ Business unavailable for a long time ）.

• 3. therefore , It needs to be dynamically adjusted according to the actual situation autocommit The pattern of , There are no general settings .

• 4. Many development frameworks have default settings set autocommit=0, What is more , One at a time SQL front , Send it once set request , Increased unnecessary expenses , If there is such a situation , You can adjust the code of the development framework .

Enjoy MySQL :)

Article recommendation ：

Technology sharing | MGR Best practices (MGR Best Practice) mp.weixin.qq.com/s/66u5K7a9u…

Technology sharing | Wanli database MGR Bug The road to repair mp.weixin.qq.com/s/IavpeP93h…

Macos System compilation percona And some functions in Macos Operational differences on the system mp.weixin.qq.com/s/jAbwicbRc…

Technology sharing | utilize systemd management MySQL Single machine multi instance mp.weixin.qq.com/s/iJjXwd0z1…

product | GreatSQL, Create better MGR ecology mp.weixin.qq.com/s/ByAjPOwHI…

product | GreatSQL MGR Optimization reference mp.weixin.qq.com/s/5mL_ERRIj…

About GreatSQL

GreatSQL It is maintained by Wanli database MySQL Branch , Focus on Improvement MGR Reliability and performance , Support InnoDB Parallel query feature , It is suitable for financial grade applications MySQL Branch version .

Gitee: gitee.com/GreatSQL/Gr…

GitHub: github.com/GreatSQL/Gr…

WeChat &QQ Group ：

Scan code to add GreatSQL Community assistant wechat friend , Send verification information “ Add group ” Join in GreatSQL/MGR Exchange wechat group , You can also directly scan the code to add GreatSQL/MGR communication QQ Group .

This article by the blog one article many sends the platform OpenWrite Release ！