Using MARS with JDBC Driver

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.