Thread: Locking rows

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

On Friday 28 Feb 2003 10:26 am, Jean-Christian Imbeault wrote:
> 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?

See the chapter "Concurrency Control" in the User Guide. Sounds like you want
to set transaction level to serializable - no explicit locking required.

This means that within your report-building transaction you don't see any
changes to the database (unless you do any within the transaction itself).

HTH

--
  Richard Huxton

On Fri, 2003-02-28 at 05:26, Jean-Christian Imbeault wrote:
> 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.

Is that a problem? ISTM that if you need to keep the DB consistent
during the entire print job, you're going to get pretty poor concurrent
performance regardless.

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

The docs on FOR UPDATE say:

This prevents them from being modified or deleted by other transactions
until the current transaction ends; that is, other transactions that
attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be
blocked until the current transaction ends.

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:

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

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.

-Doug

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.

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?

Jc

On Fri, 28 Feb 2003 19:26:55 +0900, Jean-Christian Imbeault
<jc@mega-bucks.co.jp> wrote:
>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.
> [...]
>Is locking the rows the best (or simplest) solution?

The simplest (and IMHO best) solution is:

    BEGIN;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SELECT <this> ...
    SELECT <that> ...
    SELECT <whatever> ...
    COMMIT;

If your entire report is generated from the result of a single SELECT,
you don't even need that BEGIN/SET TRANSACTION/COMMIT stuff.

Servus
 Manfred

> ordered. But while I gathering the data, which takes more than one
> query, a customer might come along and cancel an invoice.

So what?  Wouldn't you have the same situation if they cancelled
imediately after you finished the report?

Jon

>
> In that case I would print a receipt for something that was cancelled.
>
> 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?
>
> Jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> True but what I am worried about is the data changing while I am in a
> transaction.

Start a serializable-mode transaction, and the data will not change ---
not from your perspective, anyway.  Trying to lock it down to not change
from other people's perspectives seems pointless.

            regards, tom lane

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

But can't the customer cancel that invoice one millisecond *after* your application is done?
As far as I can see, from the customer's standpoint, it will look exactly the same, won't it?

Dima

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

On Fri, 28 Feb 2003, Jean-Christian Imbeault wrote:

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

No need.  Take a look here:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=transaction-iso.html

And read up on serializable, which should do exactly what you want.  I.e.
Even if people are changing the database underneath you, you'll see the
database wholly as if it never changed since your begin; was issued.

Jonathan Bartlett wrote:
 >
 > So what?  Wouldn't you have the same situation if they cancelled
 > imediately after you finished the report?

No. While I gather the data necessary to print a receipt I mark the
invoice as non-cancellable.

In other words, I want to make invoices that have had receipts printed
no longer cancellable.

Jc

Nigel J. Andrews wrote:
 >
 > But then what happens if someone does want to cancel? You're going to
say they
 > can't?

Exactly. Once you have been charged (i.e. an invoice has been printed)
for an item you cannot cancel it. At least that's the way the the
company would like it to be :)

Jc

>
> No. While I gather the data necessary to print a receipt I mark the
> invoice as non-cancellable.
>

Why don't you have two fields - non-cancellable and printed.

In transaction A, you mark everything you want to print as
non-cancellable.

In transaction B, you query for everything that is not printed AND
non-cancellable, and print it, and then update those same records as being
printed.

In addition, you probably want to turn on the maximum isolation level to
fully prevent rewriting records.

Jon

> In other words, I want to make invoices that have had receipts printed
> no longer cancellable.
>
> Jc
>