Re: Locking rows

On Sat, 1 Mar 2003, Jean-Christian Imbeault wrote:

> Doug McNaught wrote:
> >
> > If you use a single query to generate the rows, this will not be a
> > problem.
> >
> > If you use multiple queries, execute them all inside a transaction and
> > you will get a consistent view of the database.
> >
> > No locking needed.
>
> True but what I am worried about is the data changing while I am in a
> transaction.
>
> Right now I want to print customer receipts for items that have been
> ordered. But while I gathering the data, which takes more than one
> query, a customer might come along and cancel an invoice.
>
> In that case I would print a receipt for something that was cancelled.

But then what happens if someone does want to cancel? You're going to say they
can't? Unless the invoice print run is going to take quite a while I think
you're approaching it incorrectly. Even if the print run is going to take along
time you're still approaching it wrong. Say it runs for 2 hours and a customer
wants to cancel but you've locked the tables because of the printing. That
customer has to wait for 2 hours and then still needs the credit note
printing. Why not just let the transaction take a snapshot of a consistent
database, do the print run and do the credit note run as normal. You can't
avoid the credit note run whatever.

> I need to find a way to avoid this. I thought locking was a way around
> this ... or maybe I need to change my "business" logic or the way the
> app gathers the data?

It's not something I've looked at but presumably lock table will lock it so
inserts are also locked out. Obviously select for update can't prevent those
from happening so if cancellations are recorded via insert then there would
appear to be no option but to lock tables.

I could have the wrong end of the stick for what you are wanting. I only vaguly
remember the start of this thread.

Nigel Andrews