Re: Problem with transaction isolation level

On Tue, May 13, 2008 at 1:56 PM, Michal Szymanski <dyrex(at)poczta(dot)onet(dot)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