You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "rahulsinha07 (via GitHub)" <gi...@apache.org> on 2023/04/05 05:55:11 UTC
[GitHub] [airflow] rahulsinha07 opened a new issue, #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
rahulsinha07 opened a new issue, #30480:
URL: https://github.com/apache/airflow/issues/30480
### Apache Airflow version
2.5.3
### What happened
I am trying to run a Dag with MSSQL version 19, backend and airflow version as 2.5.0
#below is the DAG: Define the DAG with timezone
``*import pendulum
from airflow import DAG
from airflow.operators.python_operator import PythonOperator
from datetime import timedelta
dag = DAG("my_tz_dag", start_date=pendulum.datetime(2016, 1, 1, tz="Europe/Amsterdam"), schedule_interval=timedelta(minutes=15))
def print_hello():
print("Hello, world!")
task = PythonOperator(task_id="print_hello", python_callable=print_hello, dag=dag)`
am getting this in logs and scheduler goes down
Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 194) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)') [SQL: UPDATE dag_run SET last_scheduling_decision=?, updated_at=? WHERE dag_run.id = ?] [parameters: ((datetime.datetime(2023, 4, 3, 18, 43, 12, 737104, tzinfo=Timezone('UTC')),
[Scheduler goes down with this issue](https://i.stack.imgur.com/0VbHX.png)
I tried without giving timezone as well ,as below but this one does not start it self .
Manual Trigger works fine , But scheduling does not work,
from datetime import datetime, timedelta
from airflow import DAG
from airflow.operators.bash_operator import BashOperator
default_args = {
'owner': 'airflow',
'depends_on_past': False,
'start_date': datetime(2023, 3, 1),
'retries': 1,
'retry_delay': timedelta(minutes=5)
}
dag = DAG(
'log_cleaning',
default_args=default_args,
schedule_interval='*/5 * * * ',
catchup=False
)
log_cleaning = BashOperator(
task_id='log_cleaning',
bash_command='find /usr/local/airflow/logs/ -mtime +7 -exec rm {} ;',
dag=dag
)
### What you think should happen instead
holding an exclusive lock on the "dag_run" table
### How to reproduce
MSSQL version 19, backend and airflow version as 2.5.0
Put below DAG
#below is the DAG: Define the DAG with timezone
``*import pendulum
from airflow import DAG
from airflow.operators.python_operator import PythonOperator
from datetime import timedelta
dag = DAG("my_tz_dag", start_date=pendulum.datetime(2016, 1, 1, tz="Europe/Amsterdam"), schedule_interval=timedelta(minutes=15))
def print_hello():
print("Hello, world!")
task = PythonOperator(task_id="print_hello", python_callable=print_hello, dag=dag)`
am getting this in logs and scheduler goes down
Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 194) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)') [SQL: UPDATE dag_run SET last_scheduling_decision=?, updated_at=? WHERE dag_run.id = ?] [parameters: ((datetime.datetime(2023, 4, 3, 18, 43, 12, 737104, tzinfo=Timezone('UTC')),
[Scheduler goes down with this issue](https://i.stack.imgur.com/0VbHX.png)
### Operating System
NAME="CentOS Linux" VERSION="7 (Core)" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="7" PRETTY_NAME="CentOS Linux 7 (Core)"
### Versions of Apache Airflow Providers
_No response_
### Deployment
Docker-Compose
### Deployment details
_No response_
### Anything else
<deadlock>
<victim-list>
<victimProcess id="process1a91dc83088" />
</victim-list>
<process-list>
<process id="process1a91dc83088" taskpriority="0" logused="2888" waitresource="KEY: 56:72057594051756032 (b81181109ebc)" waittime="988" ownerId="455607779" transactionname="user_transaction" lasttranstarted="2023-04-04T22:59:22.590" XDES="0x1ad05070428" lockMode="U" schedulerid="4" kpid="4372" status="suspended" spid="109" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-04-04T22:59:22.780" lastbatchcompleted="2023-04-04T22:59:22.780" lastattention="1900-01-01T00:00:00.780" clientapp="python3.7" hostname="1f8a42cf9beb" hostpid="536968" loginname="airflow_user" isolationlevel="read committed (2)" xactid="455607779" currentdb="56" currentdbname="airflow" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="74" stmtend="244" sqlhandle="0x020000003c20e418758d69583ba929a1f81da97ad733fb4e0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 datetime2,@P2 datetime2,@P3 int)UPDATE dag_run SET last_scheduling_decision=@P1, updated_at=@P2 WHERE dag_run.id = @P3 </inputbuf>
</process>
<process id="process1a9210de4e8" taskpriority="0" logused="3436" waitresource="KEY: 56:72057594052935680 (a5115045c944)" waittime="1019" ownerId="455607758" transactionname="user_transaction" lasttranstarted="2023-04-04T22:59:22.550" XDES="0x1a7ca020428" lockMode="U" schedulerid="2" kpid="8644" status="suspended" spid="173" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-04-04T22:59:22.747" lastbatchcompleted="2023-04-04T22:59:22.733" lastattention="1900-01-01T00:00:00.733" clientapp="python3.7" hostname="1f8a42cf9beb" hostpid="537478" loginname="airflow_user" isolationlevel="read committed (2)" xactid="455607758" currentdb="56" currentdbname="airflow" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="278" stmtend="754" sqlhandle="0x02000000e0189122ec961ed4043800982dfb3901b55a7a690000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 datetime2,@P2 nvarchar(14),@P3 int,@P4 nvarchar(24),@P5 int,@P6 datetime2,@P7 nvarchar(18),@P8 nvarchar(22),@P9 nvarchar(72),@P10 int)UPDATE task_instance SET start_date=@P1, state=@P2, try_number=@P3, hostname=@P4, job_id=@P5, updated_at=@P6 WHERE task_instance.dag_id = @P7 AND task_instance.task_id = @P8 AND task_instance.run_id = @P9 AND task_instance.map_index = @P10 </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594051756032" dbid="56" objectname="airflow.dbo.dag_run" indexname="dag_run_pkey" id="lock1a911cbb680" mode="X" associatedObjectId="72057594051756032">
<owner-list>
<owner id="process1a9210de4e8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process1a91dc83088" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594052935680" dbid="56" objectname="airflow.dbo.task_instance" indexname="task_instance_pkey" id="lock1a7f661ea00" mode="X" associatedObjectId="72057594052935680">
<owner-list>
<owner id="process1a91dc83088" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process1a9210de4e8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
### Are you willing to submit PR?
- [X] 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] rahulsinha07 commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "rahulsinha07 (via GitHub)" <gi...@apache.org>.
rahulsinha07 commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1497463192
@potiuk Thanks for your reply . Please find the below Log from DB , which specify the Lock condition
<deadlock>
<victim-list>
<victimProcess id="process1a91dc83088" />
</victim-list>
<process-list>
<process id="process1a91dc83088" taskpriority="0" logused="2888" waitresource="KEY: 56:72057594051756032 (b81181109ebc)" waittime="988" ownerId="455607779" transactionname="user_transaction" lasttranstarted="2023-04-04T22:59:22.590" XDES="0x1ad05070428" lockMode="U" schedulerid="4" kpid="4372" status="suspended" spid="109" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-04-04T22:59:22.780" lastbatchcompleted="2023-04-04T22:59:22.780" lastattention="1900-01-01T00:00:00.780" clientapp="python3.7" hostname="1f8a42cf9beb" hostpid="536968" loginname="airflow_user" isolationlevel="read committed (2)" xactid="455607779" currentdb="56" currentdbname="airflow" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="74" stmtend="244" sqlhandle="0x020000003c20e418758d69583ba929a1f81da97ad733fb4e0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 datetime2,@P2 datetime2,@P3 int)UPDATE dag_run SET last_scheduling_decision=@P1, updated_at=@P2 WHERE dag_run.id = @P3 </inputbuf>
</process>
<process id="process1a9210de4e8" taskpriority="0" logused="3436" waitresource="KEY: 56:72057594052935680 (a5115045c944)" waittime="1019" ownerId="455607758" transactionname="user_transaction" lasttranstarted="2023-04-04T22:59:22.550" XDES="0x1a7ca020428" lockMode="U" schedulerid="2" kpid="8644" status="suspended" spid="173" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-04-04T22:59:22.747" lastbatchcompleted="2023-04-04T22:59:22.733" lastattention="1900-01-01T00:00:00.733" clientapp="python3.7" hostname="1f8a42cf9beb" hostpid="537478" loginname="airflow_user" isolationlevel="read committed (2)" xactid="455607758" currentdb="56" currentdbname="airflow" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="278" stmtend="754" sqlhandle="0x02000000e0189122ec961ed4043800982dfb3901b55a7a690000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 datetime2,@P2 nvarchar(14),@P3 int,@P4 nvarchar(24),@P5 int,@P6 datetime2,@P7 nvarchar(18),@P8 nvarchar(22),@P9 nvarchar(72),@P10 int)UPDATE task_instance SET start_date=@P1, state=@P2, try_number=@P3, hostname=@P4, job_id=@P5, updated_at=@P6 WHERE task_instance.dag_id = @P7 AND task_instance.task_id = @P8 AND task_instance.run_id = @P9 AND task_instance.map_index = @P10 </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594051756032" dbid="56" objectname="airflow.dbo.dag_run" indexname="dag_run_pkey" id="lock1a911cbb680" mode="X" associatedObjectId="72057594051756032">
<owner-list>
<owner id="process1a9210de4e8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process1a91dc83088" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594052935680" dbid="56" objectname="airflow.dbo.task_instance" indexname="task_instance_pkey" id="lock1a7f661ea00" mode="X" associatedObjectId="72057594052935680">
<owner-list>
<owner id="process1a91dc83088" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process1a9210de4e8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
--
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] potiuk commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1498684959
Basically - each database behaves differently. DAtabases have different dialects and a lot of the code is different: on the server, on the client, in the sqlialchemy core, in the drivers. There are even different behaviours that make the whole stack behaves differently even if different sqlalchemy driver for the same database, or sometimes there is a a difference between differen minor version of the same database.
Any of small changes in any of the layers above might cause the problem you mentioned.
If you would like to investigate it on your own, or find someone who you would like to pay for to investigate the very problem on MSSQL, this is a great contribution you can make to the community - that could likely help to move the MSSQL support out of experimental if some company like yours that is determined to use MSSQL could spend time adn money and contribute back the findings and benchmarking to move it out of experimental status.
--
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] github-actions[bot] closed issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
URL: https://github.com/apache/airflow/issues/30480
--
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] github-actions[bot] commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1553827349
This issue has been automatically marked as stale because it has been open for 30 days with no response from the author. It will be closed in next 7 days if no further activity occurs from the issue author.
--
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] potiuk commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1498678176
> Thanks for you reply , is there a valid reason why same thing works fine with potgresql and difference in implantation of Postgres and SQL server?
If I knew the answer, I would probably know how to fix it. But I don't
--
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] boring-cyborg[bot] commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "boring-cyborg[bot] (via GitHub)" <gi...@apache.org>.
boring-cyborg[bot] commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1496956421
Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.
--
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] p10 commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "p10 (via GitHub)" <gi...@apache.org>.
p10 commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1497094044
I dont care
--
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] rahulsinha07 commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "rahulsinha07 (via GitHub)" <gi...@apache.org>.
rahulsinha07 commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1498628926
Thanks for you reply , is there a valid reason why same thing works fine with potgresql and difference in implantation of Postgres and SQL server?
--
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] potiuk commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1497091273
I am afraid this one might be difficult to solve. MSSQL support is still experimental https://airflow.apache.org/docs/apache-airflow/stable/installation/prerequisites.html and because of a very small number of people using it and the kind of problems you see, I personally think we will soon consider dropping it altogether and I'd strongly advice you continue using Postgres.
In any case I would not count on fast resolution of this problem, mssql has extremely low priority for maintainers here.
However, if you want to provide more diagnostics on it - maybe we will be able to at least see what's going on - If you could provide logs from the database showing what the deadlock was with - usually databases (mssql as well) likely has more details logs where the deadlock is better explained - if you can find the right log part containing the deadlock and create a gist with the log and link it here, that would be great.
--
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] github-actions[bot] commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1563652485
This issue has been closed because it has not received response from the issue author.
--
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] potiuk commented on issue #30480: Airflow is having deadlock in backend database with MSSQL , Same Dag is working fine in postgresql backed
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk commented on issue #30480:
URL: https://github.com/apache/airflow/issues/30480#issuecomment-1497951211
IT's not very useful I am afraid. I guess solving it would need a long investigation. As I said, I doubt we have anyone who would spend their time - maybe someone will look at it some time in the future -but (just to set expectations) - people are helping here and looking at the issues in their volunteer time. Due to circumstances and state of MsSQL described above - I seriously doubt any one here has their prioroty/mind set on MySQL - so if you need to have quick resolution, you should either find someone that can do a deep investigation on this issue. You will be better off sticking with Postgres.
--
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