前言:
最近比较好奇，mysql在执行事务的时候，begin会跟第一条sql一起批量发送，还是分别发送到mysql ? 我这里先把验证的结果说下，一组sql事务里的 begin; sql; commit 会被发送三次IO，另外mysql本身是不支持这类批量发送。
该文章后续仍在不断的更新修改中， 请移步到原文地址 http://xiaorui.cc/?p=5394
为什么要关心这话题？
因为毕竟多了一次网络io操作，在一个低延迟要求较高的系统里，每个io都需要考虑优化。我们在开发中很大一个原则就是 能不用事务的就别用事务。
事务抓包测试
首先在mysql-cli端执行一个简单的事务流程。
# xiaorui.cc mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from main_tasks where id = 531211966 for update; Empty set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
在mysql端进行tcpdump抓包:
# xiaorui.cc ngrep -d eth0 -W single -l port 3306|grep -E -i -w "select|begin|commit"
抓包结果:
T 172.31.12.205:49852 -> 118.190.xxxx:3306 [AP] .....begin T 172.31.12.205:49852 -> 118.190.xxxx:3306 [AP] 9....select * from main_tasks where id = 531211966 for update T 172.31.12.205:49852 -> 118.190.xxx:3306 [AP] .....commit
我们发现 begin; sql; commit 一共发起了三个网络io交互。 上面的操作是把begin和sql这两个语句分别发送的，那么如果在mysql-cli直接 批量发送begin和sql一起执行，会出现什么？
mysql> begin;select * from main_tasks where id = 531211966 for update;commit;
这组事务操作还是被分成三组io交互。 mysql-cli会把命令进行分号;分割，然后循环发送。
# T 172.31.12.205:49852 -> 118.x:3306 [AP] .....begin # T 118.19xxxxx:3306 -> 172.31.12.205:49852 [AP] ........... ## T 172.31.12.205:49852 -> 118.19xxxx:3306 [AP] 9....select * from main_tasks where id = 531211966 for update # T 118.19xxxxx:3306 -> 172.31.12.205:49852 [AP] # T 172.31.12.205:49852 -> 118.19xxxx:3306 [AP] .....commit # T 118.19xxxx:3306 -> 172.31.12.205:49852 [AP] ...........
我们再换一个python的mysql库包试试，是否可以把事务的begin和sql一起发送。
# xiaorui.cc
>>> import pymysql
>>> db = pymysql.connect(“xxxx","refresh_user”,"xxx","refresh_master" )
>>> cursor = db.cursor()
>>> cursor.execute("begin;select * from main_tasks where id = 531211966 for update;commit;")
Traceback (most recent call last):
File "<input>", line 1, in <module>
cursor.execute("begin;select * from main_tasks where id = 531211966 for update;commit;")
File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 516, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
result.read()
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1066, in read
first_packet = self.connection._read_packet()
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet
packet.check_error()
File "/usr/local/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "/usr/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for t
he right syntax to use near 'select * from main_tasks where id = 531211966 for update;commit' at line 1")
通过tcpdump抓包也是可以看到异常信息，说明 mysql压根就不支持这类的批量发送。
T 172.31.12.205:57098 -> 118.190xxxx:3306 [AP] G....begin;select * from main_tasks where id = 531211966 for update;commit; # T 118.190xxxx:3306 -> 172.31.12.205:57098 [AP] .....(.#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from main_tasks where id = 531211966 for update;commit' at line 1 ##
总结:
使用事务也要考虑下io rtt的耗时了。
