Re: Database Serializable Isolation Level

It depends how you do the update.
In both cases the update should occur if you are just doing update mytable(field1,..) values(v_field1,...) where primary_key=...;

If however you include in the where clause all the values that you read in then the data won't have changed under you (you will get 0 rows updated)

<acf4cga_at_my-deja.com> wrote in message news:8om1e9$qql$1_at_nnrp1.deja.com...
> Hi all,
>
> I have a question on the serializable isolation levels in SQL Server 7
> and Oracle 8i and also databases in general.
> Consider the following scenario under the serializable isolation level:
>
> One thread updates a row in a table and does other processing.
> While this is happening, a second thread tries to update the same row.
> As the first thread has a lock on this row, the second thread blocks.
> Then the first thread completes the transaction and terminates. What
> happens next?
>
> From my experiments, under SQL Server, the second thread can continue
> on happily as the row is now freed. However, under Oracle, the second
> thread will be returned an error stating that the transaction can no
> longer be serialised as the row has been updated by another transaction.
>
> I got the following from the O'Reilly "Oracle Essentials" book, "If the
> transaction commits changes to the data, an operation executing with a
> SERIALIZABLE isolation level will return an error indicating that it
> cannot serialize operations. This error makes sense, because the
> transaction will have changed the state of the data from the beginning
> of the SERIALIZABLE transaction, making it impossible to perform any
> more write operations on the changed rows. In this situation, an
> application programmer will have to add logic to this or her program to
> return to the start of the SERIALIZABLE transaction and begin it again".
>
> Note that no error is returned in SQL Server when the same test is
> executed. Why do these two databases treat the serializable isolation
> level in two different ways? How do other databases such as DB2, etc.
> deal with this?
> Any help is appreciated,
>
> Charles Gamble.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Aug 31 2000 - 22:22:10 CDT