deadlocked problem

[GkRock]

I'm having trouble with one of my deadlocked on procedure
is complicated arrangement?

PS_DBAGENT__system.log__20160929_150107.log

It is causing rollback once every maintenance

 Spoiler

2016-09-29 17:08:50 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction., SQL STATE: 40001, NATIVE ERROR: 1205 (0x4B5)

2016-09-29 17:08:50 ::SaveCharacter 1535 Nsme ret=-1, qerr=-1, {?=call Usp_Save_Char_Item_Add_E(1535,4790440332494045184, 2,0, 30227,30,227, 0, 0,0,0,0,0,0,'', 1, '2016-09-29 17:00:28', 'X')}

resolved

[ShaiyaOld]

GkRock , If you search well you will find the solution.
It happens to me a few weeks ago and i got it fixed .
I would post here the solution if i was remmber what it was but.
Its some procedure that need little modification
Maybe its Usp_Save_Char_Item_Add_E but i am not sure.

##here's the FIX :

 Spoiler

USE [PS_GameData]
GO
/****** Object: StoredProcedure [dbo].[usp_Save_Char_Info_E] Script Date: 07/18/2014 15:10:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Proc [dbo].[usp_Save_Char_Info_E]

@CharID int,
@Level int, @Point smallint, @ smallint, @ smallint,
@Dex smallint,
@Rec smallint,
@Int smallint, @ smallint,
@Luc smallint,
@Hp smallint,
@Mp smallint,
@Sp smallint,
@Map smallint,
@Dir smallint,
@Exp int,
@Money int,
@Posx varchar(50),
@Posy varchar(50),
@Posz varchar(50),
@Hg int,
@Vg int,
@Cg int,
@Og int,
@Ig int,
@K1 int = null,
@K2 int = null,
@K3 int = null,
@K4 int = null,
@KillLevel tinyint,
@DeadLevel tinyint

AS

SET NOCOUNT ON

DECLARE @ServerID int,
@Grow int, @p bigint,
@PosxR real,
@PosyR real,
@PoszR real

SET @ServerID = 1

IF (ISNumeric(@Posx) = 1 and ISNumeric(@Posy) = 1 and ISNumeric(@Posz) = 1)
BEGIN
SET @PosxR = CAST(@Posx as real)
SET @PosyR = CAST(@Posy as real)
SET @PoszR = CAST(@Posz as real)
END
ELSE
BEGIN

SET @Map = 42
SET @PosxR = 66.5
SET @PosYR = 2.0
SET @PosZR = 52.6
END

UPDATE Chars WITH(XLOCK)
SET [Level] = @Level, StatPoint = @Point, SkillPoint = @,
[Str] = @, dex = @Dex, Rec = @Rec, [int] = @Int, Wis = @, Luc = @Luc,
HP = @Hp, Mp = @Mp, Sp = @Sp,
Map = @Map, dir = @Dir, [exp] = @Exp, [money] = @Money,
PosX = @Posx, PosY = @PosY, PosZ = @PosZ, hg = @Hg, vg = @Vg, cg = @Cg, og = @Og, ig = @Ig,
KillLevel=@KillLevel, DeadLevel=@DeadLevel,LeaveDate=GETDATE(),
K1 = CASE WHEN @K1 IS NOT NULL THEN @K1 ELSE K1 END,
K2 = CASE WHEN @K2 IS NOT NULL THEN @K2 ELSE K2 END,
K3 = CASE WHEN @K3 IS NOT NULL THEN @K3 ELSE K3 END,
K4 = CASE WHEN @K4 IS NOT NULL THEN @K4 ELSE K4 END
WHERE CharID = @CharID

IF(@@ERROR = 0 AND @@ROWCOUNT = 1)
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN -1
END

SET NOCOUNT OFF

[GkRock]

I do not know if you read it already resolve

but thank you all so

Originally Posted by ShaiyaOld

GkRock , If you search well you will find the solution.
It happens to me a few weeks ago and i got it fixed .
I would post here the solution if i was remmber what it was but.
Its some procedure that need little modification
Maybe its Usp_Save_Char_Item_Add_E but i am not sure.

##here's the FIX :

 Spoiler

USE [PS_GameData]
GO
/****** Object: StoredProcedure [dbo].[usp_Save_Char_Info_E] Script Date: 07/18/2014 15:10:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Proc [dbo].[usp_Save_Char_Info_E]

@CharID int,
@Level int, @Point smallint, @ smallint, @ smallint,
@Dex smallint,
@Rec smallint,
@Int smallint, @ smallint,
@Luc smallint,
@Hp smallint,
@Mp smallint,
@Sp smallint,
@Map smallint,
@Dir smallint,
@Exp int,
@Money int,
@Posx varchar(50),
@Posy varchar(50),
@Posz varchar(50),
@Hg int,
@Vg int,
@Cg int,
@Og int,
@Ig int,
@K1 int = null,
@K2 int = null,
@K3 int = null,
@K4 int = null,
@KillLevel tinyint,
@DeadLevel tinyint

AS

SET NOCOUNT ON

DECLARE @ServerID int,
@Grow int, @p bigint,
@PosxR real,
@PosyR real,
@PoszR real

SET @ServerID = 1

IF (ISNumeric(@Posx) = 1 and ISNumeric(@Posy) = 1 and ISNumeric(@Posz) = 1)
BEGIN
SET @PosxR = CAST(@Posx as real)
SET @PosyR = CAST(@Posy as real)
SET @PoszR = CAST(@Posz as real)
END
ELSE
BEGIN

SET @Map = 42
SET @PosxR = 66.5
SET @PosYR = 2.0
SET @PosZR = 52.6
END

UPDATE Chars WITH(XLOCK)
SET [Level] = @Level, StatPoint = @Point, SkillPoint = @,
[Str] = @, dex = @Dex, Rec = @Rec, [int] = @Int, Wis = @, Luc = @Luc,
HP = @Hp, Mp = @Mp, Sp = @Sp,
Map = @Map, dir = @Dir, [exp] = @Exp, [money] = @Money,
PosX = @Posx, PosY = @PosY, PosZ = @PosZ, hg = @Hg, vg = @Vg, cg = @Cg, og = @Og, ig = @Ig,
KillLevel=@KillLevel, DeadLevel=@DeadLevel,LeaveDate=GETDATE(),
K1 = CASE WHEN @K1 IS NOT NULL THEN @K1 ELSE K1 END,
K2 = CASE WHEN @K2 IS NOT NULL THEN @K2 ELSE K2 END,
K3 = CASE WHEN @K3 IS NOT NULL THEN @K3 ELSE K3 END,
K4 = CASE WHEN @K4 IS NOT NULL THEN @K4 ELSE K4 END
WHERE CharID = @CharID

IF(@@ERROR = 0 AND @@ROWCOUNT = 1)
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN -1
END

SET NOCOUNT OFF