SQL Server JDBC - Using MARS with JDBC Driver

Author

Using MARS with JDBC Driver

Wes Clark

2006-01-18, 8:23 pm

Is MARS supported through JDBC? Is the connection the same? Is this
documented anywhere?

Angel Saenz-Badillos[MS]

2006-01-18, 8:23 pm

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@discussions
.microsoft.com> wrote in message
news:9CE9D427-E6A4-42F8-82A9- 023666DB084E@microso
ft.com...
> Is MARS supported through JDBC? Is the connection the same? Is this
> documented anywhere?

Angel Saenz-Badillos[MS]

2006-01-18, 8:23 pm

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@online.microsoft.com> wrote in message
news:eHKsFQJHGHA.3176@TK2MSFTNGP12.phx.gbl...
> 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@discussions
.microsoft.com> wrote in message
> news:9CE9D427-E6A4-42F8-82A9- 023666DB084E@microso
ft.com...
>
>

Jerry Brenner

2006-01-18, 8:23 pm

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@online.microsoft.com> wrote in message
> news:eHKsFQJHGHA.3176@TK2MSFTNGP12.phx.gbl...
>
>
>

Angel Saenz-Badillos[MS]

2006-01-19, 11:23 am

I appreciate the problem

--
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@discussions.microsoft.com> wrote in message
news:74B0663A-32DE-483B-A639- 67E9ADB9A6B8@microso
ft.com...[color=darkred]
>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:
>

Angel Saenz-Badillos[MS]

2006-01-19, 11:23 am

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/d...napshotisol.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@discussions.microsoft.com> wrote in message
news:74B0663A-32DE-483B-A639- 67E9ADB9A6B8@microso
ft.com...[color=darkred]
>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:
>

Jerry Brenner

2006-01-19, 1:23 pm

Snapshot concurrency will help, but it won't solve our problem. Our database
access follows the following rules:
1) All writes are done in the same canonical order to prevent write/write
deadlocks.
2) No reads can occur after the first write to prevent read/write deadlocks
(although we still get them when running against SQL Server. (NOTE: Reads
may occur after acquiring an UPDATE lock, because we know that we have
synchronized with other transactions that could be accessing the same data.)

Our solution to #2 was to retry the SELECT, because we know that the SELECT
will be chosen as the deadlock victim because no writes have occurred in that
transaction. (We also assert that to be the case and we have never found
otherwise.) We get these deadlocks because the canonical order for writes
places foreign key tables after the table with the primary key, while the
optimizer will often choose the foreign key table as the outer table when
doing a foreign key/primary key join. This is where snapshot concurrency
will help us.

There are places in our application where we need to synchronize across app
servers. We do that by acquiring UPDATE locks in the database. The way that
we do that for other databases is to issue a SELECT FOR UPDATE (or the
equivalent syntax) on a unique key, fetch the first row and then leave the
ResultSet open until we are ready to commit the transaction. This gives us
the behavior that we want in other databases, including Oracle. In Oracle,
the SELECT FOR UPDATE will attempt to read the current value, even when
running with snapshot concurrency.

With SQL Server 2000, we have to do an UPDATE instead of a SELECT FOR
UPDATE. Unfortunately, we've found that it is possible for 2 instances of
the same UPDATE to deadlock because of the index locking. We've also seen
cases where 2 instances of the same transaction will deadlock. In those
cases, the transactions were of the form:
1. Acquire UPDATE lock
2. Issue SELECT accessing row that we just locked
....

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.

Using a second connection to acquire the UPDATE lock will not work here,
because it will be in a different lock space. It seems like using snapshot
concurrency may help here, but we would still need to issue an UPDATE to get
the UPDATE lock, instead of a SELECT FOR UPDATE. In that case, we would
still face the risk of having concurrent updates to the same row deadlock.
As I said before, we've eliminated the deadlocks that we were seeing in our
largest application and in our concurrency stess tests, but we get a large
number of deadlocks in our small internal application that is built on the
same framework and that has only a couple of users.

My understanding of the client side caching that you are describing is that
no locks would be maintained from the SELECT FOR UPDATE because the ResultSet
will not be positioned on a row within the server.

Angel Saenz-Badillos[MS]

2006-01-19, 8:23 pm

Jerry,
You obviously have a very complicated scenario and without seeing the code
it would not be possible for me to give you a complete answer. Let me try to
piecemeal it as I can.

> 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 will not affect a driver that fakes MARS by using client side caching.

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

> My understanding of the client side caching that you are describing is
> that
> no locks would be maintained from the SELECT FOR UPDATE because the
> ResultSet
> will not be positioned on a row within the server.
No, no, if you are using server side cursors we will respect them. We
provide full server side cursor granularity in this driver so you can
specify a pessimistic locking behavior for your cursor for example. Client
side caching means that when you execute a row returning query with a
forward only read only client side cursor we will retrieve _all_ the data
from the server to the client instead of streaming it as you navigate the
result set. For example I select top(200)* from customers, on a streaming
model solution I would retrieve the first row and hand it to you while the
TDS buffer is still busy. When you go to do another execute you will not be
able to use this connection so you will need to open another connection
under the covers (FAKE MARS) or multiplex the TDS buffer (MARS).

With the client side caching solution when you select the data we will read
all 200 rows locally and place them in memory. Now when you go to use a
second statement the TDS buffer is not being used and you can work just as
if you were using real MARS.

I hope this clears out some of the confusion about this feature.
--
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@discuss
ions.microsoft.com> wrote in message
news:AA741CE4-E604-4BAC-922E- 648B0DCF161B@microso
ft.com...
> Snapshot concurrency will help, but it won't solve our problem. Our
> database
> access follows the following rules:
> 1) All writes are done in the same canonical order to prevent write/write
> deadlocks.
> 2) No reads can occur after the first write to prevent read/write
> deadlocks
> (although we still get them when running against SQL Server. (NOTE: Reads
> may occur after acquiring an UPDATE lock, because we know that we have
> synchronized with other transactions that could be accessing the same
> data.)
>
> Our solution to #2 was to retry the SELECT, because we know that the
> SELECT
> will be chosen as the deadlock victim because no writes have occurred in
> that
> transaction. (We also assert that to be the case and we have never found
> otherwise.) We get these deadlocks because the canonical order for writes
> places foreign key tables after the table with the primary key, while the
> optimizer will often choose the foreign key table as the outer table when
> doing a foreign key/primary key join. This is where snapshot concurrency
> will help us.
>
> There are places in our application where we need to synchronize across
> app
> servers. We do that by acquiring UPDATE locks in the database. The way
> that
> we do that for other databases is to issue a SELECT FOR UPDATE (or the
> equivalent syntax) on a unique key, fetch the first row and then leave the
> ResultSet open until we are ready to commit the transaction. This gives
> us
> the behavior that we want in other databases, including Oracle. In
> Oracle,
> the SELECT FOR UPDATE will attempt to read the current value, even when
> running with snapshot concurrency.
>
> With SQL Server 2000, we have to do an UPDATE instead of a SELECT FOR
> UPDATE. Unfortunately, we've found that it is possible for 2 instances of
> the same UPDATE to deadlock because of the index locking. We've also seen
> cases where 2 instances of the same transaction will deadlock. In those
> cases, the transactions were of the form:
> 1. Acquire UPDATE lock
> 2. Issue SELECT accessing row that we just locked
> ...
>
> 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.
>
> Using a second connection to acquire the UPDATE lock will not work here,
> because it will be in a different lock space. It seems like using
> snapshot
> concurrency may help here, but we would still need to issue an UPDATE to
> get
> the UPDATE lock, instead of a SELECT FOR UPDATE. In that case, we would
> still face the risk of having concurrent updates to the same row deadlock.
> As I said before, we've eliminated the deadlocks that we were seeing in
> our
> largest application and in our concurrency stess tests, but we get a large
> number of deadlocks in our small internal application that is built on the
> same framework and that has only a couple of users.
>
> My understanding of the client side caching that you are describing is
> that
> no locks would be maintained from the SELECT FOR UPDATE because the
> ResultSet
> will not be positioned on a row within the server.

Jerry Brenner

2006-01-19, 8:23 pm

> > In the cases that we saw (and sent to Microsoft), one of the transactions
>
> 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.

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

Sam Wilson

2006-01-19, 8:23 pm

Perhaps I am missing something, however, have you tried using a SELECT
statement with the (UPDLOCK) hint? That way you don't have to actually change
any data, but you still acquire the update lock. Should be the same as SELECT
FOR UPDATE in oracle, no?

Sam Wilson

"Jerry Brenner" wrote:

> Snapshot concurrency will help, but it won't solve our problem. Our database
> access follows the following rules:
> 1) All writes are done in the same canonical order to prevent write/write
> deadlocks.
> 2) No reads can occur after the first write to prevent read/write deadlocks
> (although we still get them when running against SQL Server. (NOTE: Reads
> may occur after acquiring an UPDATE lock, because we know that we have
> synchronized with other transactions that could be accessing the same data.)
>
> Our solution to #2 was to retry the SELECT, because we know that the SELECT
> will be chosen as the deadlock victim because no writes have occurred in that
> transaction. (We also assert that to be the case and we have never found
> otherwise.) We get these deadlocks because the canonical order for writes
> places foreign key tables after the table with the primary key, while the
> optimizer will often choose the foreign key table as the outer table when
> doing a foreign key/primary key join. This is where snapshot concurrency
> will help us.
>
> There are places in our application where we need to synchronize across app
> servers. We do that by acquiring UPDATE locks in the database. The way that
> we do that for other databases is to issue a SELECT FOR UPDATE (or the
> equivalent syntax) on a unique key, fetch the first row and then leave the
> ResultSet open until we are ready to commit the transaction. This gives us
> the behavior that we want in other databases, including Oracle. In Oracle,
> the SELECT FOR UPDATE will attempt to read the current value, even when
> running with snapshot concurrency.
>
> With SQL Server 2000, we have to do an UPDATE instead of a SELECT FOR
> UPDATE. Unfortunately, we've found that it is possible for 2 instances of
> the same UPDATE to deadlock because of the index locking. We've also seen
> cases where 2 instances of the same transaction will deadlock. In those
> cases, the transactions were of the form:
> 1. Acquire UPDATE lock
> 2. Issue SELECT accessing row that we just locked
> ...
>
> 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.
>
> Using a second connection to acquire the UPDATE lock will not work here,
> because it will be in a different lock space. It seems like using snapshot
> concurrency may help here, but we would still need to issue an UPDATE to get
> the UPDATE lock, instead of a SELECT FOR UPDATE. In that case, we would
> still face the risk of having concurrent updates to the same row deadlock.
> As I said before, we've eliminated the deadlocks that we were seeing in our
> largest application and in our concurrency stess tests, but we get a large
> number of deadlocks in our small internal application that is built on the
> same framework and that has only a couple of users.
>
> My understanding of the client side caching that you are describing is that
> no locks would be maintained from the SELECT FOR UPDATE because the ResultSet
> will not be positioned on a row within the server.

Angel Saenz-Badillos[MS]

2006-01-20, 1:23 pm

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@discuss
ions.microsoft.com> wrote in message
news:D5F5177E-A3C3-44A2-9923- BBE1B7923D3A@microso
ft.com...
>
> 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.
>
>
>
> 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.
>
>

Wes Clark

2006-01-20, 1:23 pm

(Replying for Jerry Brenner) Yes. That’s what we did when running in cursor
mode. We can’t do that in direct mode because we need to keep the ResultSet
open.

Sam Wilson

2006-01-20, 8:23 pm

If you do a BEGIN TRAN before the select (which would be necessary for the
lock to hold until you conduct the update) or if you are running with
autocommit turned off then the locks should be held for the duration of your
connection (regardless of cursor status) or until you commit.

"Wes Clark" wrote:

> (Replying for Jerry Brenner) Yes. That’s what we did when running in cursor
> mode. We can’t do that in direct mode because we need to keep the ResultSet
> open.

Jerry Brenner

2006-01-23, 8:23 pm

You are correct. My understanding of how the locking should work when
running at READ COMMITTED, based on my time at various database vendors, is
that the UPDATE lock should be downgraded to a SHARE lock and then released
when the cursor advances if no positioned update occurred. However, that's
not how SQL Server or Oracle works. (The UPDATE locks are maintained until
the commit.) So I will be changing our code and it looks like we won't need
MARS after all.

Thanks,
Jerry

"Sam Wilson" wrote:
[color=darkred]
> If you do a BEGIN TRAN before the select (which would be necessary for the
> lock to hold until you conduct the update) or if you are running with
> autocommit turned off then the locks should be held for the duration of your
> connection (regardless of cursor status) or until you commit.
>
> "Wes Clark" wrote:
>

Sponsored Links