242021.05

技术干货 | 同样的Python代码,为啥能删表却不能更新数据?

2021.05.24

在技术社区中,有DBA小伙伴反馈碰到这样一个难题:几乎同样的Python代码,能正常删表,但却无法更新数据,这到底是为啥呢?具体情形如下:


运行下面这段Python代码,总是无法更新数据:










import pymysqlconn=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 pymysqlconn=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()


相信不止是他一个人遇到这种疑问,很多DBA的小伙伴也有过同样的困惑。今天,小编就为大家答疑解惑。其实问题并不复杂,有几个原因:


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