C# SQL Transaction (Process ID 72) was deadlocked

Question

I have created a WinForm in Visual Studio which multiple users will access. Whenever a user access the data I want to lock the data so that the other users cannot view the same data at the same time due to some business logic.

Below is my c# code

using (SqlConnection conn = new SqlConnection(connectString))
{
    using (SqlCommand cmd=new SqlCommand("get_First_Unscan_Record_withUpdate",conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@usrName", username_Form);
        conn.Open();
        record_number = cmd.ExecuteScalar().ToString();
    }
}

And My SQL Procedure

    CREATE PROCEDURE [dbo].[get_First_QC_Record_withUpdate]
@usrName nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

Declare @srno int

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

select top 1 @srno=srno from [KYC].[dbo].[KYC_Index] (XLOCK) where  [qc]='N' and [qc_int]='N' and transform_int='Y' and transform_status='Y' order by srno

update KYC_Index set [qc_int]='Y',[qc_int_by]=@usrName,[qc_int_time]=GETDATE(),@srno=KYC_Index.srno where srno = @srno

select @srno

Still my users are getting deadlock error

{"Transaction (Process ID 72) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."}

What am I doing wrong? Or should I change? Please guide.

This is not how you implement a queue. Have a single update top (1) with rowlock, holdlock, readpast, have it output the id, and have an index that covers all fields you are filtering on. — GSerg
You don't deadlock with one thing - you deadlock with two. You already have an XLOCK (personally I'd have gone with UPDLOCK), so it can't be simply a read lock trying to become an update lock. So: do you have a transaction here? there isn't one in your code, but there could be a TransactionScope in play which could add other locks to your context/SPID.... is there? It kinda feels like you could do all of this in one SQL operation, though ... maybe via an OUTPUT clause to get the srno? The first thing I'd try, though, is using UPDLOCK. Then I'd try re-writing as single SQL op. — Marc Gravell
@GSerg Are you suggesting something like this? update t1 set [qc_int]='Y',[qc_int_by]=@usrName,[qc_int_time]=GETDATE(),@srno=t1.srno from KYC_Index t1 with (UPDLOCK,HOLDLOCK) where t1.srno = (select top 1 t2.srno from KYC_Index t2 where t2.[qc]='N' and t2.[qc_int]='N' and t2.transform_int='Y' and t2.transform_status='Y' order by t2.srno) — Danish Khan
@GSerg We have requirement to update the data in SRNO order. Thatswhy two queries. Can you please direct me the right direction in the above scenario. — Danish Khan

i486 i486 · Accepted Answer · 2017-07-18T09:52:35

You cannot hide the data by locking it. To hide from other users, there must be column like hidden in the same table or better in externally linked table like hidden_rows. SERIALIZABLE level is also not necessary. You need only BEGIN TRAN / COMMIT TRAN or you can do the same from C# side (begin_tran - try { do_work, commit_tran } catch { rollback }).

https://msdn.microsoft.com/en-us/library/86773566(v=vs.110).aspx

In SELECT statement use (UPDLOCK) hint - it will lock the row for writing until end of transaction and it will be readable for others.

C# SQL Transaction (Process ID 72) was deadlocked

1 Answers