Admin Version of WITH(UPDLOCK)?

[Jerry Hewett]

Is there anything I can do from the Enterprise Manager console or from
within a JDBC connection to achieve the same effect as WITH(UPDLOCK)?

Yes, I could change all of my SQL statements to include the lock... but
isn't there any way to set or tweak something in SQL Server so that I won't
have to hack a lot of code* to make things concurrent? Perhaps a way to set
UPDLOCK as the default behaviour for the server, or schema, or table, or
something?

Jerry H.

* == The existing SQL has to remain as generic as possible so that it can be
implemented for four other databases.

[Erland Sommarskog]

Jerry Hewett (je******@yahoo.com) writes:

Is there anything I can do from the Enterprise Manager console or from
within a JDBC connection to achieve the same effect as WITH(UPDLOCK)?

Yes, I could change all of my SQL statements to include the lock... but
isn't there any way to set or tweak something in SQL Server so that I
won't have to hack a lot of code* to make things concurrent? Perhaps a
way to set UPDLOCK as the default behaviour for the server, or schema,
or table, or something?

You can use SET TRANSACTION ISOLATION LEVEL to change the default isolation
level for the transaction. However, UPDLOCK does not map to an isolation
level, and in any case I don't think you would want that lock on every
SELECT statement. Maybe you can tell us a little more on what you want to
achieve?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jerry Hewett]

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

Maybe you can tell us a little more on what you want to achieve?

Right now I'd be happy with even a minimal level of concurrency from SQL
Server via multiple JDBC connections. :-(

The code/SQL we're using works like a charm when stress- / load-tested
against multiple users / threads under Oracle, Derby, MySQL, etc. but
deadlocks constantly and consistently under SQL Server.

So far WITH(UPDLOCK) doesn't seem to be doing much. Neither does
selectMethod=cursor. I'm not a DBA, so I figure there must be something I'm
not doing right (or not doing at all).

Either that, or every other SQL database on the planet is a *LOT* more
forgiving and/or better equipped to deal with deadlocks than SQL Server is.
More than happy at this point to be proved completely wrong. ;-)

Jerry H.

[Erland Sommarskog]

Jerry Hewett (je******@yahoo.com) writes:

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

>Maybe you can tell us a little more on what you want to achieve?

Right now I'd be happy with even a minimal level of concurrency from SQL
Server via multiple JDBC connections. :-(

The code/SQL we're using works like a charm when stress- / load-tested
against multiple users / threads under Oracle, Derby, MySQL, etc. but
deadlocks constantly and consistently under SQL Server.

So far WITH(UPDLOCK) doesn't seem to be doing much. Neither does
selectMethod=cursor. I'm not a DBA, so I figure there must be something
I'm not doing right (or not doing at all).

Either that, or every other SQL database on the planet is a *LOT* more
forgiving and/or better equipped to deal with deadlocks than SQL Server
is. More than happy at this point to be proved completely wrong. ;-)

Different DB-engines have different architectures, and what works well on
one engine may not work well on another, even if the code as such is
portable.

Since you did not include any information of what you are actually doing,
it's impossible to assist further.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx