|
You last visited: Today at 18:05
Advertisement
deadlocked problem
Discussion on deadlocked problem within the Shaiya Private Server forum part of the Shaiya category.
09/30/2016, 02:18
|
#1
|
elite*gold: 0
Join Date: Jul 2011
Posts: 60
Received Thanks: 16
|
deadlocked problem
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
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
|
|
|
09/30/2016, 23:17
|
#2
|
elite*gold: 0
Join Date: Oct 2015
Posts: 156
Received Thanks: 54
|
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 :
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
|
|
|
10/02/2016, 17:14
|
#3
|
elite*gold: 0
Join Date: Jul 2011
Posts: 60
Received Thanks: 16
|
Rest
I do not know if you read it already resolve
but thank you all so
Quote:
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 :
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
|
|
|
|
All times are GMT +1. The time now is 18:05.
|
|