Re: Using MARS with JDBC Driver

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?
> >
> >
>
>
>
.

Relevant Pages

  • Re: Using MARS with JDBC Driver
    ... I am not really sure why you would expect MARS to be ... Since you mention that you are using Sql Server 2005 take a look a this: ... This alias is for newsgroup purposes only. ... > relase at a TechNet program, was supported in the jdbc driver. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Catching SQL requests by newly developed service
    ... "DBMS-based drivers act as the client in a client/server configuration where ... which sits between the driver and data source. ... gateway is a piece of software that causes one DBMS to look like another. ... applications written to use SQL Server can also access DB2 data ...
    (microsoft.public.sqlserver.programming)
  • Re: Incoming TDS RPC Protocol Stream Incorrect Error for SQL Server 2005
    ... If all you are doing is simple JDBC access, all of your configuration takes place on the client. ... Incoming TDS RPC Protocol Stream Incorrect Error for SQL Server 2005 ... Would there driver need to be set with a clientpath on ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Using MARS with JDBC Driver
    ... This alias is for newsgroup purposes only. ... > first started on SQL Server 2000, we were running in cursor mode and did ... > able to scale to that level without MARS. ... >> To be clear I am talking about the MICROSOFT SQL SERVER 2005 JDBC DRIVER ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: perl DBI on windows 64
    ... This is a limitation of the SQL server protocol, ... For the MS SQL Server, you have to use several distinct connections if you need parallel active statements. ... Multiple Active Result Sets (MARS), enables your applications to have more than one pending request per connection, and, in particular to have more than one default result set open per connection. ...
    (perl.dbi.users)
Loading