SQL Server Transactions --- C#

Question

Hi All,

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

Answer 1

I'm sure you have a reason for doing things the way you are, but if this is the actual table and not just a illustrative sample then why don't you use a view with a calculated Total column? Then you have no need for processing rows with console apps, and no need to lock any rows.

Answer 2

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.

Answer 3

Why on earth do you want to do this in C# ?? This is perfect for T-SQL!

 UPDATE 
     dbo.Students
 SET 
     TotalMarks = (some formula),
     IsTotalCalculated = 1
 WHERE 
     IsTotalCalulated = 0

One T-SQL statement and you're done, no worries about transaction scopes and shuffling loads of data back and forth.......

OK, so if you must stick to your current approach - here's what you could do:

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?

How about "hiding" the Students table, e.g. not allowing anyone access to it, and instead using a view on top of it, which only ever shows the rows that have InProcess = 0?

CREATE VIEW dbo.StudentsView
AS
    SELECT (list of fields)
    FROM dbo.Students
    WHERE InProcess = 0

This way, any request to read students will always only read those not being processed right now.