|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
INSERT ... NOLOCKHi,
Our company policy for UPDATE / SELECT operation is as follows. For both of these operations we have to put NOLOCK hint. Nut i know that the 'SELECT' operation with 'NOLOCK' hint will return the uncommitted value. So my question is, can we avoid this by using 'NOLOCK' hint for insert operation s too? But when i tried this on my machine, the query is giving some syntax error. can anyone give me an example of Insert with nolock hint. INSERT into <table> WITH NOLOCK VALUES .. regards Lara NOLOCK will always give you uncommitted data. Not sure what you mean by
"avoiding" the same. Any reason why SELECT's and UPDATE's should have NOLOCK as per your policy? -- Show quoteHTH, SriSamp Email: sris***@gmail.com Blog: http://blogs.sqlxml.org/srinivassampath URL: http://www32.brinkster.com/srisamp "lara169" <lara***@gmail.com> wrote in message news:OmNQt%233BGHA.2616@TK2MSFTNGP10.phx.gbl... > Hi, > > Our company policy for UPDATE / SELECT operation is as follows. For both > of > these operations we have to put NOLOCK hint. Nut i know that the 'SELECT' > operation with 'NOLOCK' hint will return the uncommitted value. So my > question is, can we avoid this by using 'NOLOCK' hint for insert operation > s > too? But when i tried this on my machine, the query is giving some syntax > error. > > can anyone give me an example of Insert with nolock hint. > > INSERT into <table> WITH NOLOCK > VALUES .. > > regards Lara > > I'm sorry, for Update we use ROWLOCK,
What i am asking is that if we apply the ROWLOCK hint to Insert, then it wint return the uncommitted data. right ? But whenever i tried to use this in INSERT statement, its giving systax error, can anyone give me an example ? regards Lara Show quote "SriSamp" <ssamp***@sct.co.in> wrote in message news:%23ZGcsE4BGHA.344@TK2MSFTNGP11.phx.gbl... > NOLOCK will always give you uncommitted data. Not sure what you mean by > "avoiding" the same. Any reason why SELECT's and UPDATE's should have NOLOCK > as per your policy? > -- > HTH, > SriSamp > Email: sris***@gmail.com > Blog: http://blogs.sqlxml.org/srinivassampath > URL: http://www32.brinkster.com/srisamp > > "lara169" <lara***@gmail.com> wrote in message > news:OmNQt%233BGHA.2616@TK2MSFTNGP10.phx.gbl... > > Hi, > > > > Our company policy for UPDATE / SELECT operation is as follows. For both > > of > > these operations we have to put NOLOCK hint. Nut i know that the 'SELECT' > > operation with 'NOLOCK' hint will return the uncommitted value. So my > > question is, can we avoid this by using 'NOLOCK' hint for insert operation > > s > > too? But when i tried this on my machine, the query is giving some syntax > > error. > > > > can anyone give me an example of Insert with nolock hint. > > > > INSERT into <table> WITH NOLOCK > > VALUES .. > > > > regards Lara > > > > > > lara169 (lara***@gmail.com) writes:
> I'm sorry, for Update we use ROWLOCK, It's getting confusing, are you using NOLOCK or ROWLOCK? Those are two> What i am asking is that if we apply the ROWLOCK hint to Insert, then it > wint return the uncommitted data. right ? > But whenever i tried to use this in INSERT statement, its giving systax > error, can anyone give me an example ? different ones. NOLOCK means that you read data without locking it, and you don't care if data is committed or not. May be good for a report that gives an overview of trends. Dead wrong for a report that the accounting people use for reconcilliation. NOLOCK is mainly useful with SELECT statements. ROWLOCK instructs SQL Server to use row locks rather than locks of higher granularity. Since SQL 7, row lock is the default locking mechanism, and there is rarely any reason to use this hint. If you experience table locks when you expected rowlocks with your UPDATE statement, you probably need to review your indexing. I'm not sure what you want to achieve with ROWLOCK on an INSERT statement? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > But whenever i tried to use this in INSERT statement, its giving systax You cannot use the nolock hint with an insert statement AND your syntax is > error, can anyone give me an example ? not valid. At the very least, please look up basic syntax questions in BOL. Use the READPAST locking hint in your SELECT statements (where applicable) to
skip locked (uncommitted rows). More on hints: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_03_8upf.asp There's a bug in MS SQL 2000 (Standard) when using the READPAST hint on a table without a clustered index, so keep that in mind. http://support.microsoft.com/default.aspx?scid=kb;en-us;297466 ML --- http://milambda.blogspot.com/ Thanks
"ML" <M*@discussions.microsoft.com> wrote in message http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_03_8upf.aspnews:D1318C1C-1933-4F85-B88A-0EA858A9C112@microsoft.com... > Use the READPAST locking hint in your SELECT statements (where applicable) to > skip locked (uncommitted rows). > > More on hints: > Show quote > > There's a bug in MS SQL 2000 (Standard) when using the READPAST hint on a > table without a clustered index, so keep that in mind. > http://support.microsoft.com/default.aspx?scid=kb;en-us;297466 > > > ML > > --- > http://milambda.blogspot.com/ Frankly, the problem is not clear.
But, I will try to answer what I have understood. As far as INSERT or UPDATE statements are concerned, in any case the affected row(s) in the table we are inserting/updating would be locked... no way to avoid this. But, yes it is possible to have NOLOCK/ROWLOCK with the tables in the FROM clause, that way ensuring the qualifying rows in the tables in the FROM clause are not locked or locked the way we want to handle... - R Show quote "lara169" wrote: > Hi, > > Our company policy for UPDATE / SELECT operation is as follows. For both of > these operations we have to put NOLOCK hint. Nut i know that the 'SELECT' > operation with 'NOLOCK' hint will return the uncommitted value. So my > question is, can we avoid this by using 'NOLOCK' hint for insert operation s > too? But when i tried this on my machine, the query is giving some syntax > error. > > can anyone give me an example of Insert with nolock hint. > > INSERT into <table> WITH NOLOCK > VALUES .. > > regards Lara > > > NOLOCK will make your queries return incorrect results at lightning speed.
Even worse, using NOLOCK--or even READ COMMITTED--when calculating values for an INSERT or UPDATE will allow garbage to be introduced into the database. If the database contains garbage or if the database returns garbage, then what's the point of having the database at all? Whoever promulgated the company policy probably doesn't understand this and should get a book or go back to school. Brian S. Selzer, MCDBA, MCSE+I Show quote "lara169" <lara***@gmail.com> wrote in message news:OmNQt%233BGHA.2616@TK2MSFTNGP10.phx.gbl... > Hi, > > Our company policy for UPDATE / SELECT operation is as follows. For both > of > these operations we have to put NOLOCK hint. Nut i know that the 'SELECT' > operation with 'NOLOCK' hint will return the uncommitted value. So my > question is, can we avoid this by using 'NOLOCK' hint for insert operation > s > too? But when i tried this on my machine, the query is giving some syntax > error. > > can anyone give me an example of Insert with nolock hint. > > INSERT into <table> WITH NOLOCK > VALUES .. > > regards Lara > >  |
|||||||||||||||||||||||
Other interesting topics