Re: User-facing aspects of serializable transactions

On Tue, Jun 2, 2009 at 1:13 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Greg Stark <stark(at)enterprisedb(dot)com> wrote:
>
>> Just as carefully written SQL code can be written to avoid deadlocks
>> I would expect to be able to look at SQL code and know it's safe
>> from serialization failures, or at least know where they might
>> occur.
>
> This is the crux of our disagreement, I guess.  I consider existing
> techniques fine for situations where that's possible.

a) When is that possible? Afaict it's always possible, you can never
know and when it might happen could change any time.

b) What existing techniques, explicit locking?

> But, could you
> give me an estimate of how much time it would take you, up front and
> ongoing, to do that review in our environment?  About 8,700 queries
> undergoing frequent modification, by 21 programmers, for enhancements
> in our three-month release cycle.  Plus various ad hoc queries.  We
> have one full-time person to run ad hoc data fixes and reports
> requested by the legislature and various outside agencies, like
> universities doing research.

Even in your environment I could easily imagine, say, a monthly job to
delete all records older than 3 months. That job could take hours or
even days. It would be pretty awful for it to end up needing to be
retried. All I'm saying is that if you establish a policy -- perhaps
enforced using views -- that no queries are allowed to access records
older than 3 months you shouldn't have to worry that you'll get a
spurious serialization failure working with those records.

--
greg