READ_UNCOMMITTED problem with SQL 2000 and i-net Opta 2000 JDBC

[neo]

Hi,
I have a problem to set a JDBC connection as READ UNCOMMITED.

setTransactionI solation(Connec tion.TRANSACTIO N_READ_UNCOMMIT TED)

This is causing lots of blocking on tables
and update or insert doesn't work properly.

Does anyone have any idea what I'm missing here?

I asked i-net support and they suggested to call
setAutoCommit(f alse) after the above function
and it didn't work.
also suggested impltrans = true, and I changed that option on SQL
and it caused more blocking.

Thanks in advance for any info.

[Daniel Morgan]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

neo wrote:
[color=blue]
> Hi,
> I have a problem to set a JDBC connection as READ UNCOMMITED.
>
> setTransactionI solation(Connec tion.TRANSACTIO N_READ_UNCOMMIT TED)
>
> This is causing lots of blocking on tables
> and update or insert doesn't work properly.
>
> Does anyone have any idea what I'm missing here?
>
> I asked i-net support and they suggested to call
> setAutoCommit(f alse) after the above function
> and it didn't work.
> also suggested impltrans = true, and I changed that option on SQL
> and it caused more blocking.
>
> Thanks in advance for any info.[/color]

Why are you letting anyone, or any thing, read an uncommited
transaction? If it is not committed ... it doesn't exist.
--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[neo]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

Daniel Morgan <damorgan@x.was hington.edu> wrote in message news:<106912243 4.507152@yasure >...[color=blue]
> neo wrote:
>[color=green]
> > Hi,
> > I have a problem to set a JDBC connection as READ UNCOMMITED.
> >
> > setTransactionI solation(Connec tion.TRANSACTIO N_READ_UNCOMMIT TED)
> >
> > This is causing lots of blocking on tables
> > and update or insert doesn't work properly.
> >
> > Does anyone have any idea what I'm missing here?
> >
> > I asked i-net support and they suggested to call
> > setAutoCommit(f alse) after the above function
> > and it didn't work.
> > also suggested impltrans = true, and I changed that option on SQL
> > and it caused more blocking.
> >
> > Thanks in advance for any info.[/color]
>
> Why are you letting anyone, or any thing, read an uncommited
> transaction? If it is not committed ... it doesn't exist.[/color]

This function sets dirty-read enable.
So even though there is blocking,
with this connection, data can still be read. That's what I want.

[rkusenet]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message news:1069122434 .507152@yasure. ..[color=blue]
> neo wrote:
>[color=green]
> > Hi,
> > I have a problem to set a JDBC connection as READ UNCOMMITED.
> >
> > setTransactionI solation(Connec tion.TRANSACTIO N_READ_UNCOMMIT TED)
> >
> > This is causing lots of blocking on tables
> > and update or insert doesn't work properly.
> >
> > Does anyone have any idea what I'm missing here?
> >
> > I asked i-net support and they suggested to call
> > setAutoCommit(f alse) after the above function
> > and it didn't work.
> > also suggested impltrans = true, and I changed that option on SQL
> > and it caused more blocking.
> >
> > Thanks in advance for any info.[/color]
>
> Why are you letting anyone, or any thing, read an uncommited
> transaction? If it is not committed ... it doesn't exist.[/color]

ah.. this is a typical oracleisque response. Well there are
some circumstances when READ_UNCOMMITTE D makes perfect sense.
For e.g. if u r running report on past data (like last week's report)
which is guaranteed to be read-only at the time of running
the report. why bother about COMMITTED data? a simple dirty
read will do the job as effectively, but more efficiently.

Oracle's MVRC sounds great, but unless it gives an option
to bypass when it is unnecessary, it is an overkill.

rk-

[Daniel Morgan]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

rkusenet wrote:
[color=blue]
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message news:1069122434 .507152@yasure. ..
>[color=green]
>>neo wrote:
>>
>>[color=darkred]
>>>Hi,
>>>I have a problem to set a JDBC connection as READ UNCOMMITED.
>>>
>>>setTransacti onIsolation(Con nection.TRANSAC TION_READ_UNCOM MITTED)
>>>
>>>This is causing lots of blocking on tables
>>>and update or insert doesn't work properly.
>>>
>>>Does anyone have any idea what I'm missing here?
>>>
>>>I asked i-net support and they suggested to call
>>>setAutoCommi t(false) after the above function
>>>and it didn't work.
>>>also suggested impltrans = true, and I changed that option on SQL
>>>and it caused more blocking.
>>>
>>>Thanks in advance for any info.[/color]
>>
>>Why are you letting anyone, or any thing, read an uncommited
>>transaction ? If it is not committed ... it doesn't exist.[/color]
>
>
> ah.. this is a typical oracleisque response. Well there are
> some circumstances when READ_UNCOMMITTE D makes perfect sense.
> For e.g. if u r running report on past data (like last week's report)
> which is guaranteed to be read-only at the time of running
> the report. why bother about COMMITTED data? a simple dirty
> read will do the job as effectively, but more efficiently.
>
> Oracle's MVRC sounds great, but unless it gives an option
> to bypass when it is unnecessary, it is an overkill.
>
> rk-[/color]

Makes sense but if you only do READ_UNCOMMITTE D on old data why not just
partition it?

My concern is that while some might use this as you describe ... others
could just as easily produce invalid reports. There is no protection in
the system to protect the end-user. How do they know?
--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[rkusenet]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC


"Daniel Morgan" <damorgan@x.was hington.edu> wrote
[color=blue]
> Makes sense but if you only do READ_UNCOMMITTE D on old data why not just
> partition it?[/color]

why an oracle like approach for everything :-) U asked a question whether
READ_UNCOMMITTE D has any value, I gave one and I can give many more
examples like this where a particular ISOLATION mode may be appropriate.

Oracle's assumption that its MVRC is best for all situations is plain
horse manure. They should give the flexibility to the developers to
use appropriate ISOLATION mode. I use it in Informix and SQLSERVER and
I am very happy with these products (atleast on this count) which gives
me the flexibility.

[color=blue]
> My concern is that while some might use this as you describe ... others
> could just as easily produce invalid reports. There is no protection in
> the system to protect the end-user. How do they know?[/color]

well, I am using ur own words of wisdon. Learn to work in SQLSERVER as
it is suppose to work, not like Oracle :-). I expect SQLSERVER developrs
to have an understanding of ISOLATION level.

rk-

[Dave Hau]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

"rkusenet" <rkusenet@sympa tico.ca> wrote in message
news:bpdm0b$1nh u6v$1@ID-75254.news.uni-berlin.de...[color=blue]
>
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message[/color]
news:1069122434 .507152@yasure. ..[color=blue][color=green]
> > neo wrote:
> >[color=darkred]
> > > Hi,
> > > I have a problem to set a JDBC connection as READ UNCOMMITED.
> > >
> > > setTransactionI solation(Connec tion.TRANSACTIO N_READ_UNCOMMIT TED)
> > >
> > > This is causing lots of blocking on tables
> > > and update or insert doesn't work properly.
> > >
> > > Does anyone have any idea what I'm missing here?
> > >
> > > I asked i-net support and they suggested to call
> > > setAutoCommit(f alse) after the above function
> > > and it didn't work.
> > > also suggested impltrans = true, and I changed that option on SQL
> > > and it caused more blocking.
> > >
> > > Thanks in advance for any info.[/color]
> >
> > Why are you letting anyone, or any thing, read an uncommited
> > transaction? If it is not committed ... it doesn't exist.[/color]
>
> ah.. this is a typical oracleisque response. Well there are
> some circumstances when READ_UNCOMMITTE D makes perfect sense.
> For e.g. if u r running report on past data (like last week's report)
> which is guaranteed to be read-only at the time of running
> the report. why bother about COMMITTED data? a simple dirty
> read will do the job as effectively, but more efficiently.[/color]

Unless you're running low on memory and getting lock escalation from row to
page lock, I don't see why your past data (assuming you don't update past
data) will have a lock on them. If there's no lock, then why would there be
a difference between doing READ_UNCOMMITTE D and READ_COMMITTED on those
rows. If there's no difference, then why did you say READ_UNCOMMITTE D will
do the job "more efficiently"?

Cheers,
Dave
[color=blue]
>
> Oracle's MVRC sounds great, but unless it gives an option
> to bypass when it is unnecessary, it is an overkill.
>
> rk-
>
>
>[/color]

[Dave Hau]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

One concern I have with uncommitted/dirty read is that documentation says
that "this is the lowest level where transactions are isolated only enough
to ensure that physically corrupt data is not read". This is not enough to
guarantee row-level read consistency, meaning you might not get data of the
same version for all columns, particularly for columns which have BLOB or
CLOB types for which the data is typically stored in another page.

- Dave



"Dave Hau" <nospam_dave_no spam_123@nospam _netscape_nospa m.net_nospam> wrote
in message news:SMvub.3383 0$sk4.10816@new ssvr27.news.pro digy.com...[color=blue]
> "rkusenet" <rkusenet@sympa tico.ca> wrote in message
> news:bpdm0b$1nh u6v$1@ID-75254.news.uni-berlin.de...[color=green]
> >
> > "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message[/color]
> news:1069122434 .507152@yasure. ..[color=green][color=darkred]
> > > neo wrote:
> > >
> > > > Hi,
> > > > I have a problem to set a JDBC connection as READ UNCOMMITED.
> > > >
> > > > setTransactionI solation(Connec tion.TRANSACTIO N_READ_UNCOMMIT TED)
> > > >
> > > > This is causing lots of blocking on tables
> > > > and update or insert doesn't work properly.
> > > >
> > > > Does anyone have any idea what I'm missing here?
> > > >
> > > > I asked i-net support and they suggested to call
> > > > setAutoCommit(f alse) after the above function
> > > > and it didn't work.
> > > > also suggested impltrans = true, and I changed that option on SQL
> > > > and it caused more blocking.
> > > >
> > > > Thanks in advance for any info.
> > >
> > > Why are you letting anyone, or any thing, read an uncommited
> > > transaction? If it is not committed ... it doesn't exist.[/color]
> >
> > ah.. this is a typical oracleisque response. Well there are
> > some circumstances when READ_UNCOMMITTE D makes perfect sense.
> > For e.g. if u r running report on past data (like last week's report)
> > which is guaranteed to be read-only at the time of running
> > the report. why bother about COMMITTED data? a simple dirty
> > read will do the job as effectively, but more efficiently.[/color]
>
> Unless you're running low on memory and getting lock escalation from row[/color]
to[color=blue]
> page lock, I don't see why your past data (assuming you don't update past
> data) will have a lock on them. If there's no lock, then why would there[/color]
be[color=blue]
> a difference between doing READ_UNCOMMITTE D and READ_COMMITTED on those
> rows. If there's no difference, then why did you say READ_UNCOMMITTE D[/color]
will[color=blue]
> do the job "more efficiently"?
>
> Cheers,
> Dave
>[color=green]
> >
> > Oracle's MVRC sounds great, but unless it gives an option
> > to bypass when it is unnecessary, it is an overkill.
> >
> > rk-
> >
> >
> >[/color]
>
>[/color]

[Dave Hau]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

"neo" <second714@hotm ail.com> wrote in message
news:155f8e7d.0 311171747.939d5 34@posting.goog le.com...[color=blue]
> Hi,
> I have a problem to set a JDBC connection as READ UNCOMMITED.
>
> setTransactionI solation(Connec tion.TRANSACTIO N_READ_UNCOMMIT TED)
>
> This is causing lots of blocking on tables
> and update or insert doesn't work properly.[/color]

When compared with READ COMMITTED, did you get more or same or less blocking
using READ UNCOMMITTED? Maybe your updates and inserts by themselves
already generate a lot of blocking, irrespective of your reads. This can
happen say for example if you run low on memory and SQL Server starts
locking pages instead of rows.

Another suggestion I can think of is update your JDBC driver to the latest
version.

HTH,
Dave


[color=blue]
>
> Does anyone have any idea what I'm missing here?
>
> I asked i-net support and they suggested to call
> setAutoCommit(f alse) after the above function
> and it didn't work.
> also suggested impltrans = true, and I changed that option on SQL
> and it caused more blocking.
>
> Thanks in advance for any info.[/color]

[Dave Hau]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

"Dave Hau" <nospam_dave_no spam_123@nospam _netscape_nospa m.net_nospam> wrote
in message news:SMvub.3383 0$sk4.10816@new ssvr27.news.pro digy.com...[color=blue]
> "rkusenet" <rkusenet@sympa tico.ca> wrote in message
> news:bpdm0b$1nh u6v$1@ID-75254.news.uni-berlin.de...[color=green]
> >
> > "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message[/color]
> news:1069122434 .507152@yasure. ..[color=green][color=darkred]
> > > neo wrote:
> > >
> > > > Hi,
> > > > I have a problem to set a JDBC connection as READ UNCOMMITED.
> > > >
> > > > setTransactionI solation(Connec tion.TRANSACTIO N_READ_UNCOMMIT TED)
> > > >
> > > > This is causing lots of blocking on tables
> > > > and update or insert doesn't work properly.
> > > >
> > > > Does anyone have any idea what I'm missing here?
> > > >
> > > > I asked i-net support and they suggested to call
> > > > setAutoCommit(f alse) after the above function
> > > > and it didn't work.
> > > > also suggested impltrans = true, and I changed that option on SQL
> > > > and it caused more blocking.
> > > >
> > > > Thanks in advance for any info.
> > >
> > > Why are you letting anyone, or any thing, read an uncommited
> > > transaction? If it is not committed ... it doesn't exist.[/color]
> >
> > ah.. this is a typical oracleisque response. Well there are
> > some circumstances when READ_UNCOMMITTE D makes perfect sense.
> > For e.g. if u r running report on past data (like last week's report)
> > which is guaranteed to be read-only at the time of running
> > the report. why bother about COMMITTED data? a simple dirty
> > read will do the job as effectively, but more efficiently.[/color]
>
> Unless you're running low on memory and getting lock escalation from row[/color]
to[color=blue]
> page lock, I don't see why your past data (assuming you don't update past
> data) will have a lock on them. If there's no lock, then why would there[/color]
be[color=blue]
> a difference between doing READ_UNCOMMITTE D and READ_COMMITTED on those
> rows. If there's no difference, then why did you say READ_UNCOMMITTE D[/color]
will[color=blue]
> do the job "more efficiently"?[/color]

Never mind. I see - because of the overhead of the single read lock when
you use READ_COMMITTED. My bad.

- Dave

[color=blue]
>
> Cheers,
> Dave
>[color=green]
> >
> > Oracle's MVRC sounds great, but unless it gives an option
> > to bypass when it is unnecessary, it is an overkill.
> >
> > rk-
> >
> >
> >[/color]
>
>[/color]

[rkusenet]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC


"Dave Hau" <nospam_dave_no spam_123@nospam _netscape_nospa m.net_nospam> wrote
[color=blue][color=green]
> > ah.. this is a typical oracleisque response. Well there are
> > some circumstances when READ_UNCOMMITTE D makes perfect sense.
> > For e.g. if u r running report on past data (like last week's report)
> > which is guaranteed to be read-only at the time of running
> > the report. why bother about COMMITTED data? a simple dirty
> > read will do the job as effectively, but more efficiently.[/color]
>
> Unless you're running low on memory and getting lock escalation from row to
> page lock, I don't see why your past data (assuming you don't update past
> data) will have a lock on them. If there's no lock, then why would there be
> a difference between doing READ_UNCOMMITTE D and READ_COMMITTED on those
> rows. If there's no difference, then why did you say READ_UNCOMMITTE D will
> do the job "more efficiently"?[/color]

actually I should have been clearer.
the more efficiently part was in comparison to oracle's MVRC approach,
not a one to one comparison between SQLSERVER RC and RUC approach.

Even within SQLSERVER RC and RUC, RUC is slightly more efficient since
it does not have to put a read lock on a row.

RUC vs MVRC is all together different. In MVRC it has to check for
every page whether the SCN is less than the SCN when the query started,
a totally unnecessary step for the case in question.

[Daniel Morgan]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

rkusenet wrote:

<snipped>[color=blue]
>
> RUC vs MVRC is all together different. In MVRC it has to check for
> every page whether the SCN is less than the SCN when the query started,
> a totally unnecessary step for the case in question.
>[/color]

True. But on the other hand it doesn't have to check to see if the
row is locked. Because in the Oracle world it would be irrelevant.
Writes don't block reads and reads don't brock writes.

All RDBMS's have over-head issues. They are always there ... they are
just different.
--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[rkusenet]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message news:1069192733 .974181@yasure. ..[color=blue]
> rkusenet wrote:
>
> <snipped>[color=green]
> >
> > RUC vs MVRC is all together different. In MVRC it has to check for
> > every page whether the SCN is less than the SCN when the query started,
> > a totally unnecessary step for the case in question.
> >[/color]
>
> True. But on the other hand it doesn't have to check to see if the
> row is locked.[/color]

That is in RC and SER only.
RUC does not check whether the row is locked and for the case in
question, RUC is best.
[color=blue]
> Because in the Oracle world it would be irrelevant.
> Writes don't block reads and reads don't brock writes.[/color]

oracle does not give any flexiblity in writing the application as
required. It forces MVRC always. In Informix I use RC, RUC and
SER as they are required on a case to case basis. Now I follow
the same priciple in SQLServer also.

I am not denying that MVRC is good. All I am disputing is that
it is DA best for every situation, as Oracle arrogantly assumes.
Clearly it isn't.

I believe SQLServer is implementing MVRC in Yukon. That's great,
since they will still be offering RC,RUC and SER. Oracle should also
do that.

rk-

[Daniel Morgan]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

rkusenet wrote:
[color=blue]
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message news:1069192733 .974181@yasure. ..
>[color=green]
>>rkusenet wrote:
>>
>><snipped>
>>[color=darkred]
>>>RUC vs MVRC is all together different. In MVRC it has to check for
>>>every page whether the SCN is less than the SCN when the query started,
>>>a totally unnecessary step for the case in question.
>>>[/color]
>>
>>True. But on the other hand it doesn't have to check to see if the
>>row is locked.[/color]
>
>
> That is in RC and SER only.
> RUC does not check whether the row is locked and for the case in
> question, RUC is best.
>
>[color=green]
>>Because in the Oracle world it would be irrelevant.
>>Writes don't block reads and reads don't brock writes.[/color]
>
>
> oracle does not give any flexiblity in writing the application as
> required. It forces MVRC always. In Informix I use RC, RUC and
> SER as they are required on a case to case basis. Now I follow
> the same priciple in SQLServer also.
>
> I am not denying that MVRC is good. All I am disputing is that
> it is DA best for every situation, as Oracle arrogantly assumes.
> Clearly it isn't.
>
> I believe SQLServer is implementing MVRC in Yukon. That's great,
> since they will still be offering RC,RUC and SER. Oracle should also
> do that.
>
> rk-[/color]

Arrogantly is no more a constructive phrase when referring to MVCC than
would be the same statement with respect to SQL Server's inability to
run on a UNIX platform. Lets leave the hyperbole to the marketing
departments where it belongs.

Then visit this link:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_106a.htm#2067250

and notice the graphic a few inches from the bottom of the first page.
The one that contains SET TRANSACTION. See anything that might make you
reconsider the depth of your knowledge of Oracle?
--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[rkusenet]

Re: READ_UNCOMMITTE D problem with SQL 2000 and i-net Opta 2000 JDBC

"Daniel Morgan" <damorgan@x.was hington.edu> wrote[color=blue]
> Then visit this link:
> http://download-west.oracle.com/docs...6a.htm#2067250
>
> and notice the graphic a few inches from the bottom of the first page.
> The one that contains SET TRANSACTION. See anything that might make you
> reconsider the depth of your knowledge of Oracle?[/color]

I never claimed that I am an expert in Oracle. Indeed I would have worked
less than six months of my career in Oracle. My information about MVRC
is based on what oracle marketing literature.

However it gives me a good idea on the depth of ur knowledge
as a database teacher in Washingto Univ if u make a stupid
and false claim that reading uncommitted data is useless under
all circumstances. I proved it isn't.

Now that we both have insulted each other, can u come to the
point. Does oracle allow applications to bypass MVRC. I could
have gone to the above link, but it requires a registered login.

thanks.


rk-