As per the definition: Serializable isolation prevents: dirty read,
nonrepeatable read, and phantom read.


Using MS SQL query analyzer, I have two windows and

I excute as follows.

Window1 (w1)
start tran

w2
start tran

w1
set transaction isolation level serializable

w2
set transaction isolation level serializable


w1
select * from sometable where id = 5

w2
select * from sometable where id = 5
( as per my understanding ) this second select should not go through.
But the second select is able to read the data. It doesn't block!!!

I get the same result with IBM db2 also.

Am I missing something..here.???

This is a problem I am finding using EJB and it would really help if
someone could explain the gap to me.

Thanks in advance for your help.
Misos

"misos" <misos8 (AT) yahoo (DOT) com> wrote

I'm not sure if MS SQL conforms to the ISO standard for serializable, but
typically reads don't block reads in a serializable schedule. If w1 was a
write/update operation, it would block w2.

- Dan

"Dan" <guntermann (AT) verizon (DOT) com> wrote


I don't know the ISO standard, but what Dan described is exactly the way
DEC Rdb/VMS worked, from some 20 years ago onward. This is how Rdb worked if
both w1 and w2 were locking transactions.

In addition, DEC Rdb/VMS had a special kind of read only transaction called
a "snapshot" transaction. In a snapshot read only transaction, updaters
maintain a before image of the update, tagged so that the snapshot
transaction will use the data as it looked at the time of the snapshot. In
Dan's scenario, w2 would not be blocked by the operation of w1, but it
would be limited to seeing the data prior to w1's action.

It's my understanding that snapshots are serializable.

Hello,

Using Weblogic 6.1, CMP , I am setting the isolation level to
"serializable" and concurrency strategy to "Database" but when two
transactions are happening as:

Transaction 1 ( t1)
does a select on a row

Transaction 2 (t2)
does a select on the same row ( this select is going through )


The reason begin, most of the database ( currently I am using MS
SQLServer 2000 and db2 ) , they use optimistic locking even if we
specify serializable, where by only if we try to excute UPDATE they
goes into a deadlock and one of them will proceed and the other to
fail as deadlock victim.

With Weblogic 6.1 , is there anyway we can prevent the second select
from going through. ( meaning it should go into a blocking state and
shouldn't get any lock ...something like as if the SELECTs were done
using "SELECT ...FOR UPDATE") ?

Help !!!!


"Dan" <guntermann (AT) verizon (DOT) com> wrote

"misos" <misos8 (AT) yahoo (DOT) com> wrote

level.

Try this:

Transaction 1 (t1)
does an update to set of tuples.

Transaction 2 (t2)
Tries to read a subset of the tuples updated in Transaction 1. Will block.

Commit Transaction 1;
Commit Transaction 2;

I'm not familiar with optimistic *locking* algorithms; I only have exposure
to optimistic time-ordering concurrency control algorithms; so I can't be of
help here.

where by only if we try to excute UPDATE they
Yes, the trade-off for getting the highest level of isolation possible while
still allowing for some degree of interleaving of transactions.

I get the sense that what you really desire is a SERIALIZED execution of
transactions, but I don't know why. You could explicitly lock the table, or
perhaps there is an analogous SQL Server extension that does the same thing
as the Oracle propietary extension you are referring to. Have you asked
your question within the MS-SQL Server usenet forum yet?

I'd be interested to hear whether a similar, pardon my use of the term, hack
exists in the newer versions of SQL Server.

[snip]

- Dan

"Laconic2" <laconic2 (AT) comcast (DOT) net> wrote

Very interesting. They had something similar to multiversion time-ordering
algorithms implemented in DEC Rdb 20 years ago? I was not aware of that.

"Dan" <guntermann (AT) verizon (DOT) com> wrote


It may in fact be more than just "similar" to multiversion time-ordering
algorithms.
To the extent that I understand the latter, I would guess that DEC Rdb
snapshots was "an implementation of" that algorithm.

The tricky part was "time ordering". Read Write transactions are assigned a
serial number at transaction start time,
but the time ordering is based on commit time. the transaction serial
number (TSN) is almost time ordered, but not quite.
Eash transaction had to maintain a tiny bitmap of transactions to account
for the difference.

DEC had real good engineering.
DEC had "symmetric multi processing" years before the world heard of it.
DEC had ethernet before most people had heard of modems.
DEC had a world wide network of its own, before Al Gore invented the
internet. ;-)
DEC had an OS that you could leave up and running for years at at time
before MS-DOS could handle a print job in the background.

If only DEC had not had "field of dreams" marketing strategy. 732777964.