Begin tran and row lock

Question

Hi, I'm using begin tran in my code and with this command - I update a row, it locks the whole table, but i want to lock only the updated rows.

any idea?

Answer 1

Saying that it locks the whole table is incorrect, basically. But we need to be more detailed that so in order to give a usable answer:

SQL Server need to be able to find the rows, both for the UPDATE and also for the other SELECT (I assume you are trying to read rows while the UPDATE transaction is open). If you don't have an supporting index for any of the elements in the WHERE clause, then below will happen for every row in the table. If you do have an index, below will happen only for the rows that SQL server need to read, based on the index it chooses to use. This applies for both the UPDATE and the SELECT.

For the UPDATE, for every row it has to read (see above) it will acquire an update lock, check if that row satisfies the WHERE clause. If it does, then an exclusive lock will be held and the update lock will be released. The exclusive lock will of course block others that try to do SELECT. I assume pessimistic concurrency here, which is the default for on-premise SQL Server.

For the SELECT, for every row it has to read (again based on what indexes that exists), a shared lock will be acquired and SQL Server will see if that row satisfies the WHERE clause of the SELECT. If it does, then the row is returned, if not, the shared lock is released and SQL Server moves on to the next row (I assume the default isolation level here, READ COMMITTED).

So, if you have no indexes to support the SELECT, it need a shared lock on every row, to see if that row will be returned. But the UPDATE if course has an exclusive lock, blocking that read. This happens even if the SELECT in the end try to read a different row than the one you updated.

Here is code you can play with, just post back if you want to expand on the discussion:

DROP TABLE IF EXISTS t
GO
CREATE TABLE t(c1 int identity, c2 char(5) default 'hej')

INSERT INTO t
SELECT TOP(10) 'hupp' FROM sys.columns AS a, sys.columns AS b

BEGIN TRAN
UPDATE t SET c2 = 'tjoho' WHERE c1 = 5

--Do this SELECT from annother session. It will be blocked.
SELECT * FROM t WHERE c1 = 7

--Study locks, from yet another session
SELECT * FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'
AND request_mode NOT IN('IS', 'IX')
ORDER BY request_session_id


--When we are done
ROLLBACK

Answer 2

Hi @Kodeeswaran Duraisamy ,

Hi, I'm using begin tran in my code and with this command - I update a row, it locks the whole table, but i want to lock only the updated rows.any idea?

Do you want to lock a single row in a way similar to Oracle's “SELECT FOR UPDATE WAIT”?

Quote from this similar case : https://stackoverflow.com/questions/9502273/in-sql-server-how-can-i-lock-a-single-row-in-a-way-similar-to-oracles-select#:~:text=BEGIN%20TRANSACTION%3B%20SELECT%20ITEM_ID%20FROM,built%20dynamically%20in%20my%20code.

In SQL Server there are locking hints but they do not span their statements like the Oracle example you provided. The way to do it in SQL Server is to set an isolation level on the transaction that contains the statements that you want to execute. See this MSDN page but the general structure would look something like:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

select * from ...

update ...

COMMIT TRANSACTION;

SERIALIZABLE is the highest isolation level. See the link for other options. From MSDN:
SERIALIZABLE Specifies the following:
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

More information : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15

BR,
Mia
If the reply helped, please “Accept Answer” and upvote it.--Mia

Answer 3

Don't use begin transaction at all. A simple TSQL update command in default isolation level read committed will lock the low exclusively which needs updation and will not allow others to update the same row unless you have finished. I hope this is what you need. Let me know

Answer 4

The UPDATE will lock the table exclusively.