Re: When to Commit?

The statement that "there shouldn't be any intermittent commits, EVER" is just plan wrong, as is the idea that all long running tasks can be moved to "outside office hours".

What outside hours? We support a world wide dealer distributor system.  We would like Order Entry to pretty much always be available when someone might want our product.

For a couple of years we ran three shifts Monday through Friday and had various departments working at all times over the weekend. There was no such thing as an off-shift. Many of our most important tables have rows which will be accessed by multiple processes within very short time frames. Where the updating processes are online users even waiting 30 seconds for a single commit update task to run is too long.

If anyone who stops to think about it doesn't think the comment that "there shouldn't be any intermittent commits, EVER" is wrong then here is the oposite, just as valid, and just an impractial rule, "no lock should ever be held for more than two seconds."

In the real world the choice of commit frequency has to be based on the competing DML demands on the data.

IMO -- Mark D Powell -- Received on Sun Jul 03 2005 - 15:17:31 CDT