Re: Using MARS with JDBC Driver

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank you for the info on query plans and server side cursors I will
definitelly have to do some investigation here.

--
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" <JerryBrenner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D5F5177E-A3C3-44A2-9923-BBE1B7923D3A@xxxxxxxxxxxxxxxx
>> > In the cases that we saw (and sent to Microsoft), one of the
>> > transactions
>> > was blocked on the SELECT by another transaction that was blocked on
>> > the
>> > UPDATE. It seemed incredible that a transaction that had just updated
>> > a
>> > row
>> > was blocked trying to read that same row.
>>
>> I assume this is where the MARS discussion came up, the only likely
>> explanation here is that the driver you were using at the time supported
>> fake MARS by using a separate connection under the covers for the second
>> select. Since the new connection does not run under the same transaction
>> context as the first connection you would effectively lock yourself in
>> what
>> appears to be the same connection.
>>
>
> This is not correct. We were using the jdbc driver that came with SQL
> Server 2000. The deadlock was between 2 concurrent instances of the same
> transaction, each using only 1 connection, not a self deadlock. The
> deadlock
> was due to the fact that SQL Server still acquires locks on indexes.
>
>> > With SQL Server 2000, we have to do an UPDATE instead of a SELECT FOR
>> > UPDATE.
>> This should have been similar to executing a resultSet with
>> java.sql.ResultSet.TYPE_FORWARD_ONLY,
>> java.sql.ResultSet.CONCUR_UPDATABLE.
>> You mentioned something about server side cursor not scaling the way you
>> expected, could you elaborate on this? We are actually looking at Oracle
>> compatibility options for future releases of the JDBC driver and one of
>> the
>> options on the table was to convert FOR UPDATE queries in client side
>> statements into server cursor based forward only updatable resultsets
>> under
>> the covers.
>>
>
>
> The problem with SelectMethod=cursor was that we were getting some
> terrible
> query plans. I tried adding the primary key to some of the indexes,
> because
> unique indexes were required for cursors when I worked at Sybase. This
> fixed
> some of the problems, but there were a ton of problems. I undid that
> change
> and switched to SelectMethod=direct and the problems with the bad query
> plans
> went away.
>
>


.

Relevant Pages

  • Re: Sharing an ADO connection object between two processes ?
    ... We want both to participate in the same transaction. ... The server currently supports a COM interface, ... But now they want to pass across the client ADO connection object. ...
    (microsoft.public.vc.atl)
  • Re: Connection.IsolationLevel = adXactSerializable and timeout
    ... get the server to answer that a connection is available. ... lock rows, pages while the transaction is working. ...
    (microsoft.public.data.ado)
  • Re: DataReader and Transactions
    ... They are being done on the server.. ... >> BeginTransactionfunction on my connection object). ... i need to execute more SQL statements. ... >> But then, to keep my transaction processing, i'd have to create a new ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Concurrent Connections / Shared Transaction
    ... bound sessions allow you to share the transaction space among ... > Server 2005 you can mix MARS plus asynchronous command execution to submit ... > within a connection are interleaved, ... >>>> I have two SqlCommands objects that each need to execute concurrently ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL 2000 and SqlTransaction Problem
    ... > SQL Server 2000, but has problems when I try to hit the production SQL ... > both within the transaction and outside of the transaction. ... > same for both the transaction's connection and all the non-transaction ...
    (microsoft.public.dotnet.framework.adonet)