Wednesday, May 6, 2015

Design question/ideas for .Net Client Server application with deadlocking issue

Here is some the T-SQL being executed by the app against that table (5 examples that have turned up from traces):

 --TSQL #1
SET NOCOUNT ON;

DECLARE @RECCOUNT INT;

SET @RECCOUNT = {##};

BEGIN TRANSACTION;

UPDATE BackgroundTasks WITH (XLOCK, TABLOCK) 

  SET THREAD_ID = {##} 

  WHERE BackgroundTasks_ID IN (SELECT TOP {##} BackgroundTasks_ID 

 FROM BackgroundTasks EC1 

 WHERE COMPANY_CODE = {STR} AND ID_VALUE > {##} 

AND NUMBER_OF_TRIES <= {##} 

AND (THREAD_ID IS NULL OR THREAD_ID = {##}) 

AND (PROCESSED_DATE <= {FN NOW()} OR PROCESSED_DATE IS NULL) 

AND STATUS = {STR} 

AND (NOT ID_FIELD_NAME LIKE {STR} OR NOT EXISTS(SELECT * 

FROM BackgroundTasks EC2 

WHERE EC1.BATCH_ID = EC2.BATCH_ID 

 AND EC1.BackgroundTasks_ID <> EC2.BackgroundTasks_ID 

 AND NOT EC2.ID_FIELD_NAME LIKE {STR}

)



 ORDER BY PROCESSED_DATE, BackgroundTasks_ID ASC

);

SET @RECCOUNT = @@ROWCOUNT;

UPDATE BackgroundTasks WITH (XLOCK, TABLOCK) 

 SET THREAD_ID = {##} 

 WHERE BackgroundTasks_ID IN (SELECT TOP {##} BackgroundTasks_ID 

FROM BackgroundTasks EC1 

WHERE COMPANY_CODE = {STR} 

  AND ID_VALUE > {##} 

  AND NUMBER_OF_TRIES <= {##} 

  AND (THREAD_ID IS NULL OR THREAD_ID = {##}) 

  AND (PROCESSED_DATE <= {FN NOW()} OR PROCESSED_DATE IS NULL) 

  AND STATUS = {STR} 

  AND (NOT ID_FIELD_NAME LIKE {STR} OR NOT EXISTS(SELECT * 

  FROM BackgroundTasks EC2 

  WHERE EC1.BATCH_ID = EC2.BATCH_ID 

AND EC1.BackgroundTasks_ID <> EC2.BackgroundTasks_ID 

AND NOT EC2.ID_FIELD_NAME LIKE {STR})

  ) 

ORDER BY PROCESSED_DATE, BackgroundTasks_ID ASC

);

SET @RECCOUNT = @RECCOUNT + @@ROWCOUNT;

COMMIT TRANSACTION;

SET NOCOUNT OFF;

SELECT @RECCOUNT AS RECORDCOUNT;



 --TSQL #2
SELECT * FROM BackgroundTasks WHERE ID_VALUE = {##} AND ID_FIELD_NAME = {STR}

 --TSQL #3
SELECT * FROM BackgroundTasks WHERE COMPANY_CODE = {STR}

 --TSQL #4
DELETE FROM BackgroundTasks WHERE BackgroundTasks_ID = {##}

 --TSQL #5
SELECT DISTINCT THREAD_ID FROM BackgroundTasks WHERE COMPANY_CODE = {STR} AND THREAD_ID LIKE {STR}

No comments:

Post a Comment