On Tue, May 13, 2008 at 1:56 PM, Michal Szymanski <dyrex@xxxxxxxxxxxxxx> 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
