November 21, 2008 at 12:57 pm
I am having a huge problem with my stored procedure which is cozing a table locking and I can't figure out how to resolve it.
Here is some additional details:
We have processing business application, which is using SQL 2005 table as a persisting point of all new activity posted for processing. Another application (BizTalk) is responsible for processing all new requests. So, to discover new requests(marked as StatusCode = 's'), BizTalk is running stored procedure "pOgGetActivityReqInfo" wich is returning a single row of data (xml in this case) and at the same time updating the source table, so the same record would not be processed twice.
Here is stored proc script:
ALTER PROCEDURE [dbo].[pOgGetActivityReqInfo]
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @TopRunID int
SET @TopRunID = (select TOP 1 RunID
from ActivityRunArchive WITH (NOLOCK)
where StatusCode = 's')
UPDATE ActivityRunArchive SET StatusCode = 'p' WHERE RunID = @TopRunID
SELECT RunID, XMLRequest FROM ActivityRunArchive WITH (NOLOCK) WHERE RunID = @TopRunID
FOR XML RAW('ActivityRun')
November 22, 2008 at 10:53 am
The only part of that proc that should cause a lock (to the best of my knowledge anyways) is the update statement. Even then, that should be a row level lock for the update. Is RunID unique... or is it possibly the same for all the rows in the table?
November 24, 2008 at 6:44 am
I have the same filling about "Update" statement - this is a source of a problem.
RunID is "Identity":
CREATE TABLE [dbo].[ActivityRunArchive](
[MessageID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RunID] [int] IDENTITY(1,1) NOT NULL,
[ActivityID] [int] NOT NULL,
[Source] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusCode] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EndDateTime] [datetime] NULL,
[StartDateTime] [datetime] NULL,
[NumberOfRecords] [int] NULL,
[XMLRequest] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Note] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequestUserID] [int] NULL,
[Ignore] [bit] NOT NULL DEFAULT ((0))
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I modified stored proc to the next version last week and still having a problems:
ALTER PROCEDURE [dbo].[pOgGetActivityReqInfoNew]
AS
DECLARE @TopRunID int
SET @TopRunID = (select TOP 1 RunID
from ActivityRunArchive WITH (NOLOCK)
where StatusCode = 's')
IF @TopRunID is not null
BEGIN
BEGIN TRAN
UPDATE ActivityRunArchive SET StatusCode = 'p' WHERE RunID = @TopRunID
SELECT RunID, XMLRequest FROM ActivityRunArchive WITH (NOLOCK) WHERE RunID = @TopRunID
FOR XML RAW('ActivityRun')
COMMIT TRAN
END
November 25, 2008 at 8:18 am
astar (11/21/2008)
I am having a huge problem with my stored procedure which is cozing a table locking and I can't figure out how to resolve it.Here is some additional details:
We have processing business application, which is using SQL 2005 table as a persisting point of all new activity posted for processing. Another application (BizTalk) is responsible for processing all new requests. So, to discover new requests(marked as StatusCode = 's'), BizTalk is running stored procedure "pOgGetActivityReqInfo" wich is returning a single row of data (xml in this case) and at the same time updating the source table, so the same record would not be processed twice.
Here is stored proc script:
ALTER PROCEDURE [dbo].[pOgGetActivityReqInfo]
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @TopRunID int
SET @TopRunID = (select TOP 1 RunID
from ActivityRunArchive WITH (NOLOCK)
where StatusCode = 's')
UPDATE ActivityRunArchive SET StatusCode = 'p' WHERE RunID = @TopRunID
SELECT RunID, XMLRequest FROM ActivityRunArchive WITH (NOLOCK) WHERE RunID = @TopRunID
FOR XML RAW('ActivityRun')
There are numerous problems here. The biggest one is concurrency. What happens when this gets run twice (or many times) at the exact same time? You will wind up grabbing the same runid for multiple executions and thus get problems in your application logic.
You need MORE locking control to stop that, not NOLOCKs.
I would try an index on statuscode, runid. Force a rowlock, holdlock on the select then do the update. I note you did not give us the table script so I have no idea about data types and existing indexes/keys. If runid isn't indexed, it should be as well (probably the PK, right?)
Also use begin tran before the select and check for errors and commit afterwards.
Probably better than select then update would be to use the OUTPUT clause with UPDATE TOP 1... This can be done in a single statement which is preferable. See here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 25, 2008 at 10:31 am
Thank you, problem is fixed!
As per your recommendations, I modified my stored proc., so it is now looks like this:
ALTER PROCEDURE [dbo].[pOgGetActivityReqInfo1]
AS
--create temp table to preserve result set of the update
DECLARE @VarActivityRun table(
[MessageID] [varchar](50),
[RunID] INT NOT NULL ,
[ActivityID] [int] NOT NULL,
[Source] [varchar](1000) ,
[StatusCode] [char](1) ,
[EndDateTime] [datetime] NULL,
[StartDateTime] [datetime] NULL,
[NumberOfRecords] [int] NULL,
[XMLRequest] [text] ,
[Note] [varchar](4000) ,
[RequestUserID] [int] NULL,
[Ignore] [bit] DEFAULT ((0))
)
UPDATE TOP (1) ActivityRun SET StatusCode = 'p'
OUTPUT INSERTED.* INTO @VarActivityRun
WHERE StatusCode = 's'
--Display the result set of the table
SELECT RunID, XMLRequest
FROM @VarActivityRun
FOR XML RAW('ActivityRun')
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply