question

dheeraj.uthaiah avatar image
dheeraj.uthaiah asked

Locking the rows selected in transaction

How to lock the selected rows in transaction in SQL Server 2012, so that the selected rows couldn't be selected by other select statements until the transaction gets committed? Thanks in advance.
selecttransactionsqlserver2012locking
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
If the data is already committed to the table, why are you trying to isolate it from other read operations? Just curious.
1 Like 1 ·
JohnM avatar image JohnM commented ·
Are you modifying the row?
0 Likes 0 ·
dheeraj.uthaiah avatar image dheeraj.uthaiah commented ·
No John, I am just selecting the row from the table.
0 Likes 0 ·
nidheesh.r.pillai avatar image nidheesh.r.pillai commented ·
I too am curious to know like @JohnM here. What exactly do you want to achieve from the case above?
0 Likes 0 ·
Oleg avatar image Oleg commented ·
To join the group of curious, I can also tell that I ain't the faintest why you need it. If you really, really like blocking the other transactions from reading the data while the first transaction selected them, then you can try to lock the whole table with exclusive lock so nobody can read any rows until the first transaction is done, try this: open 2 SSMS query windows. In the first window, begin transaction and select the data with x lock: begin tran; select * from SomeTable with (tablockx); In the second window, try to run the select: select * from SomeTable You will see that the second window keeps "executing" without returning any rows. Go back to the first window and rollback tran; -- or -- commit tran; -- does not matter which one Once you executed rollback (or commit or just closed the first window or kill the session), the select statement in the second window will return requested rows. I cannot post this as an answer because after reading it, someone will surely think WHY? WHY? WHY?
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.