Here is a scenario to which I need good solution.
I keep getting messages into a table ABC (24 hours & 7 days a week). Every message contains unique ID and that goes into ID column of ABC table.
This same ID is also in another master table XYZ.
Now I have a Win service which processes the messages (24 hours & 7 days a week).
Win service selects top 1 unprocessed ID from ABC table and call the SP p_proc1 to process the message. p_procc1 takes ID as argument. Once the messge is processed it will mark the rcord as processed (sets the column 'processed' to 'Y')
Now the issue is - SP p_proc1 cannot process the messages if the record with same ID in XYZ table has been locked by some application. (Some UI application locks the records indefinitely for hours and sometimes even days.)
So while getting the ID from ABC table – I want to make sure it is NOT locked (rowlock) in XYZ table. How to do this?
Another question is - how to process continuously the messages in ABC table in database itself without Win Service? Can anyone suggest me the solution how to process the messages without Win service (I mean in database itself) 24/7 ?
If the record is locked in master table XYZ, it should process the message later when it is unlocked.- We are not sure when the record will be unlocked in XYZ table. Sometimes it may take 10 days to get unlocked.
I will be thanful if someone suggests me a good a solution for this.