StrataFrame Forum

Views & BO Updates with Concurrency

http://forum.strataframe.net/Topic12733.aspx

By Crones - 11/15/2007

I have a BO mapped to a view that joins fields from 2 tables in a 1-1 relationship.  I've created stored procs to handle the inserts and updates and these work ok except for when it comes to concurrency.  My BO always returns "success" when saving when there is a concurrency issue on the main table and I'm not sure how to catch this.

Here's how my procedure logic is laid out.

UPDATE Table1 SET RowVersion = (RowVersion + 1) ... WHERE PK=@Table1PK AND ((RowVersion = @RowVersion) OR (@RowVersion IS NULL))
SELECT @RowVersion = RowVersion FROM Table1 WHERE PK=@Table1PK;
UPDATE Table2 SET ... WHERE Table1FK=@PK

When there are no concurrency issues, everything works great.
However, if there is a problem during the table updates I do not receive an error.

I think this is more of a SQL Stored Procedure issue than anything having to deal with the framework but either way I'm not sure how to handle this. Any ideas?

TIA

By StrataFrame Team - 11/19/2007

Ah, the issue is that the BO checks on the number of records modified (the ROWCOUNT in the sproc), so since you're always updating table 2 (not checking on the concurrency in an IF) then the ROWCOUNT will always be greater than 0.

Your best bet is to start a transaction and do something like this:

-- Start a transaction so you can modify both tables without issues
BEGIN TRANSACTION
-- Declare a temp variable to grab the row version from table1
DECLARE @TEMPVERSION
-- Retrieve the row version
SELECT @TEMPVERSION = RowVersion FROM Table1
-- Check the row version to make sure that there isn't a concurrency issue
IF @TEMPVERSION = @RowVersion THEN
-- There isn't a concurrency issue, so update the tables
BEGIN
-- Update Table1
-- Update Table2
-- Retrieve the @RowVersion to return it
-- Committ the transaction because everything worked as expected
COMMITT
END
ELSE
BEGIN
-- Rollback the transaction because there was a concurrency error
-- The ROWCOUNT will automatically return 0 because no rows were
-- updated and it will be detected by the business object as a
-- concurrency error
ROLLBACK
END

I'm not positive on all of the syntax, but it might get you started in the right direction.

By Crones - 11/19/2007

Thanks a bunch for the help.  That got me started in the right direction.
By StrataFrame Team - 11/19/2007

Good to hear.  Let me know if you get stuck on anything else with those sprocs.