Re: Problem with transaction isolation level

>IMHO more information is needed, especially regarding how and when do
>you change the call_status value in other parts of your code. For
>example, if some other transaction is setting call_status to something
>other than FINS and that transaction commits before the second UPDATE
>comes out of wait, then the second UPDATE would execute successfully.

I'm sure that there are only one transaction for one packet handling
(we had info about it in postgres log) and we use only one procedure
to handle a billing packets.

> I don't think Read Committed isolation level is at fault here, unless
> we are looking at some bug. The way it works is the second UPDATE
> would wait for the first transaction to either commit or abort. In
> this case, when the first transaction commits, the second UPDATE will
> re-fetch the latest committed-good copy of the row and re-apply the
> WHERE clauses before proceeding with the UPDATE operation. Since the
> latest committed-good copy has call_status set to FINS, the second
> UPDATE won't update anything.

I think the problem is that UPDATE also read CDR table (I have feeling
that CDR table is locked only for update not for select) and there is
very narrow window between select and update that cause problem. . At
the begining of transaction
procedure reads CDR and I've added FOR UPDATE:
SELECT * INTO v_cdr FROM cdr WHERE cdr_id=i_cdr_id FOR UPDATE;

and after such select row with following i_cdr_id is locked and
second transaction wait even with select.
Now problem is solved , at least I've not recorded situation when
account is charged twice.

Thank you for your help.
Michal Szymanski
http://blog.szymanskich.net