[
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
*Two 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
>
> *Two 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)
