I need to select a row and lock it for a while until a external process ends.

The problem is that I have some threads running at the same time and if I do not lock it in anyway, other thread can take the same row, and that's what i need to prevent.

I've been thinking in update the selected row and set a new field called 'lock' to 1 and prevent others to select it.

Is there any way in making a select with an update?



If you're in a 'transaction' (and assuming you're not using any kind of snapshot isolation or other mechanism that either skips locks or reads previous versions), you could technically update any column to it's current value for the given id (or unique key) - that would prevent you from having to add an additional column for locking purposes.

Example: Table1 (Id int, col2 varchar(10), col3...)

UPDATE Table1 SET col2=col2 WHERE ID = ?

That session would lock that row until a commit or rollback occurs. The locking session is able to freely read the locked row.

You should be able to prototype that by creating a test table, insert a few rows, begin a transaction and update one of the rows without committing and then bring up another SSMS window and try to access that row. The second session should remain blocked until the first session commits or rolls back.

Be aware that any processes (other than the locking process) that require table scans of this table will also be blocked until commit or rollback.

| improve this answer | |

I found a better solution for my case.

The fact is that I did not know that from operations like insert, update and delete, you can request fields from affected rows using the operator OUTPUT.

In my case, this is the query that permits me to lock (using a field) a row from other queries:

UPDATE TOP (1) dbo.list_objects 
SET locked=1 
OUTPUT inserted.ID,
WHERE linked=0 AND error=0 AND locked=0

That query, supply me the fields needed to work with and prevent other threads to getting such row while it being processed.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.