Deadlocked on lock resources in sql

Question

I am working in windows application c# MultiThread Functionality(5 Thread) More than 2M data while deleting with my internal code suddenly deadlock error occured help how to rectify and resolve the Problem Error:Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

i refer website and found one query to solve this problem here i mentioned that query but sometimes its not working some time its working SQL: ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE GO

Help how to solve that problem what should i change whether in code or sql Management

Code

if (SqlCon.State == ConnectionState.Closed)
{
   OpenConnection();
}

string sqlStmt = "Delete em from Order em inner join OrderHeader ch on ch.CartonId=em.CartonId and ch.OrderNumber = " + "'" + OrderNumber + "'";
SqlCmd.CommandText = sqlStmt;

SqlCommand.CommandTimeout = TimeoutPeriod();
SqlTransaction transaction;

// Start a local transaction.
transaction = SqlCon.BeginTransaction(IsolationLevel.ReadCommitted);

// Must assign both transaction object and connection
// to Command object for a pending local transaction
SqlCommand.Connection = SqlCon;
SqlCommand.Transaction = transaction;

try
{

   int val = SqlCommand.ExecuteNonQuery();

   // Attempt to commit the transaction.
   transaction.Commit();
   return true;
}
catch (Exception ex)
{
   throw ex;
}
finally
{
   CloseConnection(SqlCon);
}

do you know what a deadlock is and why it occurs? There are many ways to resolve the issue but all of them are bespoke — BossRoss, Jul 16 '18 at 09:07
You can refer this post :- [DeadLock](https://stackoverflow.com/questions/51324511/sql-server-are-transaction-locking-table-for-other-users/51324881#51324881) — Deepak Kumar, Jul 16 '18 at 09:09
@BossRoss while threading same time all thread are request to particular table then deadlock occurs right? — jose, Jul 16 '18 at 09:09
This is a tricky problem to solve, you need to first work out what queries transactions are locking to work out what you can do about it — TheGeneral, Jul 16 '18 at 09:10
@TheGeneral delete orders based ordernumber simple delete query with where condition! — jose, Jul 16 '18 at 09:11
@TheGeneral in loop i delete some data from table based ordenumber tha's all otherwise no other big code the only thing is thread. help!! — jose, Jul 16 '18 at 09:15
If your code is simple, please paste it so we can understand what you are doing — TheGeneral, Jul 16 '18 at 09:20
Reproduce problem and execute query 'select * from sys.dm_exec_request cross apply sys.dm_exec_sql_text(sql_handle)` and check columns blocked resourse or wait_type and last_wait_type. What is written there? — Meow Meow, Jul 16 '18 at 09:51
1. You may want to remove the transaction from your code. It's one statement so it's redundant. 2. Where does `OrderNumber` come from? Does it come from the database? I guess you are running this again and again with multiple threads. 3. My suspicion is that you are writing a slow C# client side app to do what you could do more simply in a straightforward t-sql script. There are many sample scripts for deleting in batches, if that is what you're trying to do. — Nick.Mc, Jul 16 '18 at 10:29
OrderNumber from database @Nick.McDermaid yes iam run in multithreading — jose, Jul 16 '18 at 10:39
I'll make some guesses. Your C# code is reading order numbers from the database, possibly inside a transaction. Then it's farming individual deletes out to many threads to delete records one at a time. What is the real objective here? Again you could probably write in T-SQL and have it run faster without deadlocks. — Nick.Mc, Jul 16 '18 at 10:59
I think this is an XY problem. You are trying to solve some other problem but your solution has problems. Don't solve an unsuitable solutions problems. Solve the real problem. Is the _real_ problem that you are trying to delete quickly? Don't do it this way. https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem — Nick.Mc, Jul 17 '18 at 02:41

score 0 · Answer 1 · answered Jul 16 '18 at 10:00

0

The READ_COMMITTED_SNAPSHOT database option will help avoid blocking/deadlocking with readers and writers but not writers and writers (concurrent DELETE statements).

Run this query below get recent deadlock details from the system_health trace:

WITH
      CurrentSystemHealthTraceFile AS (
        SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
        FROM sys.dm_xe_session_targets
        WHERE
            target_name = 'event_file'
            AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%\system[_]health%'
    )
    , AllSystemHealthFiles AS (
        SELECT 
            REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'\', REVERSE(FileName)), 255)) + N'system_health*.xel' AS FileNamePattern
        FROM CurrentSystemHealthTraceFile
        )
    , DeadLockReports AS (
        SELECT CAST(event_data AS xml) AS event_data
        FROM AllSystemHealthFiles
        CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS xed
        WHERE xed.object_name like 'xml_deadlock_report'
    )
SELECT TOP 10
      DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS LocalTime
    , event_data AS DeadlockReport
FROM DeadLockReports;

One can often avoid deadlocks is with query and index tuning so that only those rows needed for the task are touched. In this case, an index on Order.CartonID and a composite (ideally unique) index on OrderHeader(OrderID, CartonID) look to be useful.

Also, parameterize your query with the OrderID parameter type matching the column data type.

answered Jul 16 '18 at 10:00

Dan Guzman

43,250
3
46
71

READ_COMMITTED_SNAPSHOT not Working for some times again i face deadlock issuse @Dan Guzam – jose Jul 16 '18 at 10:19
@jose, I said READ_COMMITTED_SNAPSHOT **will not** help prevent deadlocking with concurrent DELETE statements. Have you run the query in my answer? Have your reviewed the indexes? – Dan Guzman Jul 16 '18 at 10:28
yes Run this query in my side and get deadlockreport in xml – jose Jul 16 '18 at 10:48
@jose, add an XML deadlock report to your question. – Dan Guzman Jul 16 '18 at 10:55
its too large @Dan – jose Jul 16 '18 at 11:09
Do you mean a single deadlock XML is too large? Only one is needed (assuming the are similar), not all of the ones in the trace. – Dan Guzman Jul 16 '18 at 11:14
i am not able to paste that code it's shows too large character count @Dan – jose Jul 16 '18 at 11:20
IF the XML is under 512K, you can use https://pastebin.com/ and add the link to your question. – Dan Guzman Jul 16 '18 at 11:27
@jose, this deadlock is unrelated to the delete queries in your question. Find one that has the delete statement(s) involved. – Dan Guzman Jul 16 '18 at 11:43

Deadlocked on lock resources in sql

1 Answers1