February 5, 2010 at 2:46 am
I'm having a particularly strange problem which I believe to be related to merge replication in SQLServer 2008. To be honest, I do not have a great deal of experience with replication. However, this problem seems bizarre, and I'm not sure how to proceed with resolving it.
The scenario is as follows: I have a merge replication configured between two servers for a table called "Part". I have a stored procedure defined on the publisher as follows:
CREATE PROCEDURE [dbo].[Ins_CreatePartFromDataStrings]
@ZoneIDInt,
@DataString1Varchar(20),
@DataString2Varchar(20),
@DataString3Varchar(20),
@DataString4Varchar(20),
@DataString5Varchar(20)
AS
DECLARE @QueueIDInt
DECLARE @VariantIDInt
DECLARE @ProductionIdentifier Varchar(50)
DECLARE @PartIDInt
DECLARE @ProductionIdentifierIDInt
SELECT @VariantID = VariantID
FROM Variant
WHERE ZoneID = @ZoneID
ANDIDString2 LIKE @DataString2
ANDIDString3 LIKE @DataString3
ANDIDString4 LIKE @DataString4
ANDIDString5 LIKE @DataString5
IF @VariantID IS NULL RETURN 0
SELECT @PartID = PartID
FROM Part
WHERE ProductionIdentifier = @DataString1
AND VariantID = @VariantID
IF @PartID IS NOT NULL AND @PartID > 0 RETURN @PartID
BEGIN TRANSACTION
EXECUTE @PartID = Ins_CreatePart @DataString1, @VariantID
IF @PartID <> 0
BEGIN
UPDATE Part
SETNextVariantID = NULL,
QtyRemainingToVariantChange = 0
WHERE PartID = @PartID
END
COMMIT TRANSACTION
RETURN @PartID
Basically, this SP checks the table for a row matching specific criteria, and if it finds one it returns the PK of that row, else it creates a new row and returns the PK of that row. Given the specific application, a new row should be created in about 98% of executions. Now, this SP works perfectly EXCEPT when I activate the merge replication. At that point, the query
SELECT @PartID = PartID
FROM Part
WHERE ProductionIdentifier = @DataString1
AND VariantID = @VariantID
near the beginning of the stored procedure begins intermittently returning PartIDs that exist in the table, but which do not correspond with rows that match the criteria in the WHERE clause. In fact, in each observed case where this has happened, the parameters passed to the stored procedure should have resulted in this query returning no matching rows.
What I suspect, but have not yet been able to prove: The frequency of the problem makes me think that the problem occurs specifically while the merge replication job is running. Also, when the invalid data is returned, the incorrect PK appears to always correspond to a row early in the table, ie: one of the first few hundred rows inserted into the table.
To emphasize this point, I have had the original database running for several months without any issues whatsoever. The problem only emerged when I added the replication, and immediately disappears when replication is disabled.
I'm not sure what other information regarding the problem would be helpful, but if someone has an idea what could be causing this, I'd be happy to provide any additional data required to pinpoint the issue.
February 5, 2010 at 3:32 am
Can you give us structure of 'Part' table?
Are below SPs are replicated
Ins_CreatePartFromDataStrings
Ins_CreatePart --- What does this SP doing?
February 5, 2010 at 5:43 am
Hi. Thanks for the quick response.
The stored procedures are not replicated. They reside on the publisher and are executed against the database on that server.
The Ins_CreatePart is fairly straightforward:
CREATE PROCEDURE [dbo].[Ins_CreatePart]
@ProductionIdentifierVarchar(50),
@VariantIDInt
AS
DECLARE @CountInt
SELECT @Count = Count(*)
FROM Part
WHERE ProductionIdentifier = @ProductionIdentifier
AND VariantID = @VariantID
IF @Count <> 0 RETURN -1
BEGIN TRANSACTION
INSERT
INTO Part(ProductionIdentifier, VariantID, Start)
VALUES(@ProductionIdentifier, @VariantID, GetDate())
COMMIT TRANSACTION
RETURN @@Identity
I acknowledge that this isn't particularly transaction-safe, but the environment is such that there is only one location from which this is executed, so I'm not concerned with concurrency. I did look at this, but it did not seem relevant given that the problem seems to be occurring much earlier in the stored procedure's execution.
Here's a script for the creation of the Part table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Part](
[PartID] [int] IDENTITY(2,1) NOT NULL,
[ProductionIdentifier] [varchar](50) NOT NULL,
[VariantID] [int] NOT NULL,
[NextVariantID] [int] NULL,
[QtyRemainingToVariantChange] [int] NOT NULL,
[Start] [datetime] NOT NULL,
[End] [datetime] NULL,
CONSTRAINT [PK_Part] PRIMARY KEY CLUSTERED
(
[PartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Part] WITH CHECK ADD CONSTRAINT [FK_Part_Variant] FOREIGN KEY([VariantID])
REFERENCES [dbo].[Variant] ([VariantID])
GO
ALTER TABLE [dbo].[Part] CHECK CONSTRAINT [FK_Part_Variant]
GO
ALTER TABLE [dbo].[Part] ADD CONSTRAINT [DF_Part_QtyRemainingToVariantChange] DEFAULT ((0)) FOR [QtyRemainingToVariantChange]
GO
That script is from before replication was configured, so it does not include the RowGUID that the replication wizard added, of course.
February 5, 2010 at 6:02 am
it looks like PartID is Identity column and is NOT NULL and PK also..
so below statement in your SP will always return some PartID depending upon where condition...and will always be true
IF @PartID IS NOT NULL AND @PartID > 0 RETURN @PartID
what is intention of above IF clause...?
If SPs are executed at publisher only then I guess Merge replication have no role to play in issue you are facing...
I guess you have this issue at publisher only..check if subscribers are not alterating the Part table in the way you dont wish...
If any of subscriber is updating the data in Part table...then look for how you are managing conflicts...
February 5, 2010 at 7:54 am
Hi
The query before the IF statement may return no rows. In fact, more often than not, it will not return any rows at all. When it does return no rows, @PartID will be null. If any rows are returned, it will not be null. This is the crux of the problem. When replication is running, @PartID is intermittently assigned a non-null value when there should be no rows matching the criteria.
While I agree with you that replication should have no effect on this at all, the evidence is very strong that it does, hence my post. When replication is not taking place, this stored procedure works perfectly according to expectations. In fact, it has been running on average every three minutes for 16 hours per day for nearly 6 months. The moment I enable replication, I begin to experience the behaviour indicated above.
Edit: The subscribers are not performing any updates against the table (although they may perform inserts). In fact, I am absolutely certain that the subscribers have executed no action queries against the table while this problem is occurring. I know this because I suspected some kind of clash, so I disabled all of the clients which access the subscriber table to test. At the point when this occurs, the only two processes performing action queries against this table are the stored procedure above and the replication.
February 7, 2010 at 8:52 pm
First - check whether you are getting conflicts. Depending on how you have chosen to resolve conflicts, replication will update records with the contents from another server (which sounds like what you are experiencing). The conflict resolver should have saved the data that was changed.
Second - how are you managing IDENTITY ranges on your tables on each server. In the CREATE Table for the Part table, you have specified "IDENTITY (2, 1)". To me, this suggests that you may have the same key ranges being used across all servers. This is going to cause problems (and may be implicated in the problem you are experiencing.
Third - for MERGE replication to operate, you must have a UNIQUEIDENTIFIER column specified. Your Part table does not have this. Is the create table statement you included in your post correct ? Try re-scripting the table from one of the databases that is being replicated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply