Read and Update Strategy

• Lee Chase

  Grasshopper

  Points: 20

  More actions

  September 12, 2008 at 9:16 am

  #190760

  When selecting a row then updating, deleting or creating new rows based on the contents of the row what's the best strategy to avoid concurrency issues?

  My code looks like this

  SELECT ...

  If ColA = 1

  Update row

  else

  if ColB = 1

  delete row

  else

  create new row

  I'm working in an environment with up to 50 concurrnet users and would like to avoid table locking if possible. I tried a ROWLOCK hint but it gets promoted to a TABLE lock.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  September 12, 2008 at 9:34 am

  #871092

  Suggest you read this in BOL and see how isolation levels effect data integrity and make your decision(s) based on that

  Adjusting Transaction Isolation Levels

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9dfc3c68-9bf0-4896-b3b6-d2f1a601ead1.htm

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]