Thread: 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