-2

I have been reading up on Transaction Isolation Levels and I am not sure I understand everything correctly. I need help.

Please consider the following Csharp pseudo code:

  1. Using Stored procedure 1, open transaction
  2. Insert or update table 1
  3. Using Stored procedure 2, insert or update table 2, based on data on table 1
  4. Commit transaction.

In step 3, how can I not consider the data that has been updated in step 2? Should I set a Transaction Isolation Level when I am creating a transaction in my .Net code. Here is the SQL server stored procedure pseudo code.

select @count=count(*) from table1;
if @count > 1
update table2
Ajit Goel
  • 4,180
  • 7
  • 59
  • 107
  • To clarify; you want step 3 to see the data in table 1 as it was before step 2? – Dan Def Feb 16 '17 at 20:41
  • @DanDef: At a 10,000 feet level, that true. On a 100 feet level, step 3 does much much more and therefore the sequence of the steps cannot be changed. – Ajit Goel Feb 16 '17 at 20:45

2 Answers2

1

Just changing the order doesn't solve your problem? I mean, if Step 2 changes Table 1 data, and you need it intact to execute Step 3, execute Step 3 first. It would look like this:

  1. Open Transaction
  2. Execute Procedure 2
  3. Execute Procedure 1
  4. Close Transaction

Considering what you just commented, you would probably need to use IsolationLevel.Snapshot. However, some databases don't have this enabled by default. It basically created a copy of the current data to tempdb when you update the source table.

Rodrigo Vedovato
  • 1,008
  • 6
  • 11
  • At a 10,000 feet level, you are right. On a 100 feet level, step 3 does much much more and therefore the sequence of the steps cannot be changed. – Ajit Goel Feb 16 '17 at 20:46
0

As far as I am aware, none of the isolation levels allow you to access previous versions of a row once they have been modified.

To achieve what you are asking, you will need to modify step 2 to keep track of what it has changed and make it available to step 3. Otherwise, you will need to add triggers to the tables modified in step 2 and track the changes that way.

Dan Def
  • 1,836
  • 2
  • 21
  • 39