I am working on a data entry panel, there more than 300 clients work simultaneously with updating data in a data table, I use a stored procedure to perform an update in a data table, as follows.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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;
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 gets a deadlock like this ...
transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. rerun the transaction.
I am creating a backup module for searching error logs, which shows that this stored procedure is getting a dead end, while more clients are also trying to update the data.
i also added SET TRANSACTION ISOLATION LEVEL READ COMMITTED
but this does not work for me .. can someone give a solution to resolve this situation.
or if there is some kind of mechanism that executes the execution process until the previous execution completes