[jira] [Created] (OOZIE-3660) Deadlock at Oozie Sql Server Database

["Prabhu Joseph (Jira)" <ji...@apache.org>]

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)