Is it possible to lock a record and still allow other users to read it?

Question

This might be a very dumb question, but as the saying goes, "The only dumb question is the one you don't ask"...

I've got a SQL Server 2008 database and I want to lock a record for editing. However, another user might want to see information in that record at the same time. So, I want the first person in to be able to lock the record in the sense that they are the only ones who can edit it. However, I still want other users to see the data if they want to.

This is all done from a C# front end as it's gonna be on our Intranet.

Answer 1

To expand on Marc_s's answer, the reader can also use the

set transaction isolation Level read uncommitted 

statement as described here to force reads to ignore any locks (with the notable exception of any Sch-M, schema modification, locks) that may exist. This is also a useful setting for reports that do not require absolute reproducibility, as it can significantly enance performance of those reports.for

Answer 2

In addition to the existing answers: You can enable snapshot isolation. That gives your transaction a point-in-time snapshot of the database for reads. This transaction will not take locks on data at all. It will not block.

Answer 3

Don't do your own locking - let SQL Server handle it on its own.

As long as you only SELECT, you'll put what's called a shared lock on a row - other users who want to also read that row can do so.

Only when your code goes to update the row, it will place an exclusive lock on the row in order to be able to update it. During that period of time, no other users can read that one single row you're updating - until you commit your transaction.