Working on a new integration, which loads historical data state and stats.
On first setup, I often get errors like the following:
2023-09-29 13:16:58.272 ERROR (Recorder) [homeassistant.components.recorder.core] SQLAlchemyError error processing task CommitTask(): 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: (sqlite3.OperationalError) database is locked
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, '1.987', None, None, None, None, None, 1696018482.108516, None, 23893, None, None, None, 0, <memory at 0x60354928>, None, None, 147)]
(Background on this error at: https://sqlalche.me/e/20/e3q8) (Background on this error at: https://sqlalche.me/e/20/7s2a)
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 904, in _process_one_task_or_recover
return task.run(self)
^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 305, in run
instance._commit_event_session_or_retry()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1155, in _commit_event_session_or_retry
self._commit_event_session()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1175, in _commit_event_session
session.commit()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1906, in commit
trans.commit(_to_root=True)
File "<string>", line 2, in commit
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 103, in _go
self._raise_for_prerequisite_state(fn.__name__, current_state)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 906, in _raise_for_prerequisite_state
raise sa_exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: 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: (sqlite3.OperationalError) database is locked
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, '1.987', None, None, None, None, None, 1696018482.108516, None, 23893, None, None, None, 0, <memory at 0x60354928>, None, None, 147)]
(Background on this error at: https://sqlalche.me/e/20/e3q8) (Background on this error at: https://sqlalche.me/e/20/7s2a)
I guess this is because the SQLite DB is getting pretty busy ingesting all these datapoints, but what can i do about it?
Is there a way to check if the database is locked before queueing a new job to ingest data?
should I chunk these into small jobs? I haven’t tried but not sure if/why this would help.
It eventually works, but it takes a long time due to all the retries.
Any suggestion from the developper community here?
Thanks