You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@oozie.apache.org by "Prabhu Joseph (Jira)" <ji...@apache.org> on 2022/02/23 17:56:00 UTC
[jira] [Updated] (OOZIE-3660) Deadlock at Oozie Sql Server Database
[ https://issues.apache.org/jira/browse/OOZIE-3660?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Prabhu Joseph updated OOZIE-3660:
---------------------------------
Description:
Oozie Coordination Action below queries are intermittently hangs due to deadlock
{code}
(@P0 varchar(8000),@P1 varchar(8000))SELECT COUNT(t0.id) FROM COORD_ACTIONS t0 WHERE (t0.job_id = @P0 AND t0.status = @P1)
(@P4 varchar(8000),@P0 varchar(8000),@P1 datetime2(7),@P2 varbinary(8000),@P3 varbinary(8000))UPDATE COORD_ACTIONS SET status = @P0, last_modified_time = @P1, action_xml = @P2, missing_dependencies = @P3 WHERE id IN (SELECT DISTINCT t0.id FROM COORD_ACTIONS t0 WHERE (t0.id = @P4))
{code}
*The deadlock occurs when:*
1 - the SELECT obtains a Shared lock on a non clustered index
2 - the UPDATE obtains an Exclusive lock on the Clustered index.
3 - the Select tries to obtain a Shared lock on the Clustered (key lookup) - blocked
4 - the Update tries to write (Exclusive lock) on the non clustered - blocked
*Three ways to solve this:*
- alter the existing index or create a new one that satisfies both where clauses - job_id and status. For example, "Create index ix_coordactionsjid_status on COORD_ACTIONS (status, job_id)"
- use READ COMMITED SNAPSHOT ISOLATION
*Thanks to Ricardo Nunes (SQL Server Expert) for the analysis.*
was:
Oozie Coordination Action below queries are intermittently hangs due to deadlock
{code}
(@P0 varchar(8000),@P1 varchar(8000))SELECT COUNT(t0.id) FROM COORD_ACTIONS t0 WHERE (t0.job_id = @P0 AND t0.status = @P1)
(@P4 varchar(8000),@P0 varchar(8000),@P1 datetime2(7),@P2 varbinary(8000),@P3 varbinary(8000))UPDATE COORD_ACTIONS SET status = @P0, last_modified_time = @P1, action_xml = @P2, missing_dependencies = @P3 WHERE id IN (SELECT DISTINCT t0.id FROM COORD_ACTIONS t0 WHERE (t0.id = @P4))
{code}
*The deadlock occurs when:*
1 - the SELECT obtains a Shared lock on a non clustered index
2 - the UPDATE obtains an Exclusive lock on the Clustered index.
3 - the Select tries to obtain a Shared lock on the Clustered (key lookup) - blocked
4 - the Update tries to write (Exclusive lock) on the non clustered - blocked
*Three ways to solve this:*
- alter the existing index or create a new one that satisfies both where clauses - job_id and status. For example, "Create index ix_coordactionsjid_status on COORD_ACTIONS (status, job_id)"
- use READ COMMITED SNAPSHOT ISOLATION
*
Thanks to Ricardo Nunes (SQL Server Expert) for the analysis.*
> Deadlock at Oozie Sql Server Database
> -------------------------------------
>
> Key: OOZIE-3660
> URL: https://issues.apache.org/jira/browse/OOZIE-3660
> Project: Oozie
> Issue Type: Bug
> Affects Versions: 5.3.0
> Reporter: Prabhu Joseph
> Assignee: Prabhu Joseph
> Priority: Major
>
> Oozie Coordination Action below queries are intermittently hangs due to deadlock
> {code}
> (@P0 varchar(8000),@P1 varchar(8000))SELECT COUNT(t0.id) FROM COORD_ACTIONS t0 WHERE (t0.job_id = @P0 AND t0.status = @P1)
> (@P4 varchar(8000),@P0 varchar(8000),@P1 datetime2(7),@P2 varbinary(8000),@P3 varbinary(8000))UPDATE COORD_ACTIONS SET status = @P0, last_modified_time = @P1, action_xml = @P2, missing_dependencies = @P3 WHERE id IN (SELECT DISTINCT t0.id FROM COORD_ACTIONS t0 WHERE (t0.id = @P4))
> {code}
>
> *The deadlock occurs when:*
> 1 - the SELECT obtains a Shared lock on a non clustered index
> 2 - the UPDATE obtains an Exclusive lock on the Clustered index.
> 3 - the Select tries to obtain a Shared lock on the Clustered (key lookup) - blocked
> 4 - the Update tries to write (Exclusive lock) on the non clustered - blocked
>
> *Three ways to solve this:*
> - alter the existing index or create a new one that satisfies both where clauses - job_id and status. For example, "Create index ix_coordactionsjid_status on COORD_ACTIONS (status, job_id)"
> - use READ COMMITED SNAPSHOT ISOLATION
> *Thanks to Ricardo Nunes (SQL Server Expert) for the analysis.*
--
This message was sent by Atlassian Jira
(v8.20.1#820001)