database is locked error when use sqlite db

[bluzy]

Apache Airflow version

2.6.2

What happened

I am testing dags on local airflow with default sqlite db.

After upgrade airflow version to 2.6.2 from 2.2.4, I encountered database is locked error when run airflow db init or airflow standalone command.

I inspected code, then I found that session is not released after delete serialized_dag table.

airflow/airflow/utils/db.py

Lines 886 to 893 in 5a0494f

	def _reserialize_dags(*, session: Session) -> None:
	from airflow.models.dagbag import DagBag
	from airflow.models.serialized_dag import SerializedDagModel
	session.execute(delete(SerializedDagModel).execution_options(synchronize_session=False))
	dagbag = DagBag(collect_dags=False)
	dagbag.collect_dags(only_if_updated=False)
	dagbag.sync_to_db(session=session)

After I added session.commit() after deleting serialized_dag table, it works fine.
Or, manually delete rows in serialized_dag table fix this issue..

The problem occurs with our production dag codes, not reproduced with simple example dag codes.
I think only data fields' size are difference, but I don't know why it makes db lock..

And, also here has same db lock problem.

airflow/airflow/utils/db.py

Lines 1022 to 1043 in 624cf7f

	def reflect_tables(tables: list[Base | str] | None, session):
	"""
	When running checks prior to upgrades, we use reflection to determine current state of the database.
	This function gets the current state of each table in the set of models
	provided and returns a SqlAlchemy metadata object containing them.
	"""
	import sqlalchemy.schema
	bind = session.bind
	metadata = sqlalchemy.schema.MetaData()
	if tables is None:
	metadata.reflect(bind=bind, resolve_fks=False)
	else:
	for tbl in tables:
	try:
	table_name = tbl if isinstance(tbl, str) else tbl.__tablename__
	metadata.reflect(bind=bind, only=[table_name], extend_existing=True, resolve_fks=False)
	except exc.InvalidRequestError:
	continue
	return metadata

Likewise, Adding session.commit() after sqlalchemy.schema.MetaData() solves problem.

What you think should happen instead

No response

How to reproduce

Only reproduced with sqlite db.
I don't know exact condition, but I guess large dags cause the problem..

Operating System

MacOS (Apple Silicon)

Versions of Apache Airflow Providers

No response

Deployment

Virtualenv installation

Deployment details

No response

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[potiuk]

First of all sqlitedb should only be used for local unit testing and quick start (see the documetation where we clearly warn against using it for anything else)

Secondly - the _reserilalize_dag is only used in "upgradedb" that is closing session (via @provide_session decorator - and it's precisely as plannec to not close sessons.

One of the main reasons of "development" status is that Sqlite cannot work when you have two concurrent accesses to the sqlite file, this creates a lock - which is likely what you observed. you likley had another airflow process still running and accessing the database while you were running upgrade - this is likely why you saw the lock happen. Don't do it. Don't use sqlite for anything else than local unit testing and quick start, you will have all kinds of similar problems like that what you do.

Converted to discussion in case more of it is needed.

[DFINITYManu]

This is definitely a bug in the reflect_tables() code. I'm using 2.9.1 so the bug clearly has not been fixed from 2.6.x to 2.9.x.

And those of us using Airflow during development really do not appreciate being told "don't use it for production" -- yes, we know that already.

[DFINITYManu]

Actually, the bug hasn't even been fixed in main. I still get this issue, and this workaround still works flawlessly.

[potiuk]

If you have an idea how to fix it - feel free. This discussion is from 2023 - which is 2 years ago. Since then not only a lot of things happened, but also we changed the mode of using sqlite - to actually allow concurrency (which was not there 2 years ago)l

So if you have anything to say that changes it here and if there is a similar issue in main - the best way to do something about it is to create a PR fixing it @DFINITYManu . The second best, is to create an issue describing in detail what problem you have with the "current" main - including stack traces and what kind of workaround helps. Commenting on 2 years old discussion with vague comment that might or might not have to do anything with the issue you see currently in main does not move a needle with solving the issue.

[potiuk]

And yes - 3 years ago and even for any Airflow 2 version my answer was "DO NOT USE it in multi-process access" because it has never been designed to do it.

With Airflow 3 - i..e. main, this is a different case because ... we added that capability ... so if you see any issue in main about it - PLEASE open an issue rather than complain about 2 years old comment, Venting your frustration by reading 2 years old post does not really help with solving the issue.