CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduresdatabase-deadlocks
karan thakkar
karan thakkar

Reputation: 140

Deadlock in Update Stored Procedure

i am working on a data entry panel, there more than 300 clients working at a time with updating data on data table, i am using stored procedure to execute update in data table looks like this..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:                <Author,,Name>
-- Create date: <Create Date,,>
-- Description:        <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Update_Tbl_Client_Bpo_Data]

@Id int,
@CId varchar(50),
@Tbc_No varchar(200),
@Name varchar(200),
@EmailId varchar(200),
@MobileNo varchar(50),
@Gender varchar(50),
@LicenseNo varchar(200),
@GirNo varchar(200),
@PanNo varchar(200),
@H_Address varchar(500),
@H_City varchar(200),
@H_PinNo varchar(200),
@H_county varchar(200),
@H_State varchar(200),
@O_Address varchar(200),
@O_City varchar(200),
@O_PinNo varchar(200),
@LAL varchar(200),
@MRNNo varchar(200),
@AF varchar(200),
@NRI varchar(200),
@CP varchar(200),
@Status varchar(200)

AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;



        update Tbl_Client_Bpo_Data 
set

Tbc_No=@Tbc_No,
Name=@Name,
EmailId=@EmailId,
MobileNo=@MobileNo,
Gender=@Gender,
LicenseNo=@LicenseNo,
GirNo=@GirNo,
PanNo=@PanNo,
H_Address=@H_Address,
H_City=@H_City,
H_PinNo=@H_PinNo,
H_county=@H_county,
H_State=@H_State,
O_Address=@O_Address,
O_City=@O_City,
O_PinNo=@O_PinNo,
LAL=@LAL,
MRNNo=@MRNNo,
AF=@AF,
NRI=@NRI,
CP=@CP,
Status=@Status from  Tbl_Client_Bpo_Data  where Id=@Id and CId=@CId 
END

this Stored procedure get Deadlock Error like this...

transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. rerun the transaction.

i create a backup module to find error logs which displays that this stored procedure get deadlock while more clients also try to update data.. Error Logs

i also added SET TRANSACTION ISOLATION LEVEL READ COMMITTED

but it doesn't work for me.. can someone give an solution to resolve this situation..

or if there is some kind of mechanism that holds execution process until previous execution completes

Upvotes: 4

Views: 2997

Answers (1)

Erlis Vidal
Erlis Vidal

Reputation: 171

Short answer

You might be missing an index on your table. First you will have to check if your update is generating a table scan, if so create one index on ID and CID to see if that solves your problem.

Long answer

Update statement on your table can introduce table scan. It means, that SQL Server reads every row in your table to check if row needs to be updated. During that read operation SQL Server issues (U) update lock on the row. In case if row needs to be updated, it converts (U) lock to (X) exclusive lock and held that lock till end of transaction. (U) locks are incompatible with other (U) nor (X) locks.

In your case you have 2 sessions waiting for each other. Each session puts X lock on (different) rows and tries to issue (U) lock and read the row updated by other session (with (X) lock held). It's a bit more complicated actually - you have more than 2 sessions involved but I hope you get an idea.

Create the index to avoid table scans during update

Upvotes: 6

Related Questions