Deadlock in logs

[Phzyx]

Can't seem to figure out why this issue is happening which is causing a mass dc/rollback at random times. Here are the logs

 Spoiler

2019-03-22 06:30:54 ### DB_SYSTEM_CONNECT[5444] :Game[1][0x00FB4030], Name=Game01, Addr=127.0.0.1

2019-03-22 13:32:05 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) 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)

2019-03-22 13:32:05 ::SaveCharacter 804 Haku ret=1, qerr=-1, {?=call usp_Save_Char_Info_E(804,70,0,9554,13,631,12,7,10, 72,332,155,252,42,0,1356859358,9898000,'58.154999' ,'1.859000','58.180000', 0,0,0,0,0, 0,0,0,0, 0,0)}

2019-03-22 13:32:12 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 64) 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)

2019-03-22 13:32:12 ::SaveCharacter 796 defendmiloli ret=1, qerr=-1, {?=call usp_Save_Char_Info_E(796,70,0,9275,7,9,9,698,14,9, 16416,15693,8094,42,1,1356859358,47652935,'57.6432 69','1.851179','48.723354', 0,0,0,0,0, 82,1,0,0, 2,1)}

2019-03-22 13:32:13 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) 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)

2019-03-22 13:32:13 ::SaveCharacter 804 Haku ret=1, qerr=-1, {?=call usp_Save_Char_Info_E(804,70,0,9554,13,631,12,7,10, 72,332,155,252,42,0,1356859358,9898000,'58.154999' ,'1.859000','58.180000', 0,0,0,0,0, 0,0,0,0, 0,0)}

2019-03-22 13:32:14 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 83) 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)

2019-03-22 13:32:14 ::SaveCharacter 855 Cure ret=1, qerr=-1, {?=call usp_Save_Char_Info_E(855,70,621,9354,10,78,9,9,10, 12,300,145,305,42,0,1356859358,9661480,'58.154999' ,'1.859000','58.180000', 0,0,0,0,0, 0,0,0,0, 0,0)}

 Spoiler

2019-03-22 13:32:59 discon client: (104, 1236) T=000:29:00:000, RC=1628,RCS=0, RB=21001,RBS=11, SC=76922,SCS=43, SB=1476687,SBS=834

2019-03-22 13:33:00 discon client: (104, 1236) T=002:26:00:000, RC=9779,RCS=1, RB=131706,RBS=15, SC=100347,SCS=11, SB=1924076,SBS=219

2019-03-22 13:33:00 discon client: (104, 1236) T=003:27:00:000, RC=19138,RCS=1, RB=210043,RBS=16, SC=681885,SCS=54, SB=12701014,SBS=1020

2019-03-22 13:33:00 discon client: (104, 1236) T=001:09:00:000, RC=6531,RCS=1, RB=86258,RBS=20, SC=37594,SCS=8, SB=752382,SBS=179

2019-03-22 13:33:00 discon client: (104, 1236) T=001:07:00:000, RC=5049,RCS=1, RB=67177,RBS=16, SC=93595,SCS=23, SB=1816483,SBS=446

Any help at this point would be appreciated and yes ive looked on epvp for fixes and none of them are working

[elitonfarol9]

Try this :


USE [PS_GameData]
GO

/****** Object: StoredProcedure [dbo].[usp_Save_Char_Info_E] Script Date: 03/22/2019 10:40:12 ******/
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 real,
@Posy real,
@Posz real,
@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 @ID int
SET @ID = 1

UPDATE Chars
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()
WHERE CharID = @CharID

IF( (@K1 IS NOT NULL) AND (@K2 IS NOT NULL) AND (@K3 IS NOT NULL) AND (@K4 IS NOT NULL))
BEGIN
UPDATE Chars SET K1=@K1, K2=@K2, K3=@K3, K4=@K4 WHERE CharID=@CharID
END

--EXEC PS_USERDB01.PS_UserData_IDC_T3.dbo.usp_Update_Char sPVPData_E @ID = @ID, @CharID = @CharID, @K1 = @K1, @K2 = @K2, @K3 = @K3, @K4 = @K4

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

SET NOCOUNT OFF


GO

[Phzyx]

Originally Posted by elitonfarol9

Try this :


USE [PS_GameData]
GO

/****** Object: StoredProcedure [dbo].[usp_Save_Char_Info_E] Script Date: 03/22/2019 10:40:12 ******/
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 real,
@Posy real,
@Posz real,
@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 @ID int
SET @ID = 1

UPDATE Chars
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()
WHERE CharID = @CharID

IF( (@K1 IS NOT NULL) AND (@K2 IS NOT NULL) AND (@K3 IS NOT NULL) AND (@K4 IS NOT NULL))
BEGIN
UPDATE Chars SET K1=@K1, K2=@K2, K3=@K3, K4=@K4 WHERE CharID=@CharID
END

--EXEC PS_USERDB01.PS_UserData_IDC_T3.dbo.usp_Update_Char sPVPData_E @ID = @ID, @CharID = @CharID, @K1 = @K1, @K2 = @K2, @K3 = @K3, @K4 = @K4

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

SET NOCOUNT OFF


GO

We tried this originally and thought the issue has been resolved, but sadly it wasnt. So we put in a new procedure then the error just move else where. So we are currently trying a whole new db to see if that could resolve it. Thanks though <3