Is it possible to lock a row with SELECT statement in a transaction, in SQL SERVER? I want to lock the row, so other transactions from outside cannot reach that row.
After the transaction is committed or rollbacked, the row should be released. Here is what I mean...
BEGIN TRANSACTION TRAN1
SELECT * FROM HR.Employees WITH (UPDLOCK) WHERE empid=1
...
...
...
...
COMMIT TRANSACTION
Does anybody have a suggestion? Should I do an UPDATE statement to lock the row?
Please do not mark this question as a duplicate. Because, I am not asking about UPDATE statement, I am asking about SELECT
EDIT: I tried to 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' but that locks too many things. My SP is huge and it has many SELECT statements. 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' locks the rows from all SELECTs in the SP. However, I would like to lock only rows from one table.
I think the HOLDLOCK table hint is what you're looking for. From the documentation:
HOLDLOCK Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.