Periodic event implementation

I have an ASP.NET MVC 开发者_运维百科2 web-site. All the buisnes is in the databse stored procedures (MS SQL SERVER 2008).

The task:

Some user creates record, using web-site. Appropriate row inserts into the table. This row has foreign key to another table, its value sets in the procedure. Then I want application to reassign record (change foreign key field) to another, if it was not handled during 1 minute.

For example:

1. User creates record A(id = 1, adminId = 1).

2. Only administrator with id=1 see this record and he should handle it during 1 minute.

3. If admin 1 not handles record during 1 minute, it should be reassigned to another administrator. For example A updates to (adminId = 3).

Which way would be better to do this? Parallel thread? Another application in background? Or...

---

I don't think you need a separate process to handle the time.

If you track the record creation time the above can be implemented in the application logic or as database procedure - which happens every time data is pulled from the database.

So

1. User creates the record A(id = 1, adminId = 1) with timestamp ts
2. If administrator tries to access the record the db/app will allow it if the difference between current date/time and ts is less then 1 minute; if more then 1 minute the record gets reassigned to another admin
3. If another admin tries to access the records and more then 1 minute has passed the record is assigned to them (for another minute?)

All of the above work without another process coordinating it (all of the above pull the data) and the database can be used for locking (either through some state and time of state that can be stamped on the record).

It is only if you want to push records or have the above maintenance done automatically (for some reports). Still it can always be done by checking if the records have been reassigned before getting other data.

Having another process and having it as stable as the DB is non-trivial, but you can create jobs which would run periodically within DB environment (or you can go SMO path if more applicable to your scenario).

However, I believe you did not describe all the states that the record can have (what happens if adminId|3 does not 'handle' the record).

EDIT After the comment let me explain a bit. Assuming you need some work to be done every 5 minutes (or every one minute) then it does not matter if you are actually doing it ever minute or if you are doing it the first time before user requests the data that might be affected by it.

There are differences in the two approaches, mainly the following If it is done lazily - no updates unless users requests data then

Lazy updates vs. Timely updates

• in the case of low activity on this table, you save unnecessary periodical updates (but have to make sure no client is getting stale data! - for example running reports on the DB will require you to make sure that data is updated)
• in case of heavy activity on this table there is some overhead (if there are 100 requests within the minute then checking if the data is up-to-date will require additional resources)
• you avoid having extra jobs that need management and you avoid relying on the jobs (less complex)

---

You do not need external tools. All logic can be built-in. You should add columns with time in your table, eg LastHandleTime and locked. In your example, when Admin3 try to search something, logic should be something like this:

    SELECT * FROM Table Where 
    (user='Admin3') Or 
    (DATEDIFF(m,GETDATE(),@LastHandleTime)=1 
        And dbo.fn_ChangeAdminName('Admin3') 
 and locked=0)

Here function fn_ChangeAdminName() shows you which users can access records instead of "original" user.