Event database is locked

[bendnorman]

PUDL deploys dagster using the dagster-webserver command in a conda environment. We run our full dagster DAG each night on a single machine with 64 GB of RAM and 8 CPUs. We've started to run into a stochastic failure with the dagster sqlite log database:

Traceback (most recent call last):
  File "/home/catalyst/env/bin/pudl_etl", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/catalyst/src/pudl/cli/etl.py", line 166, in main
    raise Exception(event.event_specific_data.error)
Exception: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
[SQL: INSERT INTO event_logs (run_id, event, dagster_event_type, timestamp, step_key, asset_key, partition) VALUES (?, ?, ?, ?, ?, ?, ?)]
[parameters: ('7462889e-ee58-462e-8eb3-452042c3995d', '{"__class__": "EventLogEntry", "dagster_event": {"__class__": "DagsterEvent", "event_specific_data": null, "event_type_value": "STEP_START", "logging ... (745 characters truncated) ... 042c3995d", "step_key": "static_pudl_tables", "timestamp": 1697784113.4544585, "user_message": "Started execution of step \\"static_pudl_tables\\"."}', 'STEP_START', '2023-10-20 06:41:53.454458', 'static_pudl_tables', None, None)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Stack Trace:
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/execution/plan/execute_plan.py", line 275, in dagster_event_sequence_for_step
    for step_event in check.generator(step_events):
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/execution/plan/execute_step.py", line 428, in core_dagster_event_sequence_for_step
    yield DagsterEvent.step_start_event(step_context)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/events/__init__.py", line 912, in step_start_event
    return DagsterEvent.from_step(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/events/__init__.py", line 430, in from_step
    log_step_event(step_context, event)
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/events/__init__.py", line 309, in log_step_event
    step_context.log.log_dagster_event(
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/log_manager.py", line 406, in log_dagster_event
    self.log(level=level, msg=msg, extra={DAGSTER_META_KEY: dagster_event})
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/log_manager.py", line 421, in log
    self._log(level, msg, args, **kwargs)
  File "/home/catalyst/env/lib/python3.11/logging/__init__.py", line 1634, in _log
    self.handle(record)
  File "/home/catalyst/env/lib/python3.11/logging/__init__.py", line 1644, in handle
    self.callHandlers(record)
  File "/home/catalyst/env/lib/python3.11/logging/__init__.py", line 1706, in callHandlers
    hdlr.handle(record)
  File "/home/catalyst/env/lib/python3.11/logging/__init__.py", line 978, in handle
    self.emit(record)
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/log_manager.py", line 287, in emit
    handler.handle(dagster_record)
  File "/home/catalyst/env/lib/python3.11/logging/__init__.py", line 978, in handle
    self.emit(record)
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/instance/__init__.py", line 224, in emit
    self._instance.handle_new_event(event)
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/instance/__init__.py", line 2142, in handle_new_event
    self._event_storage.store_event(event)
  File "/home/catalyst/env/lib/python3.11/site-packages/dagster/_core/storage/event_log/sqlite/sqlite_event_log.py", line 238, in store_event
    conn.execute(insert_event_statement)
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1385, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)

The above exception was caused by the following exception:
sqlite3.OperationalError: database is locked

Stack Trace:
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/home/catalyst/env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)

Questions

• Why would this error happen on a 64 GB 8-core machine but not on our local machines, which have 10 cores and 32 GB of RAM?
• Would you suggest switching to using the Postgres / Docker dagster deployment? Switching from conda environments to the multiple container Docker setup would be a significant change for PUDL. Do you have any other recommendations for resolving the locked event log database issue? Is there a potential work around that can limit the number of processes trying to write to the event log?

[bendnorman]

We ended up resolving this issue by running a postgres database for the dagster backend. We are running the postgres database in the same container because our existing build infrastructure isn't set up to handle multi-container applications.