###先了解可能影响开事务的几个proxysql变量 * `mysql-forward_autocommit` When `mysql-forward_autocommit=false` (the default), ProxySQL will track (and remember) the autocommit value that the client wants and change `autocommit` on a backend connection as needed. For example, if a client sends `set autcommit=0`, ProxySQL will just reply OK. When the client sends a DDL, proxysql will get a connection to target hostgroup, and change `autocommit` before running the DDL. If mysql-forward_autocommit=true, `SET autocommit=0` is forwarded to the backend. `SET autocommit=0` doesn't start any transaction, the connection is set in the connection pool, and queries may execute on a different connection. If you set `mysql-forward_autocommit=true`, you should also set `mysql-autocommit_false_not_reusable=true` to prevent the connection to be returned to the connection pool. In other words, setting `mysql-forward_autocommit=false` will prevent this behaviour since the autocommit state is tracked. * `mysql-autocommit_false_is_transaction` If `mysql-autocommit_false_is_transaction=true` (false by default), a **backend** connection with `autocommit=0` is treated as a transaction. If `forward_autocommit=true` (false by default), the same behavior applies. * `mysql-autocommit_false_not_reusable` When set to `true`, a connection with `autocommit=0` is not re-used and is destroyed when the connection is returned to the connection pool. ### 场景测试(在1.4.15版本,也就是2.0以内的版本) * 场景1: `mysql-forward_autocommit`=true `mysql-autocommit_false_is_transaction`=true python中的db.autocommit(False)和类似执行curosr.execute("set autocommit=0")都会转发到后端主库,并开启事务,需要手动commit/rollback,或者curosr.execute("set autocommit=1")来提交事务。`无论后续操作是读,还是写,都会开启事务` `注意点:` python默认连接到mysql中,就会默认将autocommit设置为false,如果想连接进来开启自动提交,db.autocommit(True)是不生效的,需要curosr.execute("set autocommit=1")来才能正常设置为自动提交。 在python的MySQLdb中,如果不开启自动提交,那无论是查询,还是变更都得进行手动结束事务(commit/rollback/set autocommit=1) * 场景2: `mysql-forward_autocommit`=false `mysql-autocommit_false_is_transaction`=true (无论是true还是false) 此时set autocommit=0操作只会被记住,不会转发到后端MySQL。只有变更SQL时,会自动先执行autocommit=0。 1. 如果只是查询,按路由规则将查询SQL转发到后端指定MySQL中,不会带autocommit。 2. 如果是DDL,DML的语句,会自动执行set autocommit=0,再执行DDL,DML的SQL,此事需要手动结束事务。(commit/rollback/set autocommit=1) 3. 如果在连接到proxysql后,执行了python中db.autocommit(True)或者执行了set autocommit=1后,无论是查询、DDL、DML都不会再带上autocommit=0了(也就是不会再转发autocommit=0了),只有相应的SQL执行。 ### 测试代码如下: import MySQLdb ip="10.200.131.4" port=6033 #port=3306 username="sbtest_rw" #username="onlymaster_rw" password="sbtest_rw_123" #password="t6BDA7hdtY7i3*t*" db = MySQLdb.connect(host=str(ip), port=int(port), user=username, passwd=password, db='', connect_timeout=2) db.select_db('information_schema') cursor = db.cursor() #db.autocommit(False) #db.autocommit(1) #db.autocommit(True) #db.autocommit=1 cursor.execute("set autocommit=1 /* test */") #cursor.execute("begin /* test */") #cursor.execute("select @@AUTOCOMMIT /* test */;") #print cursor.fetchone() cursor.execute("select 'test' /* test */;") cursor.execute("update sbtest.sbtest1 set k='xx' where id = 1000 /* test */;") #cursor.execute("commit /* test */"); #cursor.execute("set autocommit=1 /* test */") cursor.close() db.close() ### 场景测试2(在2.0.15版本) `mysql-forward_autocommit`参数已经弃用了,无论是true还是false,都按false生效。 ### 建议使用姿势 * 1、`mysql-forward_autocommit` 将此参数设置为false,那么需要默认将自动提交打开,后续所有事务得显示提交,set autocommit=0这种对proxysql来说,并不是事务,会将读打到从库中去。 特别注意是python中,需要连接进来后,设置成自动提交。 如果没有设置,所有更新都需要进行显示commit/rollback python案例: 连接后设置db.autocommit(True) 后续开事务,通过begin,start transaction显示开启 * 2、`mysql-forward_autocommit` 将此参数设置为true,那么set autocommit为当作一个事务,都需要显示的commit/rollback,或者set autocommit=1. 特别注意python中,需要默认打开自动提交,不能通过db.autocommit(True)方式,得通过execute("set autocommit=1")的方式。否则不生效。 python案例: 连接后,通过curosr.execute("set autocommit=1")打开自动提交 (或者重写autocommit方法,全db.autocommit(True)能正常生效。) 后续开事务可通过set autocommit=0开启,不过提交后记得set autocommit=1打开自动提交,建议是显示开启事务 ### 补充说明: 走proxy后,db.autocommit(True)无法转到后端原因为,没有获取到正确的autocommit导致,没有执行set操作。具体代码逻辑如下: * pymysql中connection.py源代码: ![connect.py.png](http://imgs.itopers.com/connect.py.png) #### 如何解决: 改写autocommit方法后,将判断条件去掉,不管什么情况都进行set操作。 ![autocommit_re.png](http://imgs.itopers.com/autocommit_re.png) * MySQLdb库的源代码: ![image-connect.png](http://imgs.itopers.com/image-connect.png) * get_autocommit方法: ![image-_mysql.c.png](http://imgs.itopers.com/image-_mysql.c.png) * 具体原因为什么获取不到正确的值,如下连接也有描述到: https://blog.csdn.net/gao1738/article/details/42839483 ### 结语: 接入proxysql后,特别是python程序,默认是通过关闭自动提交(set autocommit=0)的方式开启事务,需要特别注意,一不小时本来直连mysql没有问题,通过proxy后就会有问题了---大事务问题。 所以建议开事务都通过显示(begin或者start transaction)方式开,再显示提交或回滚。
文章最后更新时间: 2021年01月29日 17:55:54
分类文章统计
Python常见错误(3)
Python基础(10)
Django(5)
Flask(1)
Linux基础(5)
shell(11)
linux排障(4)
虚拟化(1)
Consul(3)
ProxySQL(7)
SequoiaDB(2)
TiDB(4)
Redis(2)
oracle(10)
MySQL(64)
常用软件(2)
硬件排障(2)
HTML(1)
JavaScript(1)
我们的作品(18)
windows(1)
总结(1)
按年文章统计
2013(43)
2014(19)
2015(25)
2016(6)
2017(30)
2018(7)
2019(17)
2020(4)
2021(4)
2023(1)
2024(2)
老版入口
亲,扫我吧!
友情链接
飞哥的:imbusy.me/
冰川的:www.mindg.cn
海洋的:hiaero.net
宏斌的:techindeep.com
若水的:nosa.me
段郎的:sixther.me
肥客联邦:fk68.net