《叶问》32期，一样的Python代码，为什么可以删表，却不能更新数据

电玩女神 2023-01-21 02:16 24阅读 0赞

## 问题 ##

运行下面的这段Python代码，却总是无法更新数据：

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

而运行下面的这段看起来一样的代码，却可以正常删表：

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

## 回答 ##

其实问题并不复杂，有几个原因：

1.  要写入的表是InnoDB引擎，而InnoDB引擎是支持事务的，也就是写入后，**要提交事务才是真正完成写入**。
2.  连接数据库时，需要自行设定事务自动提交模式，是开启还是关闭。
3.  pymysql模块里，**默认不启用自动提交模式**。
4.  所以对表进行DML操作时，需要提交事务后才能成功。
5.  而删除表是DDL操作，目前DDL操作还不支持事务，所以即便没有开启自动提交，也能成功。

知道上面的原因就好办了。我们先看下pymysql源码中关于自动提交的设定：

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

所以，解决方法有好几种：

1. **在连接初始化时开启自动提交模式**，例如：

#设置属性autocommit=1亦可
    conn=pymysql.connect(
    host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
    port= 3306, db='test', charset='utf8mb4', autocommit=True)

2. 或者执行完DML操作后，**再执行一次commit请求**，例如：

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

3. 又或者在创建完连接后，**修改autocommit模式**，例如：

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

到这里，自动提交的问题解决了。

但还要更进一步，开启或关闭autocommit有有什么利弊呢？简言之，有几点建议：

1.  **当有大批量数据更新时，可以先关闭autocommit**，等事务结束后，再手动提交。事务commit时要刷新redo log、binlog等，代价还是比较大的。
2.  关闭autocommit的缺点在于，当忘记主动提交事务时，可能会造成相应的行锁一直持有不释放，**其他事务会被长时间阻塞**，如果是线上生产环境，则可能造成严重后果（业务长时间不可用）。
3.  因此，需要根据实际情况动态调整autocommit的模式，并没有通用的设置。
4.  不少开发框架都会默认设置 `set autocommit=0`，更有甚者，每次执行一个SQL前，都要发送一次set请求，增加了无谓的开销，如果有这种情况，可以自行调整开发框架的代码。

Enjoy MySQL :)

延伸阅读

*  [《叶问》31期，MySQL中如何查询某个表上的IS（意向共享）锁][31_MySQL_IS]  
    
 *  [全新的MySQL 8.0行锁观测方式][MySQL 8.0]  
    
 *  [会话和锁信息查询视图 | 全方位认识 sys 系统库][_ _ sys]
 *  [InnoDB 层锁、事务、统计信息字典表 | 全方位认识 information\_schema][InnoDB _ _ _ information_schema]
 *  [从一个案例深入剖析InnoDB隐式锁和可见性判断][InnoDB]

--------------------

点击文末“**阅读原文**”直达「叶问」专栏

[31_MySQL_IS]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b428e8a4ccb9884d6b5ea3933c3affadfa0792e7197acfe8e07d908a2a63edfc86cbd4295&idx=1&mid=2653935844&scene=21&sn=7bb2137b126bc83d7a1cf11ccdcbb383#wechat_redirect
[MySQL 8.0]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49688a4cc07e6840dd0a712a02d88592baeac2846e11c0a0fc38887a75071b0bd32a8b14&idx=1&mid=2653934338&scene=21&sn=c76dc57b359b006863f4d6909957caca#wechat_redirect
[_ _ sys]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b4c5e8a4cc54883fa68e0ef53ffbff4e84f549042c2e039f28958a9cfce789e6f997c85cf&idx=8&mid=2653935156&scene=21&sn=ec7548eaea44979859bd151207731ea2#wechat_redirect
[InnoDB _ _ _ information_schema]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b4fd88a4cc6cefae3ce68ae69594dc7f41fe25079229e0bc1617b7e9f1ca1222d461db356&idx=2&mid=2653935026&scene=21&sn=cb4d93272242314c373df6e619e095d3#wechat_redirect
[InnoDB]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b4e988a4cc78eeb0f9901f0e8144201c1bbcf2f4756976ac84159bbb3c61e9b108c76bec6&idx=1&mid=2653934834&scene=21&sn=edcac29d5cdf290281357649c2983caa#wechat_redirect