A customer has a problem where while one user is doing some "horrible batch process" they don't want any other user to carry out "another horrible process". There is the danger that the "horrible batch process" may not complete due to an issue or a network failure, also it cannot (for various reasons) be completed in a single transaction.
Various possible flagging mechanisms for dealing with this all have issues with cleaning up in the case that the connection doing the "horrible batch process" disconnects before completion, which would then require a manual resetting of a flag.
One suggestion was to use lock table a dedicated table with LOCK TABLE and that the second process would not start if it could not successfully carry out an appropriate operation - however because of the transaction issue (see above), the WITH HOLD clause would be needed so that the lock would survive commits. However in the absence of a RELEASE TABLE statement is there a way of releasing such a lock without disconnecting? Just setting a new table lock without the WITH HOLD doesn't seem to do it. Better ideas gratefully received.
In general everything operates at isolation level 0.
asked 26 Mar '14, 13:05
You can use the flag scenario in combination with a database timer event. Your process needs to update the flag every minute. The database timer will check every two minutes, if the flag is outdated (older than 1 minute). In this case the database timer is cleaning the flag.
This approach will quarantee, that in case of a failure in the "horrible batch process" the flag will be cleared.
This approach is independent of transactions, connections and even robust towards short network disconnects.
(Any credits for the basic idea go to the team of Microsoft Exchange, don't know where they have borrowed it ;) )
answered 26 Mar '14, 13:25