c# - Tables for content versioning getting out of sync -

c# - Tables for content versioning getting out of sync -

i have implemented content versioning 2 tables. understand maybe not optimal implementation, it's i've got work with.

one contents, has int id , other fields title, htmlcontent, etc.

the other contentversions. has foreign key content id , version field represents content revision , iscurrent flag represents version current 1 time (of re-create exists in contents table). has other fields title, htmlcontent, etc.

when user updates content, scheme supposed create new contentversion , update corresponding content. using entity framework , looks kinda this:

// asp.net mvc action [httppost] public jsonresult save(contentversion contentversion) { using (var scope = new transactionscope()) { seek { var versions = contentversionrepository.getbyid(contentversion.id) .orderbydescending(v => v.version) .firstordefault(); if (versions == null) contentversion.version = 1; else contentversion.version = versions.version + 1; contentversion.iscurrent = false; contentversion.lastmodified = datetime.now; contentversionrepository.save(contentversion); contentversionrepository.updatecurrent(contentversion); scope.complete(); homecoming json(new { status = "success" }, jsonrequestbehavior.allowget); } grab (exception ex) { homecoming json(new { status = "fail" }, jsonrequestbehavior.allowget); } } } // ef repository public class contentversionrepository { private readonly myefcontext context; private readonly dbset<contentversion> dbset; public myefcontext datacontext { { homecoming context; } } public ilist<contentversion> getbyid(int id) { homecoming context .database.sqlquery<contentversion>(string.format(@"select * contentversions c c.id = {0}", id)) .orderby(v=>v.version) .tolist(); } public int save(contentversion version) { entities.add(version); homecoming context.savechanges(); } public int updatecurrent(contentversion version) { context.database.executesqlcommand(string.format("execute sp_update_current {0},{1};", version.id, version.version)); homecoming 0; } public dbset entities { { homecoming dbset; } } public contentversionrepository(myefcontext context) { this.context = context; dbset = context.contentversions; } } }

the sp_update_current sproc looks like:

create procedure [dbo].[sp_update_current](@id int, @version int) begin update contentversions set iscurrent = case when [version] = @version 1 else 0 end id = @id; update contents set title = cv.title, htmlcontent = cv.htmlcontent, summary = cv.summary contents c inner bring together contentversions cv on c.id = cv.id cv.id = @id , cv.iscurrent = 1 end; go

however, users reporting every 1 time in while, when saving new versions, content out of sync. is: content table not updated correctly. new contentversion record there, iscurrent false (0). doesn't happen, occasionally.

my thought was double clicking causing sort of race condition, set js in place maintain users double clicking, , added transactionscope, in case. still, issue persists.

i've looked @ code on , on , can't figure out doing wrong.

you don't have duplicate verison numbers, right? create unique index on (contentid, version) create sure.

make transaction serializable definition excludes all concurrency issues.

use sql profiler transaction tracing create sure database calls part of save happen within single session , single transaction. sometimes, calls can "slip out" , hard tell source code.

btw, mix of ef , raw sql kind of unusual , error prone. if object model ef maintains becomes out of sync respect database behavior becomes undefined.

c# sql-server asp.net-mvc entity-framework