Use connection pool (?)

[micheljung]

DB connection handling is still foobar.

Main problem is that the policy server somehow locks the database for other applications, probably because the connection isn't closed? No idea.

[micheljung]

Like the python server when trying to link to steam:

faf-python-api                   | [2018-02-12 13:00:39,860] ERROR in app: Exception on /users/validate_steam/Z0FBQUFBQmFnWThBalFuU3doQWJ0LXdEc3lEU3dDTTZoX0pCdzA4YU1SR2h4R0x3ZUFKcUFBVFJXdTk2Q1hDeEhNYnVSZ2hxbjVhTDNreVlrY2EwT1I0bHpXS3RzMk5DZm5mU0hMNWhfVkZNNk5TRGhTV3MzQ0RhYXRueGFnMmlzdkZUY3E1blNSWml2eUdqWVJrR2xGVThFckRnQTYyU0VGcEU3MzNxZDZ3T1FNWWNvNFZRRDdrPQ%3D%3D [GET]
faf-python-api                   | Traceback (most recent call last):
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1988, in wsgi_app
faf-python-api                   |     response = self.full_dispatch_request()
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1641, in full_dispatch_request
faf-python-api                   |     rv = self.handle_user_exception(e)
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/flask_cors/extension.py", line 161, in wrapped_function
faf-python-api                   |     return cors_after_request(app.make_response(f(*args, **kwargs)))
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1544, in handle_user_exception
faf-python-api                   |     reraise(exc_type, exc_value, tb)
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
faf-python-api                   |     raise value
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1639, in full_dispatch_request
faf-python-api                   |     rv = self.dispatch_request()
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/flask/app.py", line 1625, in dispatch_request
faf-python-api                   |     return self.view_functions[rule.endpoint](**req.view_args)
faf-python-api                   |   File "/code/api/users_route.py", line 468, in validate_steam_request
faf-python-api                   |     'id': user_id
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 167, in execute
faf-python-api                   |     result = self._query(query)
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 323, in _query
faf-python-api                   |     conn.query(q)
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 836, in query
faf-python-api                   |     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1020, in _read_query_result
faf-python-api                   |     result.read()
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1303, in read
faf-python-api                   |     first_packet = self.connection._read_packet()
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 982, in _read_packet
faf-python-api                   |     packet.check_error()
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 394, in check_error
faf-python-api                   |     err.raise_mysql_exception(self._data)
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 120, in raise_mysql_exception
faf-python-api                   |     _check_mysql_exception(errinfo)
faf-python-api                   |   File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 115, in _check_mysql_exception
faf-python-api                   |     raise InternalError(errno, errorvalue)
faf-python-api                   | pymysql.err.InternalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

[micheljung]

Maybe just needs a commit, or autocommit enabled.

Brutus digged up this: http://pymysql.readthedocs.io/en/latest/modules/connections.html

[duk3luk3]

This is a lock contention problem, not a connection contention problem.

I think we are currently locking the whole login table for every update, because we are using the default REPEATABLE READ Isolation level.

We should probably change that.

Here is the documentation: https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html