Deadlock when reading job data from SQL database

question
1

I running following query to aggregate some job data:

SELECT
    B.JobId,
    SUBSTRING(B.Name, 1, CHARINDEX(',', B.Name) - 1) AS Name,
    LastState,
    C.CreatedAt AS LastStateCreatedAt,
    REPLACE(D.Value, '"', '') AS RecurringJobName,
    B.CreatedAt
FROM
(
    SELECT
        Id AS JobId,
        COALESCE(
            JSON_VALUE(InvocationData, '$.t'),
            JSON_VALUE(InvocationData, '$.Type')
        ) AS Name,
        StateId AS LastStateId,
        StateName AS LastState,
        CreatedAt
    FROM
        Hangfire.Job
) AS B
LEFT JOIN Hangfire.State AS C ON Id = B.LastStateId
LEFT JOIN Hangfire.JobParameter AS D ON (B.JobId = D.JobId AND D.Name = 'RecurringJobId')

The query works but crashes randomly with following error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.ReadAsyncExecute(Task task, Object state)
   at Microsoft.Data.SqlClient.SqlDataReader.InvokeAsyncCall[T](AAsyncCallContext`1 context)

Any idea what might cause this issue?

2

Using WITH(NOLOCK) like here https://github.com/HangfireIO/Hangfire/blob/master/src/Hangfire.SqlServer/SqlServerMonitoringApi.cs#L543 solved the issue.