You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "Gollum999 (via GitHub)" <gi...@apache.org> on 2023/06/15 20:47:13 UTC
[GitHub] [airflow] Gollum999 opened a new issue, #31945: Transaction leak when using `inlets`
Gollum999 opened a new issue, #31945:
URL: https://github.com/apache/airflow/issues/31945
### Apache Airflow version
Other Airflow 2 version (please specify below)
### What happened
We started seeing errors like this in one of our DAGs:
``` 2023-06-15 14:01:08.791 | ERROR | airflow.task | Task failed with exception
Traceback (most recent call last):
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1015, in _commit_impl
self.engine.dialect.do_commit(self.connection)
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 685, in do_commit
dbapi_connection.commit()
psycopg2.errors.IdleInTransactionSessionTimeout: terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/opt/conda/envs/production/lib/python3.9/contextlib.py", line 126, in __exit__
next(self.gen)
File "/opt/conda/envs/production/lib/python3.9/site-packages/airflow/utils/session.py", line 36, in create_session
session.commit()
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1435, in commit
self._transaction.commit(_to_root=self.future)
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 836, in commit
trans.commit()
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2378, in commit
self._do_commit()
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2568, in _do_commit
self._connection_commit_impl()
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2539, in _connection_commit_impl
self.connection._commit_impl()
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1017, in _commit_impl
self._handle_dbapi_exception(e, None, None, None, None)
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
util.raise_(
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1015, in _commit_impl
self.engine.dialect.do_commit(self.connection)
File "/opt/conda/envs/production/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 685, in do_commit
dbapi_connection.commit()
sqlalchemy.exc.InternalError: (psycopg2.errors.IdleInTransactionSessionTimeout) terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
(Background on this error at: https://sqlalche.me/e/14/2j85)
2023-06-15 14:01:08.796 | INFO | airflow.task | Marking task as FAILED. dag_id=test_transaction_timeout, task_id=taskflow_True__sleep_60__inlets_True__outlets_True, execution_date=20230615T183240, start_date=20230615T190008, end_date=20230615T190108
```
This error consistently shows up _after_ the body of the task is done executing. Presumably it is failing when Airflow is trying to update the task state to "success" in the metadata DB. The error also only shows up on Tasks that run longer than 5 minutes, which corresponds to how we have configured Postgres regarding idle transactions (`idle_in_transaction_session_timeout = 300000`).
After much troubleshooting, I tracked this down to the fact that we had started adding [`inlets`](https://airflow.apache.org/docs/apache-airflow/stable/authoring-and-scheduling/datasets.html) to tasks in that DAG. Removing `inlets` and re-running the broken tasks resolves the error.
My theory is that something around this feature is not properly cleaning up its DB connection, leaving a transaction open that eventually causes the connection to get killed by the DB backend. SQLAlchemy must be keeping that zombie connection in its connection pool, which is then re-used when some internal Airflow method uses `create_session`/`@provide_session`.
### What you think should happen instead
Tasks should never get a DB connection that was not properly terminated.
### How to reproduce
First edit `postgresql.conf` to set `idle_in_transaction_session_timeout = 60000`, then run the following DAG:
```
#!/usr/bin/env python3
import datetime
import logging
import time
from airflow.datasets import Dataset
from airflow.decorators import dag, task
logger = logging.getLogger(__name__)
ds_in = Dataset('in')
ds_out = Dataset('out')
@dag(
schedule='@daily',
start_date=datetime.datetime(2023, 6, 15),
default_args={
'retries': 0,
},
)
def test_transaction_timeout():
"""
With PostgreSQL backend, set `idle_in_transaction_session_timeout = 60000` in `postgresql.conf`
"""
def make_task(sleep_time, use_inlets, use_outlets):
task_id = f'sleep_{sleep_time}__inlets_{use_inlets}__outlets_{use_outlets}'
@task(task_id=task_id)
def test_task():
logger.info('hello')
time.sleep(sleep_time)
logger.info('world')
if use_inlets:
test_task = test_task.override(inlets=[ds_in])
if use_outlets:
test_task = test_task.override(outlets=[ds_out])
return test_task()
for sleep_time in [30, 60, 90]:
for use_inlets in [True, False]:
for use_outlets in [True, False]:
make_task(sleep_time, use_inlets, use_outlets)
```
Running this DAG should look like:
### Operating System
CentOS Stream 8
### Versions of Apache Airflow Providers
N/A
### Deployment
Other
### Deployment details
Airflow 2.5.1
Reproduced in self-hosted deployment and locally in standalone mode
Postgres DB backend (v12.11)
### Anything else
This affects both `@task` decorated functions as well as classic Operators.
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Gollum999 commented on issue #31945: Transaction leak when using `inlets`
Posted by "Gollum999 (via GitHub)" <gi...@apache.org>.
Gollum999 commented on issue #31945:
URL: https://github.com/apache/airflow/issues/31945#issuecomment-1597375800
That's a bit frustrating. Some Qs to make sure we aren't missing something obvious:
* Did you restart your DB after applying `idle_in_transaction_session_timeout = 60000`?
* Are you using the default configs around SQLAlchemy pools? Mine are:
```
sql_alchemy_pool_enabled = True
sql_alchemy_pool_size = 5
sql_alchemy_max_overflow = 10
sql_alchemy_pool_recycle = 1800
sql_alchemy_pool_pre_ping = True
```
* Any other non-default Airflow configs? The only ones that come to mind for me are:
```
executor = LocalExecutor
parallelism = 12
sql_alchemy_conn = postgresql+psycopg2://<user>:<pass>@localhost/<db_name>
min_file_process_interval = 5
dag_dir_list_interval = 30
```
Do you know of any other way to track DB connection lifetimes? Maybe there is another way to check for open transactions that doesn't rely on `idle_in_transaction_session_timeout`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Gollum999 commented on issue #31945: Transaction leak when using `inlets`
Posted by "Gollum999 (via GitHub)" <gi...@apache.org>.
Gollum999 commented on issue #31945:
URL: https://github.com/apache/airflow/issues/31945#issuecomment-1598789806
On second thought, I don't think those settings are related. I have overridden those values in my local config to speed up iteration while testing, but our production deployment (which also exhibits this error) uses the default values:
```
min_file_process_interval = 30
dag_dir_list_interval = 300
```
Apologies for the red herring.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] eladkal commented on issue #31945: Transaction leak when using `inlets`
Posted by "eladkal (via GitHub)" <gi...@apache.org>.
eladkal commented on issue #31945:
URL: https://github.com/apache/airflow/issues/31945#issuecomment-1666590068
I'm closing this one as can't reproduce. If new information comes to light we can revisit
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Gollum999 commented on issue #31945: Transaction leak when using `inlets`
Posted by "Gollum999 (via GitHub)" <gi...@apache.org>.
Gollum999 commented on issue #31945:
URL: https://github.com/apache/airflow/issues/31945#issuecomment-1594804866
Just confirmed that this reproduces on 2.6.1 as well.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #31945: Transaction leak when using `inlets`
Posted by "ephraimbuddy (via GitHub)" <gi...@apache.org>.
ephraimbuddy commented on issue #31945:
URL: https://github.com/apache/airflow/issues/31945#issuecomment-1598301884
>Did you restart your DB after applying idle_in_transaction_session_timeout = 60000?
Yeah.
> min_file_process_interval = 5
dag_dir_list_interval = 30
It seems the problem you are having is from this configuration but I haven't tried it. Why do you have these? Doesn't really look good to me
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] eladkal closed issue #31945: Transaction leak when using `inlets`
Posted by "eladkal (via GitHub)" <gi...@apache.org>.
eladkal closed issue #31945: Transaction leak when using `inlets`
URL: https://github.com/apache/airflow/issues/31945
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #31945: Transaction leak when using `inlets`
Posted by "ephraimbuddy (via GitHub)" <gi...@apache.org>.
ephraimbuddy commented on issue #31945:
URL: https://github.com/apache/airflow/issues/31945#issuecomment-1597283374
I'm not able to reproduce this issue. Have tried everything you said here
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #31945: Transaction leak when using `inlets`
Posted by "ephraimbuddy (via GitHub)" <gi...@apache.org>.
ephraimbuddy commented on issue #31945:
URL: https://github.com/apache/airflow/issues/31945#issuecomment-1594388185
2.5.1 is a little bit old. Can you try the reproduction with 2.6.1?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org