Search code examples
c#sql-serverstored-procedurestransactionstransactionscope

SQL Server Transactions --- C#

I need a help. Let me first explain the scenario with a small sample.

Suppose I have a Students table with columns:

Id int(PK)  
Name varchar(200)  
Marks1 int  
Marks2 int  
Marks3 int  
TotalMarks int  
IsTotalCalculated bit  
InProcess bit

This table has huge number of records.

Now, I want to calculate the TotalMarks of each student and update the TotalMarks column.

Now coming to my C# Console App I am calling to stored procedures:

SP1 => I am fetching top two records at a time which has InProcess = 0 and IsTotalCalculated = 0, sets its InProcess = 1 and do the processing. (Has a SELECT and UPDATE)

SP2 => Finally again update these two rows which updates its IsTotalCalculated = 1 and InProcess = 0 (UPDATE)

Concern: My concern is as soon as I select the 2 rows for processing then any other Console App instance should not select these 2 rows for processing. What should I do?
Note: I have put the C# code of my two SPs in a TransactionBlock.

Thanks,

Justin Samuel

Solution

  • Can't you just check in SP1 if InProcess = 1 and then if that is true ignore the rest until InProcess becomes 0 ?

    The trick is to block any reads while you update InProcess to 1. This can be done with SET TRANSACTION ISOLATION LEVEL READ COMMITTED which specifies that statements (in your SP) cannot read data that has been modified but not committed by other transactions.

    Hope this helps.