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

 

[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]

 

[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.

 

[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.

 

[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!!!