万答#2,一样的Python代码,为什么可以删表,却不能更新数据

229 阅读3分钟

欢迎来到 GreatSQL社区分享的MySQL技术文章,如有疑问或想学习的内容,可以在下方评论区留言,看到后会进行解答

问题

运行下面的这段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模块里,默认不启用自动提交模式。 所以对表进行DML操作时,需要提交事务后才能成功。

  • 4.而删除表是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)
  1. 或者执行完DML操作后,再执行一次commit请求,例如:
sql = "update t1 ...
cur.execute(sql)
cur.execute("commit")
  1. 又或者在创建完连接后,修改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 :)

文章推荐:

技术分享 | MGR最佳实践(MGR Best Practice) mp.weixin.qq.com/s/66u5K7a9u…

技术分享 | 万里数据库MGR Bug修复之路 mp.weixin.qq.com/s/IavpeP93h…

Macos系统编译percona及部分函数在Macos系统上运算差异 mp.weixin.qq.com/s/jAbwicbRc…

技术分享 | 利用systemd管理MySQL单机多实例 mp.weixin.qq.com/s/iJjXwd0z1…

产品 | GreatSQL,打造更好的MGR生态 mp.weixin.qq.com/s/ByAjPOwHI…

产品 | GreatSQL MGR优化参考 mp.weixin.qq.com/s/5mL_ERRIj…

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。

Gitee: gitee.com/GreatSQL/Gr…

GitHub: github.com/GreatSQL/Gr…

微信&QQ群:

可扫码添加GreatSQL社区助手微信好友,发送验证信息“加群”加入GreatSQL/MGR交流微信群,亦可直接扫码加入GreatSQL/MGR交流QQ群。

file

本文由博客一文多发平台 OpenWrite 发布!