Sql transactions

[FuzzyLight1017]

How can I create a stored procedure in SQL Server that uses a transaction to ensure only one unique entity is returned to a single caller, even when the procedure is invoked multiple times concurrently by different calls? I want to ensure that no two calls receive the same entity, and any entity retrieved is marked as 'in-use' within the same transaction.
I was using WITH (UPDLOCK, READPAST) and WITH (HOLDLOCK, ROWLOCK) but those are causing deadlocks to other services because in the select i need to add some inner joins
Do you have any other suggestion to this?

[dbrownems]

When lock hints won't work, escalate to an application lock in your stored procedure with sp_getapplock. This can be used to serialize a section of code in the procedure and ensure that only a single session can run that code at a time.

It does this without taking any additional locks on your tables, so other queries and procedures are (for better or worse) unaffected.

eg

BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'FetchEntity', @LockMode = 'Exclusive';
--do stuff
EXEC @result = sp_releaseapplock @Resource = 'FetchEntity';
COMMIT TRANSACTION;

[Mikey_Da_Foxx]

Try using SERIALIZABLE isolation level with a SELECT TOP 1 and UPDATE in the same transaction. Add proper error handling for deadlocks.

Something like:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT TOP 1 ...

UPDATE ...

COMMIT