Concurrency / LINQDataSource / FormView / Stored Procedures

[Jay Pondy]

VS2008 / SQL Server 2005 / Windows XP

I have been unable to generate a concurrency exception using a single table
in a DataContext.dbml with Insert / Update / Delete SPs via a LINQDataSource
and a FormView.

I am able to successfully add, update and delete rows but not generate a
concurrency exception.

After two days I'm feeling pretty darned stumped!! I can bundle the whole
thing up if anybody is up for taking a look.

Here is the SQL I am using:

CREATE TABLE [dbo].[Departments](
[PKID] [int] IDENTITY(1,1) NOT NULL,

 [varchar](10) NOT NULL,
	[Description] [varchar](50) NOT NULL,
	[TS] [timestamp] NOT NULL,
 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED 
(
	[PKID] 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

CREATE PROCEDURE [dbo].[zDepartmentInsert]

	@PKID INT OUTPUT,
	@Code VARCHAR(10),
	@Description VARCHAR(50),
	@TS TIMESTAMP OUTPUT

AS

	INSERT INTO [dbo].[Departments] (
		[Code],
		[Description]
	) VALUES (
		@Code,
		@Description)

	SELECT
		@PKID = SCOPE_IDENTITY(),
		@TS = TS
	FROM [dbo].[Departments]
	WHERE
		[PKID] = SCOPE_IDENTITY()
GO

CREATE PROCEDURE [dbo].[zDepartmentUpdate]

	@PKID int,
	@Code varchar(10),
	@Description varchar(50),
	@TS timestamp OUTPUT

AS

	UPDATE [dbo].[Departments] SET
		[Code] = @Code,
		[Description] = @Description	
	WHERE
		[PKID] = @PKID AND
		[TS] = @TS

	SELECT
		@TS = [TS]
	FROM [dbo].[Departments]
	WHERE
		[PKID] = @PKID	
GO

CREATE PROCEDURE [dbo].[zDepartmentDelete]

	@PKID int,
	@TS timestamp

AS

	DELETE FROM
		[dbo].[Departments]
	WHERE
		[PKID] = @PKID AND
		[TS] = @TS
GO

 

[Patrice]

What if you are using the same criteria in your SELECT than in your UPDATE.
IMO the problem is that the select always return a single row so it hides
the fact that the update statement didn't processed any row.

 

[Jay Pondy]

If I understand you correctly you are talking about the Update SP.

If I modify it so that it checks the @@RowCount to make sure the row was
updated before I grab the new TimeStamp it still does NOT detect a
concurrency problem.

IF @@RowCount = 1
SELECT
@TS = [TS]
FROM [dbo].[Departments]
WHERE
[PKID] = @PKID

If I add an ELSE statement and RAISERROR an exception does occur but as I
understand it the DataContext should be detecting the concurrency issue
without raising errors from the SPs.

What if you are using the same criteria in your SELECT than in your UPDATE.
IMO the problem is that the select always return a single row so it hides
the fact that the update statement didn't processed any row.

--
Patrice

VS2008 / SQL Server 2005 / Windows XP

I have been unable to generate a concurrency exception using a single
table
in a DataContext.dbml with Insert / Update / Delete SPs via a
LINQDataSource
and a FormView.

I am able to successfully add, update and delete rows but not generate a
concurrency exception.

After two days I'm feeling pretty darned stumped!! I can bundle the whole
thing up if anybody is up for taking a look.

Here is the SQL I am using:

CREATE TABLE [dbo].[Departments](
[PKID] [int] IDENTITY(1,1) NOT NULL,

 [varchar](10) NOT NULL,
[Description] [varchar](50) NOT NULL,
[TS] [timestamp] NOT NULL,
CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
(
[PKID] 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

CREATE PROCEDURE [dbo].[zDepartmentInsert]

@PKID INT OUTPUT,
@Code VARCHAR(10),
@Description VARCHAR(50),
@TS TIMESTAMP OUTPUT

AS

INSERT INTO [dbo].[Departments] (
[Code],
[Description]
) VALUES (
@Code,
@Description)

SELECT
@PKID = SCOPE_IDENTITY(),
@TS = TS
FROM [dbo].[Departments]
WHERE
[PKID] = SCOPE_IDENTITY()
GO

CREATE PROCEDURE [dbo].[zDepartmentUpdate]

@PKID int,
@Code varchar(10),
@Description varchar(50),
@TS timestamp OUTPUT

AS

UPDATE [dbo].[Departments] SET
[Code] = @Code,
[Description] = @Description
WHERE
[PKID] = @PKID AND
[TS] = @TS

SELECT
@TS = [TS]
FROM [dbo].[Departments]
WHERE
[PKID] = @PKID
GO

CREATE PROCEDURE [dbo].[zDepartmentDelete]

@PKID int,
@TS timestamp

AS

DELETE FROM
[dbo].[Departments]
WHERE
[PKID] = @PKID AND
[TS] = @TS
GO
[/QUOTE]

 

[Patrice]

Yes as an optimistic concurrency issue anyway relates to the update (??).

My approach would be :
- drop whatever doesn't pertain to concurrency including the select
statement
- I would even add a 1=0 criteria clause to create a no brainer concurrency
issue

From here, it should work then :
- add back the criteria (you'll have now to actually create a concurrency
issue)
- add back the select statement (likely *before* doing the update so that
this result doesn't mess the update statement)

For now the goal is to make 100% sure that the update statement is seen as
affecting no records... From there we should be able to see if this is how
it is handled or if some more work is needed...

You have also a linq forum at :
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=123&SiteID=1

Good luck.

 

[Jay Pondy]

Leaving only the update statement with a 1=0 criteria to force a concurrency
issue did not work. Same result - the formview appears as if the update was
made but no concurrency issue was raised.

 

[Patrice]

And you only have an update now in your proc ? According to
http://weblogs.asp.net/scottgu/arch...ing-our-database-using-stored-procedures.aspx
it looks like a simple update should do it...

I'll try to give this a shot when coming back from lunch (two hours from now
here) (please post here if meanwhile you found the solution)....

 

[Jay Pondy]

Yes - I've read that article about five times and gone over it with a fine
tooth comb and wish Scott had made good on his promise to follow up with an
article dedicated to concurrency issues. It'll be at least 5 or 6 hours
before I can get back on this problem - I appreciate your interest and help.

 

[Patrice]

It's look tougher than expected. My understanding for now is that the idea
is that this is no more a framework base feature (previously it was checking
affected rows) but that you have to handle this if you are customizing the
linq behavior (likely so that you have full control if needed about what is
done especially if you add aditional linq providers ?).

http://linqinaction.net/blogs/jwool...ures-with-linq-with-concurrency-checking.aspx
could be a good starting point (it looks like it shows how to plug your own
code so that you get the original/new values). Additionaly you would have
also to add adtional code so that if the sp signals a concurrency error (for
example ain a return value) you explicitely throw conflictexception (and
posisbly you would have also to list concurrency conflic details if you need
to provide this info further down)...

Sorry for the poor help but I thought it was much similar to how it was
previously done. Hoepfully somone who have gone throguh this will finally
popup...

 

[Jay Pondy]

The article you cited (on the beta2 version) mentions S Gutherie pointing
Wooley to a helper method on the table entity type to fetch the original
values. When I look at the code behind in DataContext.designer.cs the call
to the stored procedure method has the designer generated code Gutherie
mentions.

Based on your input I thought maybe a return value signals a concurrency
problem but varying the return made no difference. The designer generated
code actually does fetch what should be the new value for the time stamp and
returns it to the caller. When I do a normal update you can in fact see the
timestamp value change in the formview so it is being round tripped from the
stored procedure.

If I create the same simple project with a FormView and a LINQDataSource
bound to a table with an identity and timestamp and do NOT use stored
procedures and add the following code to the web form code behind:

protected void Page_Load(object sender, EventArgs e)
{
ds.Updated += new
EventHandler<LinqDataSourceStatusEventArgs>(ds_Updated);
}


protected void ds_Updated(object sender, LinqDataSourceStatusEventArgs e)
{
if (e.Exception != null && e.Exception is ChangeConflictException)
{
e.ExceptionHandled = true;
txtMessage.InnerHtml = e.Exception.Message;
}
}

and then open two browsers on the same record in edit mode to create a
concurrency fault the ChangeConflictException is in fact trapped. If I
examine the DataContext code behind there is designer generated code in there
to trap the concurrency or raise the exception which means this being handled
by the LINQ implementation itself.

Sign me

Still Puzzled...

 

[Jay Pondy]

From the book "LINQ in Action": "Additionally, we'll be responsible for
handling concurrency conflicts explicitly."

Once you start using stored procedures for updates and deletes all of those
nifty concurrency features found in the DataContext disappear!!!