Rows lock - manually using(different transactions)

0
votes

I want to make a lock mechanism like that, but I'm not sure it possible or not. First, Lock a row in temporary table(with select statement and output data to application) Next, I make some other transactions(Insert, Update to another table) And then release the locked row.

In that time, Any select statement that want to get data from locked row should wait

2
sql-serverlocking
Have you checked sp_getapplock? Sounds like you're trying to implement something similar - James Z
@JamesZ: Thanks you. I will check! - pham cuong

2 Answers

0
votes

All you need is the strictest transaction isolation level, it will prevent other users to access the rows even when you are only reading the row.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

   SELECT * FROM TableName WHERE <some row>

   UPDATE <Statement>

COMMIT TRANSACTION;
0
votes

See this article KB324417 and try this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

   SELECT * FROM TableName (XLOCK, PAGLOCK) WHERE <some row>

   UPDATE <Statement>

COMMIT TRANSACTION;