I have written an application that prints out data from a database. One
problem I am encountering is in the time it takes to generate the print
file the data may have changed.
I was thinking that a way around this would be to lock the rows that are
used to generate the print file. However I am not quite sure how to best
go about this.
Is locking the rows the best (or simplest) solution?
If locking is a good idea how could I lock and then unlock the rows
returned by this (somewhat ugly) query?
select prod_id, sum(quantity), case when (select stock_qty+cancelled_qty
from stock where pid=prod_id) is null then 0 else (select
stock_qty+cancelled_qty from stock where pid=prod_id) end as stock_qty
from invoice_li, invoices where invoices.id=invoice_id AND dist_id=1 AND
NOT invoices.cancelled AND NOT invoice_li.cancelled group by prod_id;
I will also be doing some other select queries between this one and the
printing (I need to get more information on each prod_id returned).
I've read the iDocs and Bruce's online manual but can seem to find any
info on the syntax for locking other that for SELECT FOR UPDATE. And
even then the docs don't state how to remove the lock created by a
SELECT FOR UPDATE.
Thanks!
Jc