Isolation usage - Practical Scenario

• Siva Ramasamy

  SSCarpal Tunnel

  Points: 4026

  More actions

  February 8, 2012 at 12:13 am

  #251156

  Hi All,

  I think I can handle this scenario by using the right isolation levels...but not sure how.

  Scenario:

  Table A has 5 columns (C1, C2, C3, C4, C5).

  User1 opens a connection to Table A and reads all the 5 columns @ 10:30 AM

  User2 opens a different connection to Table A and reads all the 5 columns @ 10:31 AM

  At this point User1 and User2 are seeing the same values for all the 5 columns.

  @ 10:35 AM User1 modifies the values for column1, 3 and 5, but updates all the 5 columns to the table and commits it.

  Now Column1, 3 and 5 have new values ....column 2 and 4 have old values in the table.

  @ 10:36 AM User2 modifies the values for column 2 and 4, but updates all the 5 columns to the table and commits it.

  Since User2 read the table before User1 had committed his changes, now column 1,3 and 5 have lost the changes made by user1 and is now showing the values as of 10:30 AM.

  I know that both User 1 and User 2 are reading the table for updating the values.

  So, I want to let User 2 know that some one is already trying to modify it...

  How do I achieve this....???

  Thanks for reading my loooooooong question......

• Paul White

  SSC Guru

  Points: 150467

  More actions

  February 8, 2012 at 3:35 am

  #1444384

  There are a couple of choices, use pessimistic locking or the optimistic rowversion:

  http://www.mssqltips.com/sqlservertip/1501/optimistic-locking-in-sql-server-using-the-rowversion-data-type/

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi