DML Operations - Deadlock Victim SQL Server

General Discussion
1

Hi All,

I am supporting a development team leveraging liquibase to introduce CI/CD to database changes and eliminate the need for DBAs to deploy changes.

We’ve been working well for DDL changes. However, occasionally we have some DML operations needed for data fixes, etc.

I came across an example the other day that was raising a SQL Server Deadlock situation. Whereby one large formatted SQL file as a larger changeset.

I was able to work around this by wrapping it with a BEGIN and END for a transaction.

Please is an example with the 3 Operation causing a deadlock. With last causing it:

DROP TABLE IF EXISTS #TEMP;
DROP TABLE IF EXISTS #TEMP1;
DROP TABLE IF EXISTS #TEMP2;
DROP TABLE IF EXISTS #TEMP3;
DROP TABLE IF EXISTS #TEMP4;

SELECT PL.AccountId, PL.PatientId, PL.VisitNumber, PT.ProcedureTypeCode, STRING_AGG(PCM.ProcedureCodeMasterId,',') ProcedureCodeMasterId INTO #TEMP FROM CON.ProcedureLog PL With(NOLOCK) JOIN TRE.PatientTreatmentPlanProcedure PTTP With(NOLOCK) ON PTTP.accountid = PL.accountid AND PL.patientid = PTTP.patientid AND PTTP.DetailReferenceNumber = PL.DetailReferenceNumber JOIN CON.ProcedureCodeMaster PCM 
With(NOLOCK) ON PL.ProcedureCodeMasterId = PCM.ProcedureCodeMasterId JOIN CON.ProcedureType PT With(NOLOCK) ON PCM.ProcedureTypeId = PT.ProcedureTypeId WHERE PT.ProcedureTypeCode = 'C'
GROUP BY PL.AccountId, PL.PatientId, PL.VisitNumber,PT.ProcedureTypeCode	
ORDER BY PL.AccountId;

SELECT DISTINCT PL.AccountId, PL.PatientId, PL.VisitNumber VisitNumber INTO #TEMP1 FROM CON.ProcedureLog PL With(NOLOCK) JOIN #TEMP T ON PL.AccountId = T.AccountId AND PL.PatientId = T.PatientId AND PL.VisitNumber = T.VisitNumber  AND PL.ProcedureCodeMasterId NOT IN (SELECT VALUE FROM STRING_SPLIT(T.ProcedureCodeMasterId,',') WHERE VALUE != '') INNER JOIN CON.ProcedureCodeMaster PCM With(NOLOCK) ON PCM.ProcedureCodeMasterId = PL.ProcedureCodeMasterId;

SELECT DISTINCT A.* INTO #TEMP2 FROM #TEMP A LEFT JOIN #TEMP1 B ON A.AccountId = B.AccountId AND A.patientId = B.patientId AND A.VisitNumber = B.VisitNumber WHERE B.AccountId IS NULL;

What are recommendations on handling this to run in order OR prevent this from occruing? If I run directly via a SQL Command in SQL Server Management Studio no issues occur

2

I’m not really understanding your changelog.

You are dropping the #TEMP* tables, then attempting to use them in the subsequent SQL statements.

I’ll admit I’m not familiar with SQL-Server, so I must be missing something obvious.

3

Hi @daryldoak - even if I remove the temp table drops. This is still causing a deadlock.

Specifically this one causes the deadlock:

SELECT DISTINCT A.* INTO #TEMP2 FROM #TEMP A LEFT JOIN #TEMP1 B ON A.AccountId = B.AccountId AND A.patientId = B.patientId AND A.VisitNumber = B.VisitNumber WHERE B.AccountId IS NULL;
4

I’m not familiar with transaction control in sql-server. I’ll need to let someone else handle this one. Sorry.