0

SQL Server: how to hold exclusive lock for select query? For mysql,

select * from Employee e
where e.id=123
for update

Other concurrent transaction can not read or write the selected row.

How to achieve the same for SQL server?

SELECT *
FROM   Employee e
WITH   (HOLDLOCK, ROWLOCK)
WHERE  e.id = 123;

For (HOLDLOCK, ROWLOCK), is it holding the READ LOCK on the selected row? With READ LOCK, other transaction can still read the locked row, right?

Dale K
  • 25,246
  • 15
  • 42
  • 71
eastwater
  • 4,624
  • 9
  • 49
  • 118
  • Here are the [docs](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15) – Dale K Mar 17 '20 at 05:51
  • if your update command in **Begin Tran** and turned of **read committed snap shot** you don't need any-think . Select statement wait for end update then run. – Amirhossein Mar 17 '20 at 06:54

2 Answers2

1

You should specify XLOCK-hint on your statement to achieve desired behavior.

Other concurrent transaction can not read or write the selected row.

SELECT *
FROM Employee e
WITH (XLOCK)
WHERE e.id = 123;

Now I want to describe why HOLDLOCK and ROWLOCK won't do a trick.

HOLDLOCK will inform SqlServer to hold S-locks (shared locks) until transaction ends. S-lock prevents concurrent writes while someting is being read. But it allows to read data concurrently. That's why it called 'shared'. By default, shared locks live as long as it proposed by current isolation level of transaction. For read committed isolation level (which is default) shared lock will be released after data was fully read. That means row-level lock releases just after row was read, page-level lock releases after all data on page was read and table-level lock releases after statement was ended. But HOLDLOCK forces shared locks to be released at the end of transaction. So, when you set HOLDLOCK, shared locks of statement are forced to live longer like it was executed within transaction with repeatable read isolation level.

XLOCK works just like HOLDLOCK with one difference: X-locks (exclusive locks) are used.

UPDLOCK forces SqlServer to use U-locks (update locks) inistead of S-locks without any other differences with HOLDLOCK hint. U-lock is a specific lock type for read statements followed by any writes within transaction, what potentially can cause deadlocks. So, you use UPDLOCK when you want restrict reads of the same data during concurrent transactions for update purpose.

ROWLOCK tells SqlServer to hold locks at row-level even when page- or table-level locks are supposed to be used. Basically, you don't need to specify ROWLOCK if you're not sure what you do, because you can make statement execution more complicated.

Alex Ilin
  • 51
  • 1
  • 4
0

With the addition of an exclusive lock (xlock) or update lock (updlock) any other select statement will be blocked, for the duration of the transaction. Whether only the row is locked (or not) depends on how the row is accessed: if a table scan is performed then the whole table is exclusively locked.

begin transaction
select *
from Employee with(xlock /*or updlock*/, holdlock, rowlock /*not assured*/)
where id = 123

--check the locks
exec sp_lock
--rollback transaction
lptr
  • 1
  • 2
  • 6
  • 16
  • For the query, only one row is selected. what do you mean by rowlock /*not assured*/? could you explain: Whether only the row is locked (or not) depends on how the row is accessed: if a table scan is performed then the whole table is exclusively locked. Why the whole table will be locked? – eastwater Mar 17 '20 at 20:05