![]() 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. 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 setting spconfigure max degree of parallelism, 1 as well replacing scopeidentity with identity. Also I found some uncommitted transactions in code which is also another factor for creating deadlock. An alternative to this would be to use set transaction isolation level read uncommitted. You can also use with (nolock) on tables which are joined to the table which is being updated. GROUP BY PL.AccountId, PL.PatientId, PL.VisitNumber,PT.ProcedureTypeCode Following are the solutions I applied for deadlock in my scenario : deadlock is solved by setting row lock on insert and update query. This will tell the query optimiser to lock rows one by one as they are updated, rather than to use a page or table lock. ![]() With(NOLOCK) ON PL.ProcedureCodeMasterId = PCM.ProcedureCodeMasterId JOIN CON.ProcedureType PT With(NOLOCK) ON PCM.ProcedureTypeId = PT.ProcedureTypeId WHERE PT.ProcedureTypeCode = 'C' ![]() 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 last causing it: DROP TABLE IF EXISTS #TEMP Please is an example with the 3 Operation causing a deadlock. I was able to work around this by wrapping it with a BEGIN and END for a transaction. Whereby one large formatted SQL file as a larger changeset. I came across an example the other day that was raising a SQL Server Deadlock situation. However, occasionally we have some DML operations needed for data fixes, etc. MS SQL DB supports of using nolock option.I am supporting a development team leveraging liquibase to introduce CI/CD to database changes and eliminate the need for DBAs to deploy changes. Transaction wise, is there any diferent behaviour for JDBC adapter with Oracle DB & MS SQL DB. If we use NO_Transaction type for JDBC Polling, what will be the functionality wise difference with implicit Local transaction ? Is JDBC polling holds lock at the row level or table level ? if locks is at table level, then it prevent any concurrent transactions to insert/ modify data in the same buffer table from the End system. We are getting deadlock issue while Polling is in active mode & to troubleshoot further we need below information: The JDBC conection with MSSQL 2000 DB is defined as “LOCAL_TRANSACTION” type & wM is doing implicit transaction ![]() Mark ID Column field ) as ‘y’ to mark it as processed. We are using WM JDBC Polling (Basic Notification) to pick up data from buffer table with an interval of 60 sec & update status flag (i.e. We have an existing integration with MSSQL 2000. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |