February 7, 2012 at 8:07 am
Hi,
Currently I'm facing an issue in a SQL Server retrieval procedure.
Stored procedure returns unexpected result when executed by many user (No. of users > 40) concurrently for the same parameter. For No of users <= 20 it shows expected result. Error rate is 0.07-0.10 % measured using jMeter.
The following SQL statement is expected to return one row. But under heavy load it sometimes returns no rows.
exec RetrievingNode @Keys='one,two,three'
CREATE PROCEDURE [dbo].[RetrievingNode]
@Keys nvarchar(max)
as
Begin
SET NOCOUNT ON;
--***************************************
--***************************************
-- Turn On or Off updation of LastAccessDateTime
-- To Turn on set @TurnOnUpdation=1
-- To Turn Off set @TurnOnUpdation=0
declare @TurnOnUpdation as bit
set @TurnOnUpdation=1
--***************************************
--***************************************
declare @Variable as table(Value nvarchar(max),KeyedNodes_Id int,KeyNodeValue nvarchar(max),IsParent bit, IsReadOnly bit )-- this table will hold ids against which LastAccessDateTime can be updated.
declare @tbl_Keys table(id int identity(1,1),Value nvarchar(max))
--following will take all the keys into @tbl_Keys
insert into @tbl_Keys(Value)select Data from Split(@Keys,',')
DECLARE @count as int
DECLARE @counter as int
SET @counter=1
SELECT @count=COUNT(*) FROM @tbl_Keys
--Declare @ChildId as int
declare @ChildNodes table(id int,Value nvarchar(max),ParentNode_id int)
declare @ParentId as int
WHILE(@counter<=@count)
BEGIN
DECLARE @key as nvarchar(max)
SELECT @key=Value FROM @tbl_Keys WHERE id=@counter
if(@counter=1)
begin
if not exists(select * from keyednodes(nolock) KN where KN.Value=@Key and KN.ParentNode_Id is null)
BEGIN
---node does not exists
Break;
END
declare @ValueExistsForFirstKey as bit
set @ValueExistsForFirstKey=0
insert into @ChildNodes
select k2.Id,k2.Value,k2.ParentNode_Id from keyednodes(nolock) k1 inner join keyednodes(nolock) k2 on k1.id=k2.ParentNode_id where K1.value=@Key and k1.ParentNode_Id is null
if(@count=1)
BEGIN
IF EXISTS(
select GV.Value,GV.KeyedNodes_Id,KN.Value from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Value=@Key and KN.ParentNode_Id is null
)
BEGIN -- If value of node exists
SET @ValueExistsForFirstKey=1
insert into @Variable
select GV.Value,GV.KeyedNodes_Id,KN.Value,1,GV.ReadOnly from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Value=@Key and KN.ParentNode_Id is null
END
ELSE IF NOT EXISTS(select * from @ChildNodes)
BEGIN
insert into @Variable select -1,-1,-1,0,0 --Node exists but No value or children exists
GOTO ExitFromProcedure
END
IF(@counter=@count)
BEGIN
if exists(select Id,Value,ParentNode_Id from keyednodes(nolock) KN where KN.Value=@Key and KN.ParentNode_Id is null)
BEGIN
if(@ValueExistsForFirstKey=0)and (not exists(select * from @ChildNodes))
BEGIN
insert into @Variable select -1,-1,-1,0,0
GOTO ExitFromProcedure
END
END
END
END
end
else
begin
declare @KeyIsChild as int
set @KeyIsChild=0
if exists(select * from @ChildNodes where Value=@Key)
begin
set @KeyIsChild=1
End
ELSE
BEGIN
--Key path does not exist
--print 'key path does not exist'
GOTO ExitFromProcedure
ENd
if not exists(select * from @ChildNodes where Value=@Key)
BEGIN
set @ParentId=0
END
ELSE
BEGIN
select @ParentId=Id from @ChildNodes where Value=@Key
ENd
delete @ChildNodes
if(@KeyIsChild=1)
begin
insert into @ChildNodes
select k2.Id,k2.Value,k2.ParentNode_Id from keyednodes(nolock) k1 inner join keyednodes(nolock) k2 on k1.id=k2.ParentNode_id where k2.ParentNode_Id=@ParentId
end
end
SET @counter=@counter+1
END
if exists(select * from @ChildNodes)
begin
IF EXISTS
(
select GV.Value,CN.id,CN.Value from @ChildNodes CN left outer join GlobalVariables(nolock) GV on CN.Id=GV.KeyedNodes_Id --children
union all
select GV.Value,GV.KeyedNodes_Id,KN.Value from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId--children
)
BEGIN
insert into @Variable
select GV.Value,CN.id,CN.Value,0,GV.ReadOnly from @ChildNodes CN left outer join GlobalVariables(nolock) GV on CN.Id=GV.KeyedNodes_Id --children
union all
select GV.Value,GV.KeyedNodes_Id,KN.Value,1,GV.ReadOnly from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId--children
END
ELSE
BEGIN
if(@count <>1)
insert into @Variable select -1,-1,-1,0,0
if(@count=1) and not exists(select * from @Variable)
insert into @Variable select -1,-1,-1,0,0
END
end
else
Begin
if (@KeyIsChild=1)
BEGIN
--select @ParentId
--select * from @Variable
insert into @Variable
exec [RetrievingNode_A] @ParentId
--IF EXISTS
--(
--select GV.Value,GV.KeyedNodes_Id,KN.Value from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
--on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId
--)
--BEGIN
-----IF value of the node exists
--insert into @Variable
--select GV.Value,GV.KeyedNodes_Id,KN.Value,1,GV.ReadOnly from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
--on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId --node
--END
--ELSE
--BEGIN
--insert into @Variable select -1,-1,-1,0,0 --Node exists but No value or children exists
--END
END
End
----
ExitFromProcedure:
select KeyedNodes_Id,KeyNodeValue,Value,IsParent,Isnull(IsReadOnly,0)as IsReadOnly from @Variable order by IsParent desc, KeyNodeValue asc
--update LastAccessDateTime
IF(@TurnOnUpdation=1)
BEGIN
IF EXISTS(select * from @Variable)
BEGIN
UPDATE GlobalVariables SET LastAccessDateTime=getdate()
WHERE KeyedNodes_Id IN (SELECT KeyedNodes_Id FROM @Variable)
END
END
End
Go
CREATE FUNCTION [dbo].[Split]
(
@RowData nvarchar(max),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(max)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
could any one help on this ?
Thanks
Harish
February 7, 2012 at 8:13 am
What is your code trying to achieve? Any heads and tails information would be nice for other people to help answer your question.
February 7, 2012 at 8:17 am
"NOLOCK" hint is the culprit here.
February 7, 2012 at 8:20 am
Thanks.
exec RetrievingNode @Keys='one,two,three'
This stored proc takes a key path and returns node as well its children.
here are the database table
CREATE TABLE [dbo].[KeyedNodes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Value] [nvarchar](max) NOT NULL,
[FullKeyPath] [nvarchar](max) NOT NULL,
[ParentNode_Id] [int] NULL,
CONSTRAINT [PK_KeyedNodes] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[GlobalVariables] Script Date: 02/07/2012 15:47:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GlobalVariables](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Value] [nvarchar](max) NOT NULL,
[ReadOnly] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[LastAccessDateTime] [datetime] NOT NULL,
[ModifiedDateTime] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](max) NOT NULL,
[KeyedNodes_Id] [int] NOT NULL,
CONSTRAINT [PK_GlobalVariables] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks,
Harish
February 7, 2012 at 8:20 am
Per BOL:
READ UNCOMMITTED
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
More: http://msdn.microsoft.com/en-us/library/aa259216%28v=sql.80%29.aspx
February 7, 2012 at 8:37 am
Hi Dev,
I checked by removing "NOLOCK". error still there. issue seems to be somewhere else.
Thanks
Harish
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply