Paul White NZ (4/5/2010)
Bill Nicolich (4/5/2010)
...when that happens, there's the problem of identifying rows that have been marked as reserved but are not being handled by a current user.A history table works well. For simple implementations, adding columns to the queue table to store the process/user identifier that owns the resource, and the time it acquired it, can work well (enough) too.
When you match up a work item with a user, there are some considerations. What constitutes a user? In my case, a user was a unique session id from the web server. It wasn't a web farm situation. A user could have IE open with one session and FireFox open with another session - and these in my case should be handled separately. So if I just had just a "user id" matching things up in the table, I'd run into problems. So, that's why I added both a session id and a user id in the table.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow