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.

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 ·
Are you modifying the row?
0 Likes 0 ·
No John, I am just selecting the row from the table.
0 Likes 0 ·
I too am curious to know like @JohnM here. What exactly do you want to achieve from the case above?
0 Likes 0 ·
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.