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
