Deadlock in logs

Phzyx · 03/22/2019, 14:12

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

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

elitonfarol9 · 03/22/2019, 14:37

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 · 03/22/2019, 15:11

Quote:

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