why sometimes select return empty, but mysql has data...

[fatpo]

env:

python 2.7
tormysql==0.2.9

connections:

pool = tormysql.ConnectionPool(
    max_connections=20,  # max open connections
    idle_seconds=7200,  # conntion idle timeout time, 0 is not timeout
    wait_connection_timeout=3,  # wait connection timeout
    host=settings.db_host,
    user=settings.db_user,
    passwd=settings.db_pass,
    db=settings.db_name,
    charset="utf8",
    cursorclass=pymysql.cursors.DictCursor,
)

in a handler, I insert into smscode('18520330873', '1234') and commit;

with (yield pool.Connection()) as conn:
    dic = {'mobile': mobile, 'code': code,  'type': u'阿里大鱼'}
    sms_info = SMSCodeInfo(dic)
    yield SMSCodeService.insert_sms(self.context, conn, sms_info)
    yield conn.commit()

in another handler, I select smscde where mobile='18520330873' and code='1234', it return None.

 with (yield pool.Connection()) as conn:
     # 判断手机验证码正不正确
    mobile = self.req_info.req.mobile
    code = self.req_info.req.code
    password = self.req_info.req.password

    ret_dic = yield SMSCodeService.check_phone_code(self.context, conn, mobile, code)
    if ret_dic['code'] != settings.RETURN_OK:
        raise tornado.gen.Return(False)

please help me...5555... is this something about mysql's REPEATABLE-READ?

[snower]

Tormysql use pymysql implementation, pymysql the autocommit acquiescence is FALSE, so even if the select query also need to manually commit, or you may not be able to query the new data, all the api you can see pymysql document search.

[fatpo]

so my code should be?

 with (yield pool.Connection()) as conn:
    yield conn.commit()

    # 判断手机验证码正不正确
    mobile = self.req_info.req.mobile
    code = self.req_info.req.code
    password = self.req_info.req.password

    ret_dic = yield SMSCodeService.check_phone_code(self.context, conn, mobile, code)
    if ret_dic['code'] != settings.RETURN_OK:
        raise tornado.gen.Return(False)

I confuse about conn.commit(), in my opinion, only when create or insert, you should use conn.commit()...

[snower]

mysql的autocommit并没有说只在insert或者是update生效，你可以去看下mysql官网的文档介绍，当前连接查询不commit是不能查询到新的数据的，你也可以测试一下看看

[snower]

https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html 这里边介绍都很详细了，可以看看

[fatpo]

竟然是中国人。。。 我查了下，数据看 select @@autocommit 是1，说明开启了自动commit。 你推荐的文档，果然是和REPEATABLE-READ有关系。 thx。

[snower]

除了数据库全局配置autocommit之外，pymysql连接数据库的时候也会对当前连接重新设置这个值，默认就会被设置为0

[fatpo]

pymysql这把autocommit设置为0，所有的db操作都是事务了。那么确实每一次conn之前要先commit一下，获取最新数据。thx。