Welcome to the MySQL technical articles shared by the great SQL community. If you have any questions or want to learn, you can leave a message in the comment area below. After seeing it, you will answer it

problem

Run the following Python code, but you can't always 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()
The table can be deleted normally by running the following code that looks the same:

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()

answer

In fact, the problem is not complicated for several reasons:

• 1. The table to be written is the InnoDB engine, which supports transactions, that is, after writing, the transaction to be committed is the real completion of writing.
• 2. When connecting to the database, you need to set the automatic transaction submission mode, whether to turn it on or off.
• 3. In the pymysql module, auto submit mode is not enabled by default.
  Therefore, when DML operations are performed on tables, transactions need to be committed before they can succeed.
• 4. Deleting a table is a DDL operation. At present, DDL operation does not support transactions, so it can succeed even if automatic submission is not enabled.

It's easy to know the above reasons. Let's first look at the settings for automatic submission in the pymysql source code:

[root@yejr-mgr1 pymysql]# cat /usr/lib/python2.7/site-packages/pymysql/connections.py
...
#Around 158 lines
 158     :param autocommit: Autocommit mode. None means use server default. (default: False)
...

Therefore, there are several solutions:

1. Enable automatic submission mode during connection initialization, for example:

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

1. Or after the DML operation, execute the commit request again, for example:

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

1. Or modify the autocommit mode after creating the connection, 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")

Here, the problem of automatic submission is solved.

But going further, what are the pros and cons of turning autocommit on or off? In short, there are several suggestions:

• 1. When there is a large number of data updates, you can close autocommit first, and then submit manually after the transaction is completed. The cost of refreshing redo log and binlog during transaction commit is relatively high.
• 2. The disadvantage of turning off autocommit is that when you forget to actively commit a transaction, the corresponding row lock may not be released, and other transactions will be blocked for a long time. If it is an online production environment, it may cause serious consequences (the business is unavailable for a long time).
• 3. Therefore, the autocommit mode needs to be dynamically adjusted according to the actual situation, and there is no general setting.
• 4. Many development frameworks will set set autocommit=0 by default. What's more, each time before executing an SQL, you have to send a set request, which increases unnecessary overhead. If this happens, you can adjust the code of the development framework yourself.

Enjoy MySQL :)

Article recommendation:

Technology sharing | MGR best practice
https://mp.weixin.qq.com/s/66...

Technology sharing | the way to repair MGR Bug in Wanli database
https://mp.weixin.qq.com/s/Ia...

Macos system compiler percona and the difference of some functions on Macos system
https://mp.weixin.qq.com/s/jA...

Technology sharing | using systemd to manage single machine and multiple instances of MySQL
https://mp.weixin.qq.com/s/iJ...

Product | GreatSQL to create a better MGR ecology
https://mp.weixin.qq.com/s/By...

Product | GreatSQL MGR optimization reference
https://mp.weixin.qq.com/s/5m...

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.

Gitee:
https://gitee.com/GreatSQL/Gr...

GitHub:
https://github.com/GreatSQL/G...

Wechat & QQ group:

You can scan code to add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR exchange wechat group, or directly scan code to join GreatSQL/MGR exchange QQ group.

This article is composed of blog one article multi posting platform OpenWrite release!