Prabhu Joseph created OOZIE-3660:
------------------------------------
Summary: 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
Oozie Coordination Action below queries are intermittently hangs due to deadlock
(@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))
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)
