SQL Server ROWLOCK over a SELECT if not exists INSERT transaction -


i have upgraded sql server 2005 2008. remember in 2005, rowlock did not work , had use pagelock or xlock achieve type of actual locking. know reader of ask "what did wrong?" nothing. conclusively proved edit "rowlocked" row, couldn't if escalated lock level. haven't had chance see if works in sql 2008. first question has come across issue in 2008?

my second question follows. want test if value exists , if so, perform update on relevant columns, rather insert of whole row. means if row found needs locked maintenance procedure delete row mid-process, causing error.

to illustrate principle, following code work?

begin tran  select      profileid        dbo.usesessions        (rowlock)       (profileid = @profileid) option      (optimize (@profileid unknown))  if @@rowcount = 0 begin     insert dbo.usersessions (profileid, sessionid)     values      (@profileid, @sessionid) end else begin     update      dbo.usersessions     set         sessionid = @sessionid, created = getdate()           (profileid = @profileid) end  commit tran

an explanation...

  • rowlock/pagelock granularity
  • xlock mode

granularity , isolation level , mode orthogonal.

  • granularity = locked = row, page, table (paglock, rowlock, tablock)

  • isolation level = lock duration, concurrency (holdlock, readcommitted, repeatableread, serializable)

  • mode = sharing/exclusivity (updlock, xlock)

  • "combined" eg nolock, tablockx

xlock have locked row exclusively want. rowlock/pagelock wouldn't have.


Comments

Post a Comment

Popular posts from this blog

asp.net - repeatedly call AddImageUrl(url) to assemble pdf document -

i'm using abcpdf , i'm curious if can recursively call addimageurl() function assemble pdf document compile multiple urls? something like: int pagecount = 0; int theid = thedoc.addimageurl("http://stackoverflow.com/search?q=abcpdf+footer+page+x+out+of+", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetochain(theid); } pagecount = thedoc.pagecount; console.writeline("1 document page count:" + pagecount); //flatten document (int = 1; <= pagecount; i++) { thedoc.pagenumber = i; thedoc.flatten(); } //now try again theid = thedoc.addimageurl("http://stackoverflow.com/questions/1980890/pdf-report-generation", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetoc...
Read more

java - Android recognize cell phone with keyboard or not? -

in android app, want detect user's device if device has keyboard(like motorola milestone) , show corresponding user interface. guys know how that? ! regards justicepenny http://developer.android.com/reference/android/content/res/configuration.html public int keyboard the kind of keyboard attached device. 1 of: keyboard_nokeys, keyboard_qwerty, keyboard_12key. that's it!
Read more

iphone - How would you achieve a LED Scrolling effect? -

how achieve led scrolling effect example below? led sign - led ticker emulator iphone , ipad http://img.skitch.com/20101201-rsfh4p1bajb1wp94k466pdpiuj.preview.jpg sometimes remunerates comment in other posts :) have put code you, should implement font '8pin matrix' realistic feeling. uilabel *label; - (void)viewdidload { [super viewdidload]; label = [[uilabel alloc]initwithframe:cgrectmake(10, 50, 300, 20)]; label.font = [uifont fontwithname:@"courier-bold" size:16.0]; label.backgroundcolor = [uicolor blackcolor]; label.textcolor = [uicolor greencolor]; label.linebreakmode = uilinebreakmodecharacterwrap; // otherwise "…" label.text = @"this led text scrolled "; // blanks space between [self.view addsubview:label]; [nstimer scheduledtimerwithtimeinterval:0.12f target:self selector:@selector(scrolllabel) use...
Read more