SQLite database is locked occasionally when executing time tasks concurrently

[zhangshengchun]

use 'timer tasks' to schedule some spiders run periodically，sometimes when a spider scheduled to execute，but it not run and Throw a "database is locked" error.

Logs: [2019-04-30 00:00:27,840] ERROR in apscheduler: Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) sqlite3.OperationalError: database is locked

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/scrapydweb/operations/execute_task.py", line 170, in execute_task task_executer.main() File "/usr/local/lib/python3.6/site-packages/scrapydweb/operations/execute_task.py", line 43, in main self.get_task_result_id() File "/usr/local/lib/python3.6/site-packages/scrapydweb/operations/execute_task.py", line 70, in get_task_result_id db.session.commit() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 162, in do return getattr(self.registry(), name)(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1026, in commit self.transaction.commit() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 493, in commit self._prepare_impl() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 472, in _prepare_impl self.session.flush() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2451, in flush self._flush(objects) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2589, in _flush transaction.rollback(_capture_exception=True) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 68, in exit compat.reraise(exc_type, exc_value, exc_tb) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 129, in reraise raise value File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2549, in _flush flush_context.execute() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute rec.execute(self) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute uow, File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj insert, File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 1120, in _emit_insert_statements statement, params File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement distilled_params, File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context e, statement, parameters, cursor, context File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception util.raise_from_cause(sqlalchemy_exception, exc_info) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked [SQL: INSERT INTO task_result (task_id, execute_time, fail_count, pass_count) VALUES (?, ?, ?, ?)] [parameters: (15, '2019-04-30 00:00:22.834299', 0, 0)] (Background on this error at: http://sqlalche.me/e/e3q8)

[my8100]

How often does it take place? Could you show me the parameters of all running tasks in detail by posting screenshots?

[zhangshengchun]

How often does it take place? Could you show me the parameters of all running tasks in detail by posting screenshots?

occasionally，is the time tasks history log file helpful for your analysis: time_task_history.log

[my8100]

SQLite is too fragile for concurrent writing. A way to work around is adjusting the minute and/or second parameter to ensure that your tasks wouldn't trigger concurrently. The more stable database like MySQL and PostgreSQL would be supported in the next release.

[my8100]

Fixed in PR #76.