December 16, 2004 at 4:36 pm
Our product supports SQLServer as one of the three databases. I'm a programmer that writes queries to gather data from database. The database guy in our team mandated that every SQL query must append WITH NOLOCK after each table name, for performance reason. After a close look at the JDBC API(specifically, Connection interface) and read an article from MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp), I believe by calling connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED) does the same thing as using NOLOCK in the query(i.e., hint SQL server not to use lock). I also tested with a few select queries and it seems to be in accordance to my assumption.
Is there any reference or anyone who can back up my findings? It'll save me a lot because right now I have to append the "NOLOCK" for tons of queries for MS-SQL server but not others.
Thanks in advance!
December 17, 2004 at 1:53 am
From BOL :
"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction"
The main difference is, that isolation level remains valid for the connection until you change it explicitly (or until the connection is closed). If you plan to perform actions that require locks later, in the same connection, you must remember to reset isolation level. As long as you don't mind about dirty reads in any of the statements during the entire connection "life", there is no reason not to use Read Uncommitted setting. Read BOL (=SQL Server Books Online) to find out more about locking.
HTH, Vladan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply