I need to lock a row in a table so no one can read this line while I'm running a procedure. I am using BEGIN TRAN in this procedure. So, this record I'm trying to block is uncommitted during the process.
Is it possible?
Asked
Active
Viewed 146 times
4
Szymon
- 42,577
- 16
- 96
- 114
Felipe Pilon
- 63
- 1
- 5
-
1Possible Repeat Question : http://stackoverflow.com/questions/111652/locking-row-in-sql-2005-2008 – granadaCoder Oct 02 '13 at 19:23
1 Answers
0
Depending on what is the purpose of your stored procedure:
- In case it modifies the mentioned row, you can base on transaction levels
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
--UPDATE/INSERT/DELETE your row here
...
COMMIT TRANSACTION SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
- Use lock hints
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT column1, column2
FROM yourTable WITH (ROWLOCK)
WHERE ID = YourRecordId
...
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
