>>>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)
>
>Hi Hugo
>
>The transaction is wrapped around assigning the record to a processor/dialer? That means the user takes possession of the record until something unassigns it? I'm wondering why not begin the transaction, permit edits and end the transaction. That way the transaction would vanish if the user disconnected, or the user would complete the work requiring the assignment.
PMFJI, but my specific requirement was to assign a lead out of a queue to a processor. After assigning, it's up to them to maintain contact, so ownership of the record is longer-term than the lifetime of a transaction.
In this case, the record lock stuff is to ensure that the top item of the queue can't be assigned twice. It makes identification and assignment a single operation.
