Problem with transaction isolation level

From: Michal Szymanski <dyrex(at)poczta(dot)onet(dot)pl>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with transaction isolation level
Date: 2008-05-13 08:26:20
Message-ID: c5a7b85d-13ed-459d-9692-cc67098bd170@24g2000hsh.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hiroshi Saito 2008-05-13 08:32:59 Re: Compiling trigger function with MinGW
Previous Message Martijn van Oosterhout 2008-05-13 08:09:13 Re: change encoding