Re: When to Commit?

Daniel,

All commits should always be between logical units of work, but the issue is as I interpreted Sybrand's comment in his reply on how often to commit within a process is that the entire job should have only one commit. While this is fine in theory the reality is that it is not practical or desirable from a concurrency point of view. I do not believe any large complex OLTP application exists where concurrent demand for the same rows can be completely designed out. Waiting two seconds for a logical unit of work to complete is a necessity, but waiting one minute for a task to complete 30 such logical units of work to complete is unacceptable to the end-user sitting in front of a terminal. Sometimes you commit between every unit of work is completed and sometimes only after tens of thousands of units complete.

I will rephrase my point that you set the frequency for issuing commits between logical units of work based on the competing demands for concurrent access to the row and the theoretical desire to issue one commit at the end of the process.

As far as long running jobs go we do not currently have any that run more than a couple hours so I will have to reference tasks that I have seen but do not have access to. I visited with some competitors a couple of years back. There were running OPS on two 12-cpu machines. We were talking about their 11T warehouse. It turned out the largest portion of the warehouse was actually an OLTP system whose primary function was to generate billing. They would generate line item detail invoices that often numbered over 1,000 pages for every customer they had. I will take their word that the time to perform the physical IO just to read the data took over 12 hours. I do not believe that PQO was in use. The process was a single job. But arguing about the fact this process could have been improved to run in less time would still not change the fact that it would still need to commit as it ran because even an improved process would hold locks too long if other processed needed to access the same rows. An example might be the order header rows with said rows being locked because they had been marked as invoiced You cannot block adding new detail items for the current month's bill just because the prior month's bill is being generated.

HTH - Mark D Powell - Received on Mon Jul 04 2005 - 15:33:09 CDT