抓包分析mysql的事务提交过程

前言:

       最近比较好奇,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的耗时了。


大家觉得文章对你有些作用! 如果想赏钱,可以用微信扫描下面的二维码,感谢!
另外再次标注博客原地址  xiaorui.cc