Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NewMedia42
Starting Member
35 Posts |
 Posted - 2004-05-05 : 16:01:52
|
| I have a project I'm working on, where I need to update lists of things in two different ways;Update #1: The first way is I take a master list of locations, and I need to contact another server to find out if anything has changed at that particular location. If new things have been added, I need to add them as well.Update #2: The second way is that each item that is associated with a location may have it's own custom update cycle, anywhere from every 5 minutes to once a day.In order to solve the first update, I added an additional field to the DB called "AssignedTo", which gets set whenever a particular service takes ownership of the location to check all the items.The second update situation has me stumped at the moment. The critical thing is there will be at least 10 or more services running at the same time updating things - so I have to make sure that if I return a particular row to the service, that another service won't get the same one. Would it be possible to construct a select query which would only return 1 item (doesn't matter what order), and would set an "AssignedTo" value at the same time it returns, to ensure no one else gets it? Is this the best way to try to solve this sort of issue, or is there another method that I should consider? The solution need to be able to work, even if there are 100 or more services that may be updating the data simultaneously. |
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2004-05-05 : 16:57:23
|
| perhaps rather than an assign to column, have a queue table. Insert into queue select top 1 column_list, assignedto from items left join queue on items.id=queue.id where queue.id is nullthe way transactrions work in SQL Server mean any two or more services won't be able to select and insert the same row from the items table.now you can select id from queue where assignedto = whichever service into a local variable and pass that to the service.when the service completes delete that item from queue.;-]... Quack Waddle |
 |
|
|
|
|
|
|
|
Checking in/out items - DB structure/process q...