Обсуждение: Problem with transaction isolation level

Hi,
I develop telecommunication software and I have encountered problem
with isolation level in Postgres.
Our database receive special packets that informs about end of call,
and from time to time (it happens when such packets arrive almost in
the same time e.g 8ms difference) one call  charge user account twice.
Look at following diagram that clarify what exactly happen:

http://gdn.superhost.pl/pub/RozjazdKontWyjasnienie.jpg

I think problem is because we use default Read Commited isolation
level. In presented example value of credit should be changed only if
call_status<>FINS and first transaction after modification of credit
value set call_status to FINS. This should prevent from second
modification of  credit (bacause call_status=FINS), but in our systems
sometimes such protection does not work. I think that between check of
call_status and update of credit is small window that cause that
second transaction cannot see results of first transaction (=second
transaction cannot 'see' that call_status=FINS)

I think that switching to serializable level can fix this issue but we
cannot do this because we afraid of side effects of such strict
isolation level. Second thought is to use explicitly ACCESS EXCLUSIVE
lock on CDR table but does it help?

Michal
http://blog.szymanskich.net

On Tue, May 13, 2008 at 1:56 PM, Michal Szymanski <dyrex@poczta.onet.pl> wrote:
>
> I think problem is because we use default Read Commited isolation
> level. In presented example value of credit should be changed only if
> call_status<>FINS and first transaction after modification of credit
> value set call_status to FINS. This should prevent from second
> modification of  credit (bacause call_status=FINS), but in our systems
> sometimes such protection does not work. I think that between check of
> call_status and update of credit is small window that cause that
> second transaction cannot see results of first transaction (=second
> transaction cannot 'see' that call_status=FINS)
>

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.

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.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

>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