>>I need to retrieve a record out of a queue, and I want to lock it to make sure that only one user retrieves and is working on a record at a time.
>>
>>I don't think this is a complicated concept, but I've never done it before. Can somebody give me a crash course on what I'm looking for? I'm going to have a status on the record of Pending/Done I think to explicitly mark that the record has been processed, but I need to work on the locking part. This is the requirement that was provided to me:
>>
>>This action must be safe across multiple webservers, meaning no 2 users could possibly receive the same row at the same time (atomic operation? Cross-database locking?)
>
>I had a similar issue for a system that sends messages to the PAs my solution (it works well, not sure if I am lucky thou) was a combination a transaction level and update command:
>
>From WIkipedia:
>
>Repeatable reads[edit]
>In this isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so phantom reads can occur.
>
>
>
>

>		TEXT TO lcUpdate NOSHOW FLAGS 1 PRETEXT 1 + 2 + 4 TEXTMERGE
>			SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
>			
>			BEGIN TRANSACTION READQUEUE;
>
>			WITH MQ(PK, Served, ServedBy) AS
>				(
>						SELECT		TOP 1
>									MessagesQueue.PK,
>									GETDATE(),
>									Ports.PK
>							FROM	SkyDB.SkyVoice.MessagesQueue
>							JOIN	SkyDB.SkyVoice.Ports on Ports.PK = <<tnPortPK>>
>							JOIN	SkyDB.SkyVoice.Priorities ON Priorities.PK = Ports.fkPriorities
>							WHERE 	Served is null and MessagesQueue.Priority >= Priorities.Priority <<a bunch of other conditions>>
>							ORDER	BY MessagesQueue.Priority, MessagesQueue.Queued
>				)					
>			UPDATE SkyDB.SkyVoice.MessagesQueue
>				SET		Served		= MQ.Served,
>						ServedBy	= MQ.ServedBy
>				OUTPUT	Inserted.PK
>				FROM	SkyDB.SkyVoice.MessagesQueue
>				JOIN	MQ ON MQ.PK = MessagesQueue.PK;
>
>	 		COMMIT TRANSACTION READQUEUE;
>	 		
>	 		SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
>		ENDTEXT
>

>
>Imagine I have a number of "dialers" (in one or more computers) that are watching the MessagesQueue table, when a new message is requested and a record added to the queue I need only one of this programs to actually dial out and play the message, my thinking here is that the isolation level will put a lock on the record while selecting the records (only one, the oldest) and then does the update immediately on the field Served, which is one of the fields used to filter the select statement.
>
>This has been working quite well for some time (2-3 years)

Mind giving me a quick code review to make sure I grasped all the important concepts?

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION GetNewLead;

DECLARE @LeadID int =
	(
		SELECT TOP 1
			ID
		FROM
			Leads
		WHERE
			Processor_Id IS NULL
		ORDER BY
			EnteredOn
	);

UPDATE
	Leads
SET
	Processor_Id = 1
WHERE
	ID = @LeadID;

SELECT @LeadID;

COMMIT TRANSACTION GetNewLead;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;