search

numberscope / backscope

Numberscope's back end: responsible for getting sequences and other data from the On-Line Encyclopedia of Integer Sequences, pre-processing it (factoring etc), and storing it.
MIT License
1 stars 9 forks source link

Apparent race conditions when adding a new sequence #66

Closed gwhitney closed 1 year ago

gwhitney commented 1 year ago

In debugging https://github.com/numberscope/frontscope/issues/252, I frequently had to add A123456 to a database in which it did not yet occur. Nevertheless, typically (but not always) trying to do a Differences visualization on A123456 when not present in the database would result in the following error log in the backend:

Log ``` 127.0.0.1 - - [03/Apr/2023 20:59:18] "GET /api/get_oeis_values/A123456/100 HTTP/1.1" 200 - 127.0.0.1 - - [03/Apr/2023 20:59:18] "GET /api/get_oeis_factors/A123456/100 HTTP/1.1" 500 - Traceback (most recent call last): File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute cursor.execute(statement, parameters) psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "sequences_pkey" DETAIL: Key (id)=(A123456) already exists. The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/app.py", line 2464, in __call__ return self.wsgi_app(environ, start_response) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/app.py", line 2450, in wsgi_app response = self.handle_exception(e) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask_cors/extension.py", line 165, in wrapped_function return cors_after_request(app.make_response(f(*args, **kwargs))) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/app.py", line 1867, in handle_exception reraise(exc_type, exc_value, tb) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/_compat.py", line 39, in reraise raise value File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/app.py", line 2447, in wsgi_app response = self.full_dispatch_request() File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/app.py", line 1952, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask_cors/extension.py", line 165, in wrapped_function return cors_after_request(app.make_response(f(*args, **kwargs))) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/app.py", line 1821, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/_compat.py", line 39, in reraise raise value File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/app.py", line 1950, in full_dispatch_request rv = self.dispatch_request() File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/flask/app.py", line 1936, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/home/glen/code/backscope/flaskr/nscope/views.py", line 258, in get_oeis_factors seq = find_oeis_sequence(valid_oeis_id, 'full_nofactor') # we're about to do it... File "/home/glen/code/backscope/flaskr/nscope/views.py", line 131, in find_oeis_sequence return save_oeis_sequence(seq, detail) File "/home/glen/code/backscope/flaskr/nscope/views.py", line 70, in save_oeis_sequence db.session.commit() File "", line 2, in commit File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1451, in commit self._transaction.commit(_to_root=self.future) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 829, in commit self._prepare_impl() File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl self.session.flush() File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3383, in flush self._flush(objects) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3522, in _flush with util.safe_reraise(): File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__ compat.raise_( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_ raise exception File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3483, in _flush flush_context.execute() File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute rec.execute(self) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute util.preloaded.orm_persistence.save_obj( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj _emit_insert_statements( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 1097, in _emit_insert_statements c = connection._execute_20( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection return connection._execute_clauseelement( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement ret = self._execute_context( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context self._handle_dbapi_exception( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception util.raise_( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_ raise exception File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/home/glen/code/backscope/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "sequences_pkey" DETAIL: Key (id)=(A123456) already exists. [SQL: INSERT INTO sequences (id, name, shift, values, raw_refs, backrefs, factors) VALUES (%(id)s, %(name)s, %(shift)s, %(values)s::VARCHAR[], %(raw_refs)s, %(backrefs)s::VARCHAR[], %(factors)s::VARCHAR[])] [parameters: {'id': 'A123456', 'name': 'Ludwig van Beethoven, Bagatelle No. 25, "Für Elise".', 'shift': 1, 'values': ['-20', '56', '55', '56', '55', '56', '51', '54', '52', '49', '32', '37', '40', '44', '49', '51', '32', '36', '44', '48', '51', '52', '32', '37', '44' ... (4292 characters truncated) ... '32', '37', '44', '56', '55', '56', '55', '56', '51', '54', '52', '49', '32', '37', '40', '44', '49', '51', '32', '36', '42', '52', '51', '49', '25'], 'raw_refs': 'Cf. A144488.', 'backrefs': ['A144488'], 'factors': None}] (Background on this error at: https://sqlalche.me/e/14/gkpj) ```

Thus it appears that there are usually two threads both trying to add the sequence to the database, and the second one fails.

gwhitney commented 1 year ago

I think what is going on here is that the first get_oeis_values GET returns the values and begins execution of a database update, and the second GET of get_oeis_factors actually computes the factors synchronously and puts the entry in the database. If that second GET actually completes before the deferred database update of the first GET completes, I think it would cause the error. Therefore the recommended fix to try is for every get to synchronously update the database, and later additions of data, either by the deferred operation or by a later synchronous GET, to do updates to an existing record rather than creating a new record.