Re: When to Commit?

Mark D Powell wrote:

> 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.

I think you misunderstood Sybrand. One commit per logical unit of work is the theoretical goal.
>
> 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.

As Daniel mentioned, Billing is seldom an OLTP process.

> ... 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.

What data is billing committing that affects the call centers? Let's see is this example one?

> ... 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 -
>

Header rows locked or detail rows locked, which is is?   Adding details to the account for next month's bill should not even be visible on the query generating last month's bill. There had to be some serious design issues if that kind of interaction was slowing down the billing generator.

And if it was hardware, then the solution is find the bottle neck and open it. Example: one assignment I had involved speeding up various queries for a data conversion. I got about halfway to the goal conversion time when the project was shelved. A couple years later the project was resurrected. But on the newer hardware the "slow" queries were no longer "too slow" and I got do do some other development.

SO, obviously I too side with Daniel and Sybrand: If you think you need to commit within logical transactions, then you design is very likely flawed.

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost