TransactionScope to read single record

Question

I need to read a single row from SQL Server with a stored procedure as a standalone call. Let's say there is a BL.GetRecord(int id) for it.

I need to call this method from inside another method. Do I need a transaction scope for reading it?

Here are possible GetRecord(int id) bodies:

using(var ts = new TransactionScope())
{
    return prov.GetRecord(id);
}

or

using(var ts = new TransactionScope())
{
    var record = prov.GetRecord(id);
    ts.Complete();
    return record;
}

OR just

return prov.GetRecord(id);

Method for 2. will have its own TransactionScope with Complete().

The purpose is to read 'clean' records.

Do I need to Complete() (is it = COMMIT?) on reading as we aren't saving anything?

All these operate on a single table.

I need something like, 1 user updates a record (which also does a reading first). I think it's better if second user should read updated value by waiting rather than reading whatever is currently if the first is going to update it in few milliseconds anyway.

Not by much but thanks. Maybe I need to add some locks in a select query too. — Nickolodeon, Dec 22 '20 at 13:48

score 1 · Answer 1 · answered Dec 22 '20 at 15:13

You can use the transaction where you are updating the record, so that when you get request to read and update the record at the same time, update transaction will hold lock on row, will release once transaction is completed and next user will get updated record.

No need to use transaction scope for reading.

TransactionScope to read single record

1 Answers1