Re: Using MARS with JDBC Driver
- From: "Angel Saenz-Badillos[MS]" <angelsa@xxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 19 Jan 2006 09:16:50 -0800
I am sorry the message got submitted mid post.
As I was saying, I appreciate the problem, without having looked at your
specific application I can only adress it in general terms as something
along the lines that Oracle transaction isolation level behaves differently
from Sql Server 2000. I am not really sure why you would expect MARS to be
the solution to this problem, while it is true that by using MARS you can
work arround this problem (with a large product rewrite to ensure everything
is running under the same connection) the fake MARS behavior in the Jdbc
driver would work just as well (or as badly).
Since you mention that you are using Sql Server 2005 take a look a this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp
It was _designed_ to do exactly what you are trying to do, emulate the
transaction lock behavior of Oracle _while in a transaction_. You should be
able to get your application working on snapshot level with minor or no
rewrites depending on your current locking story.
I would be interested in finding out more about why you believe that MARS
would work for you in this scenario, there is probably something very
obvious that I am missing. Keep in mind however that for all purposes the
fake MARS we have implemented will work exactly like MARS as long as you are
able to client side cache the resultsets you retrieve, you can even expand
the jvm memory size with the xmx flag to work arround even medium large
resultsets (though I would not recommend this necesarily)
--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"Jerry Brenner" <Jerry Brenner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:74B0663A-32DE-483B-A639-67E9ADB9A6B8@xxxxxxxxxxxxxxxx
>I work with Wes. I've been led to believe by various people from
>Microsoft,
> prior to the release, that MARS would be supported and then, after the
> relase at a TechNet program, was supported in the jdbc driver. This is
> the
> #1 issue that is driving us to SQL Server 2005 because the lack of this
> feature is limiting our ability to scale.
>
> We need to synchronize across multiple app servers, so we do this by
> getting
> update locks in the database. In Oracle, we do a SELECT FOR UPDATE. When
> we
> first started on SQL Server 2000, we were running in cursor mode and did
> the
> equivalent. Once we started doing performance work, we saw that we needed
> to
> change to direct mode. At that point, I changed the code to do an UPDATE
> for
> an update lock when running against SQL Server. We have a number of
> concurrency stress tests. We were getting deadlocks in those tests
> between 2
> searched updates on the same row because of the locking on index rows. I
> have a friend who is an architect in the SQL Server group. I twice had to
> go
> to him and then to a developer that works on the locking code to come up
> with
> a solution. (The solution was to index a certain way and to use
> HOLDLOCK.)
> The problems in our concurrency stress tests got resolved after the 2nd
> pass.
> However, we now have similar problems in an internal application that only
> has a couple of users.
>
> Given the continued problems, we are trying to move to SQL Server 2005 in
> preparation for our next release. Our biggest customer on SQL Server is
> planning to go live on our next release and we are concerned that we won't
> be
> able to scale to that level without MARS. (We have no problem scaling to
> that level with Oracle.) Furthermore, based on our experience with the
> small
> internal app, we are concerned that some new functionality could get
> introduced into the larger app that will change the access patterns and
> cause
> the deadlocks to emerge again.
>
> The work arounds that you suggest won't work for us. Is there a projected
> timeframe for a solution?
>
> "Angel Saenz-Badillos[MS]" wrote:
>
>> To be clear I am talking about the MICROSOFT SQL SERVER 2005 JDBC DRIVER
>>
>> --
>> Angel Saenz-Badillos [MS] DataWorks
>> This posting is provided "AS IS", with no warranties, and confers no
>> rights.Please do not send email directly to this alias.
>> This alias is for newsgroup purposes only.
>> I am now blogging: http://weblogs.asp.net/angelsb/
>>
>>
>>
>>
>> "Angel Saenz-Badillos[MS]" <angelsa@xxxxxxxxxxxxxxxxxxxx> wrote in
>> message
>> news:eHKsFQJHGHA.3176@xxxxxxxxxxxxxxxxxxxxxxx
>> > Wes,
>> > Mars is not supported with the v1.0 driver, it is a feature that we are
>> > looking to implement in a later release (given how extremely hard this
>> > is
>> > to implement and the poor bang for the buck this feature gives it will
>> > probably be implemented post v1.1).
>> >
>> > As per JDBC spec MARS like behavior is supported in this driver, (the
>> > driver will behave as if MARS is enabled thanks to some under the
>> > covers
>> > "magic") The two accepted ways to do this in the JDBC space are to
>> > either
>> > create a new connection under the covers whenever you ask for a second
>> > resultset or to client side cache the data from your entire resultset
>> > so
>> > that the tds buffer is free and available for a second resultset at any
>> > time. For this driver and keeping in mind that Pooling will be mostly
>> > supported by application servers we decided against opening a second
>> > connection (this basically kills your pooling story) and opted for
>> > client
>> > side caching.
>> >
>> > The obvious problem with client side caching is that it becomes really
>> > inneficient to retrieve large resultsets, in extreme cases you may
>> > actually run out of JVM memory! The workarround is to retrieve smaller
>> > ammounts of data (always a good recomendation) or when this is not
>> > feasible to use server side cursors. When you use server side cursors
>> > you
>> > only client side cache the specified (fetchsize) number of rows. We are
>> > providing a very granular server side cursor story in this driver.
>> >
>> > I hope this helps, let me know if you have any specific comments or
>> > concerns.
>> >
>> > --
>> > Angel Saenz-Badillos [MS] DataWorks
>> > This posting is provided "AS IS", with no warranties, and confers no
>> > rights.Please do not send email directly to this alias.
>> > This alias is for newsgroup purposes only.
>> > I am now blogging: http://weblogs.asp.net/angelsb/
>> >
>> >
>> >
>> >
>> > "Wes Clark" <WesClark@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> > news:9CE9D427-E6A4-42F8-82A9-023666DB084E@xxxxxxxxxxxxxxxx
>> >> Is MARS supported through JDBC? Is the connection the same? Is this
>> >> documented anywhere?
>> >
>> >
>>
>>
>>
.
- References:
- Re: Using MARS with JDBC Driver
- From: Angel Saenz-Badillos[MS]
- Re: Using MARS with JDBC Driver
- From: Angel Saenz-Badillos[MS]
- Re: Using MARS with JDBC Driver
- From: Jerry Brenner
- Re: Using MARS with JDBC Driver
- Prev by Date: Re: Using MARS with JDBC Driver
- Next by Date: Re: Using MARS with JDBC Driver
- Previous by thread: Re: Using MARS with JDBC Driver
- Next by thread: Re: Using MARS with JDBC Driver
- Index(es):
Relevant Pages
|
Loading
