python - Flask 错误处理程序，回滚 mysql 异常

Question

我有一个 Flask 应用程序，并且有一个端点/资源使用 FlaskSqlAlchemy 作为 orm 在 candidate_device 表中插入一条记录。我遇到一个问题，当我在 jenkins 上运行我的测试时，jenkins 在 48 个并行线程/进程 pytest -n 48 some_service/tests 中运行这些测试。大约有 13 个测试调用上述资源并在 candidate_device 表中创建新记录。不是所有时候，但有时我的测试会因为这个错误而失败

{"asciTime":"2016-07-19 07:59:43,093", "fileName":"app.py", "logRecordCreationTime":"1468915183.093476", "functionName":"log_exception", "levelNo":"40", "lineNo":"1423", "time":"93", "levelName":"ERROR", "message":"Exception on /v1/candidates/1641/devices [POST]" }#012Traceback (most recent call last):#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/flask_restful/__init__.py", line 268, in error_router#012 return self.handle_error(e)#012 File "/root/base_service_container/candidate_service/common/talent_api.py", line 33, in handle_error#012 app_name, url, user_id, user_email = get_request_info(current_app)#012 File "/root/base_service_container/candidate_service/common/error_handling.py", line 175, in get_request_info#012 user_id = request.user.id#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 237, in __get__#012 return self.impl.get(instance_state(instance), dict_)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 573, in get#012 value = state._load_expired(state, passive)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 480, in _load_expired#012 self.manager.deferred_scalar_loader(self, toload)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 664, in load_scalar_attributes#012 only_load_props=attribute_names)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 219, in load_on_ident#012 return q.one()#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2473, in one#012 ret = list(self)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2516, in __iter__#012 return self._execute_and_instances(context)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2529, in _execute_and_instances#012 close_with_result=True)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2520, in _connection_from_session#012 **kw)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 882, in connection#012 execution_options=execution_options)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 887, in _connection_for_bind#012 engine, execution_options)#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 313, in _connection_for_bind#012 self._assert_active()#012 File "/root/.pyenv/versions/2.7.9/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 214, in _assert_active#012 % self._rollback_exception#012InvalidRequestError: 
This Session's transaction has been rolled back due to a previous exception during 
flush. To begin a new transaction with this Session, first issue Session.rollback().
Original exception was: (_mysql_exceptions.OperationalError) (1205, 'Lock wait timeout exceeded; try restarting transaction') 
[SQL: u'INSERT INTO candidate_device (one_signal_device_id, candidate_id, registered_at_datetime) VALUES (%s, %s, %s)'] [parameters: ('b702183d-4541-4ec0-a977-82d2a18ff681', 1641L, datetime.datetime(2016, 7, 19, 7, 58, 52, 49878))]

Mian错误是这样的

Original exception was: (_mysql_exceptions.OperationalError) (1205, 'Lock wait timeout exceeded; try restarting transaction')

所以我想，为什么不添加一个错误处理程序来捕获此类异常并回滚，这样其他代码就不会因先前的异常而失败。

我的想法可能是完全错误的。所以我只是想问问你们这些聪明的人有没有办法处理这些问题，因为我找不到使用一些 SqlAlchemy 配置等来修复这个错误的解决方案。

非常感谢您的帮助。提前致谢

Answer 1

您的问题一定与连接限制有关。根据查询执行的时间长短，您可以超过此数字(相同的查询并不总是使用相同的时间，这就是为什么有时会失败有时不会失败的原因)。

首先执行这个sql查询:

show variables like "max_connections";

这个数字是你的 mysql 服务器允许的并发连接数。更多连接需要更多内存，因此请明智地选择它。

如何增加 mysql 连接限制？简单，只需转到您的配置文件(通常位于/etc/my.cnf，但这取决于您的安装)和属性

max_connections = 200

请注意，虽然您只启动了 48 个进程，但使用的连接数会更多。 SqlAlchemy 使用连接池，因此每个进程都会消耗多个连接。但同样，sqlalchemy 的确切行为取决于您的配置。