can isolation levels prevent duplicate insert?

i have a table like this

UserPageId (Primary Key) int UserId (user Foreign Key) int PageId (page Foreign Key) int

and the whole senario is to prevent user to add one page more than once,,according to each user can be use with several person and we might have concurrent users

first i want to solve this by isolation level Serializable,but it ends with deadlocks

my functions are:

public void Add(int PageId,int UserId) { using (TransactionScope scope = newTransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable })) { using (entities EFmodel = new entities()) { EFmodel.Connection.Open(); EFmodel._UserId = UserId; if (!Pages.Exists(EFmodel, PageId)) Pages.Add(EFmodel, PageId); else ERModel.AddModelError("", "you have already added this page"); EFmodel.SaveChanges(); } scope.Complete(); } return ERModel; }

Pages.Exist:

public bool Exists(entities EFmodel, int PageId) { int ctn = EFmodel.UserPages.Count(x => x.PageId == PageId && x.UserId == EFmodel._UserId); if(ctn!=0) return true; return false; }

Pages.Add:

public static void Add(entities EFmodel,int PageId) { UserPage userpage = new UserPage() { UserId = EFmodel._UserId, PageId = PageId, UserPageId = 0 }; EFmodel.UserPages.AddObject(userpage); }

--------------Solutions-------------

There is no way to allow concurrent update of shared data while both preventing update anomalies and avoiding serialization failures. Deadlocks are one type of serialization failure. The trick is to route the database requests through a layer which will, in a generalized way, catch serialization failures and retry the affected transactions.

Category:isolation level Time:2012-04-09 Views:0

Related post

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.506 (s). 13 q(s)