1

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.

Arif YILMAZ
  • 5,754
  • 26
  • 104
  • 189
  • @Oscar, no it is not duplicate with the link you provided. I am asking about SELECT. your link explains UPDATE statement – Arif YILMAZ Sep 30 '16 at 08:59
  • This other one: http://stackoverflow.com/questions/9502273/in-sql-server-how-can-i-lock-a-single-row-in-a-way-similar-to-oracles-select – Oscar Sep 30 '16 at 09:01
  • @Oscar, I tried the solution in the link you gave. It gave me a big hope but it didnt work. Because, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" in SP locks many things. My SP is huge, and it does a lot of SELECTs. If it locks all rows I select, the system will stop. I specifically want to lock rows from one table with a SELECT in SP – Arif YILMAZ Sep 30 '16 at 09:29
  • What about http://stackoverflow.com/a/9503004/122718? This is the right way to do it. I'd add (rowlock) to the mix. Note, that this does not really lock the row. It locks the index record in the clustered index (likely). Other indexes are still readable and X-lockable even. – usr Sep 30 '16 at 09:46

2 Answers2

2

UPDATE lock is a special kind of lock used when an update statement is searching for rows to update. It is compatible with SHARED locks, so it increases concurrency, and also minimizes chance for a conversion deadlocks when converting to XLOCK.

You can use an XLOCK hint on the table in the SELECT statement, but beware that it could lock more than you expected, depending on the indexes and physical path to data in general.

Also, with RCSI, it will not block the readers.

It would be probably better to use some other application-controlled logic here instead of relying on SQL Server's locking mechanism.

dean
  • 9,960
  • 2
  • 25
  • 26
1

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.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68