Lock a field/record for reading

Transact-SQL
1

How do I lock a field / record so that it cannot be read by another process until my sproc finishes executing? Does using a transaction accomplish this?
TIA

2

what does your sproc do?

3

You would use this statement prior to beginning a transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Keep in mind that the default locking level in SQL Server is PAGE, if you only want to lock the row(s) you're processing you must include WITH (ROWLOCK):

SELECT 
FROM Table1 WITH (ROWLOCK)
INNER JOIN Table2 WITH (ROWLOCK) ... etc.

Also keep in mind that if you will be working on many rows (1000 or more) it's likely more efficient to lock the pages rather than the rows, it will use less memory and CPU.

You cannot lock "fields" (columns) in SQL Server, only rows, pages and tables.

4

Yes, a transaction is needed to do it for one table / row / column.

You can then add hints (which are really commands to SQL) to exclusively lock that row until you're done with the proc.

CREATE PROCEDURE ...
AS
...

BEGIN TRANSACTiON
SELECT
FROM dbo.table_you_want_to_restrict WITH (ROWLOCK, XLOCK)
INNER JOIN dbo.some_other_table ... ON ...

COMMIT TRANSACTION --it's critical to either COMMIT or ROLLBACK transaction to free the lock