I'm running a heavy SELECT query using WITH (NOLOCK). This still causes other processes trying to INSERT in one of the tables to get blocked.
I thought the locking hint would prevent from blocking other processes?
I need some help to under stand when the right time is for NOLOCK. I work in a small dev group and NOLOCK seams to be a buzz word and others are throwing it in all over for no apparent reason.
I read the thing from http://www.sql-server-performance.com/ and I am sure that our web and SQL servers are about 100x over sized for the application. While are ASP.Net (VB) app may demonstrate some hesitation from time to time I am more inclined to blame poor VB.Net coding techniques before slow SQL. The point being the NOLOCK is being added to SELECTS that are not part of a transaction and were using the SQL data adapter to return datasets or single column values.
Also I am not even sure it’s being used correctly. The OLM has the example:
SELECT au_lname FROM authors WITH (NOLOCK)
However I am seeing it formatted like this:
SELECT au_lname FROM authors (NOLOCK)
I am by no mean an expert, I follow what I read in books or from examples from others. And I have never read in a book go crazy with NOLOCK because it’s the bomb!
Any thoughts? I am trying to learn as much as I can before I raise my hand and say this might be a bad idea.
Thanks
Other than "dirty" reads, what are the cons of using NOLOCK throughout a query?
View 1 Replies View RelatedHi,
I have a job that runs 3 seperate DTS packages.
The first step imports a file and runs successfully.
The second step which is the 2nd DTS package is hanging in the execute mode until I manually stop the job. Apparently,We discovered a bulk insert that is blocking a select statement--both proccesses are within this second DTS package. I tried using the WITH (UNLOCK) on the tables but this DTS package is still failing.
Does anyone have any suggestion? It would be greatly appreciated.
Hi guys,
I am a new user for DB2.
In DB2 V8, i have a table with some no of columns.
table test
col1
col2
col3
col4
Now i want to issue a query like this.
select col1 from test with(NOLOCK) where col1 = <some value>
when i am executing this query on that time i am getting a error message like this...
SQL0158N The number of columns specified for "MT.TEST" is not the
same as the number of columns in the result table. SQLSTATE=42811
Anyone can help me???
i tried to find out.
I did the same thing with SQL server 2000.
I am not facing any problem there.
Thanks & Regards,
Muthu
Paul Randal,
There has been a discussion/debate going on this thread about the benefits and drawbacks of using the NOLOCK hint:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67294
It occurred to me that you might know more about this than any of us, or at least be able to point us to a white paper or knowledge base article that explains the subject in more detail. Any light you can shed on the subject would be a big help.
CODO ERGO SUM
is really helpful use "with (nolock)" sentence in selects??
100% useful?? disadvantages?
best regards
Joseph
Hello,
Does anyone know if you place NOLOCK after a view in a select statement, if the effects trickle down to the tables in the view? Or does one have to add NOLOCK to each table within the view?
Thanks
Hello !!
I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ???? I mean, I don't like to lock any table for selects.
Is It possible ???? How to do It (step by step) ?
Thanks !!
Hey guys,
I came across a SQL statement, thought up by a developer, in which two views were joined with the NOLOCK hint:
SELECT v1.xxx, v2.yyy
FROM dbo.vw_SomeView v1 WITH (NOLOCK)
INNER JOIN dbo.vw_SomeOtherView WITH (NOLOCK) ON v1.id = v2.id
The views are not created the NOLOCK hint. So my question is: has the NOLOCK hint any effect here?
I've looked in the BOL and searched on the net but can't find anything on this particular topic.
Lex
PS. Personally I don't like to use views in JOINs. I've seen too many cases in which tables are joined twice just because they are part of both views. Further more I don't like the "random" use of NOLOCK because most people don't seem to understand the implications of it. But this is besides the point of my question ;)
What will happen if I'm running a query with the NOLOCK hint but the SQL Server has run out of resources?
Thanks!
Hi,
I was sreading about NOLOCK that it could prevent deadlocks but could return data which is not committed yet.
1) Should we use NOLOCK with select statements
2) If the transaction isolation level is set appropriately (e.g. Serializable)in the component (for e.g COM+ component) but NOLOCK is specified in the select then would it return uncommitted data. I mean if the transaction is controlled at hihger level then what will be the Pros and Cons of using NOLOCK.
Thanks
What is the purpose of using WITH(NOLOCK)?
View 3 Replies View RelatedHi!
select * from rubberstock_tbl_shoppingcarttemp
It dosent take datas from the table but
select * from rubberstock_tbl_shoppingcarttemp (nolock)
This querey takes the data from the table
what can i do to retrieve records through first query.I dont know what
is happening please explain and also dont know about nolock
Thanks in advance!
When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. This gives a Dirty Read in a result set.
The data retrieved in the select statement may not be correct since some other process might have updated the data while it was taken in the select statement.
Now, Can I know what is the use of NOLOCK? In which way we can make efficient use of NOLOCK ???
Hi, I get an error when select * from a table with nolock....please help.
Cannot specify an index or locking hint for a remote data source
Hi,
I have never used NOLOCK, or ROWLOCK, or anything of that sort. It's something I thought was over my head when I first encountered it and as I am gaining a better grip on SQL I have decided I want to try and tackle it. Also, because I think its causing me some problems :)
I recently implemented a new query into my application.
The query below gets the COUNT(*) of certain tables. Some of these tables are tall (5 million plus rows)
The counts returned are not absolutely critical information, and we are not joining or doing any other transactions based on this data. I believe running these counts on these pretty active tables (selects,inserts) are possibly causing slowdowns ?
Would using NOLOCK be an appropriate situation for something like this ? I am looking for a performance increase hopefully, and hoping there isnt much of a downside? I'm also not sure what type of reliability to expect from NOLOCK.
Any help much appreciated !!
Thanks once again
mike123
create PROCEDURE [dbo].[select_UserStats_Admin_TEST]
(
@userID int
)
AS SET NOCOUNT ON
DECLARE @Mail_Sent INT, @Mail_Received INT, @Comments_Sent INT, @Comments_Received INT,
@friendsListCount INT
SELECT @Mail_Sent = count(*) FROM tblMessage WHERE messageFrom = @userID
SELECT @Mail_Received = count(*) FROM tblMessage WHERE messageTo = @userID
SELECT @Comments_Sent = count(*) FROM tblComment WHERE CommentFromID = @userID
SELECT @Comments_Received = count(*) FROM tblComment WHERE CommentTo = @userID
SELECT @friendsListCount = count(*) FROM tblFriends WHERE userID = @userID
SELECT @Mail_Sent as Mail_Sent, @Mail_Received as Mail_Received, @Comments_Sent as Comments_Sent,
@Comments_Received as Comments_Received, @friendsListCount as friendsListCount
Hi,Is there a way to catch every select comming to sql and change it toselect with nolock?or how to make database READ UNCOMMITTED permanent?any ideas?Richard
View 3 Replies View RelatedI am getting lot of deadlocks in my application. As it is very complexti avoid deadlocks at this stage of application we have done few stepsto lessen the impact.We have added retries after deadlock is capturted.We have added select * from TABLE with (nolock) wherever possible.But interestingly second step is not working. I have few simple selectstatements where i am using nolock criteria still I am gettingdeadlock victim error. Any idead why it happening. I thought as soonas I put nolock in the query it will ignore all the locks.My sp isCREATE procedure sp_Check_denomination@supply_till_idint,@product_codechar(4),@iso_currency_codechar(3),@denominationmoneyasdeclare @product_id numeric(5)select @product_id = product_id from product with (nolock) whereproduct_code = @product_codeif exists (select *from transaction_inventory TI with (nolock),product_ccy_denom PCD with (nolock)where TI.supply_till_id = @supply_till_idand TI.product_id = @product_idand TI.iso_currency_code= @iso_currency_codeand TI.denomination = @denominationand TI.product_id = PCD.product_idand TI.iso_currency_code = PCD.iso_currency_codeand TI.denomination = PCD.denominationand PCD.product_id=@product_idand PCD.denomination = @denominationand PCD.iso_currency_code=@iso_currency_codeand PCD.tradeable = 1)beginreturn(1)endelsebeginreturn(0)endGO
View 1 Replies View RelatedBackground:I am currently working on a mission critical web based applicationthat is accessed 24 hours a day by users from just about every timezone. We use MS SQL Server as our database and we have lots ofproblems with time-outs. We used to have lots of problems with locksuntil my management decided that we would use the WITH (NOLOCK) hinton EVERY select statement and WITH (ROWLOCK) on EVERY updatestatement. I have argued since the beginning that the NOLOCK hintshould be the exception and not the rule. Meanwhile we continue tohave problems related to time-outs.Problem:I'm the one that they call when there are time-out errors.I am a programmer first and a DBA when I have to be. I'd really liketo hear from some of you who are the opposite. I realize that thereare many factors that contribute to slow response from a databaseserver (indexes, RAM, disk speed, etc.), but what I really need tohear from an expert is whether or not using NOLOCK on **EVERY** queryin a 30GB database that has 344 tables is a bad idea.Thanks in advance,Stephen McMahonJoin Bytes!
View 2 Replies View RelatedIs the NOLOCK optimizer hint being deprecated? Or does this apply only to use of NOLOCK in UPDATE and DELETE statements or what exactly?
I can see that in this article that there are circumstances in which the hint is deprecated; however, I have developers that are acting like NOLOCK is about to be dropped entirely. As far as I know, that is not the case but I would definitely like some feedback on this.
I have been experiencing deadlock errors with two stored procs that I am using.
SP1 is a read query that gets all unprocessed orders (status = 0)
SP2 is an insert query that inserts new orders (Status = 0) uses a transaction.
I have a multithreaded application and once in a while the read query (SP1) will try to read a new row that has just been inserted but not committed yet hence the deadlock arises.
If i use a hint "With(NoLocks)" this will be a dirty read and still read the uncommitted insert from SP2 - is this correct?
Where as if I use hint "With(ReadPast)" this will now only read committed rows and hence the deadlock should not arise - it will not read any uncommitted rows - Correct?
So I think that it is better to use READPAST than NOLOCK. Any orders that have status = 0 not picked up will get picked up on the next round when SP1 is executed again.
Any thougths or suggestions are always appreciated.
Jawahar
Hello...
Is it normal in SQL Server 6.5 the user who only running the query blocking the other user who try to update/add the records?
note: The query is a complex SQL.
Many Thanks!
We are using SQL server 6.5 and currently have about 100 users connections at a given
point in time. The application is Visual Basic 5.0 based and it allows users to create
MS Word documents from the application. These documents names are stored in a table which basically
acts as a reference table. Every time a document needs to be attached to a record
this table is called with an insert/update query. This results in an exclusive page/table
lock and ends up into a blocked process. This finally results into a major halt for
all the system users.
Manual killing of these blocked processes frees up the resources and brings things back to normal
although disruptive to the users.
Any clue as to why the blocked processes are not able to free themsevles up ?
Are we missing something in our SQL configuration that will help us with unblocking
these processes ?
Hi,
Issuing 'dbcc shrinkfile (log ,truncateonly)'
I get the error message below. Books online doesn't say any more.
Can anyone explain? I am the only user connected to the db at the time, no jobs are executing.
Cannot shrink log file 2 (log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
What is SPID -1? I see nothing listing it in any activity monitor, but it has just recently started blocking some processes.
Thanks,
jim craddock
I am getting the following error message when I tried installing the 2005 Developer Edition from the MSDN kit. I previously installed the 2005 Express Edition, and it worked just fine, so I thought that the install failed as it was at a lower version than the Express Edition. I uninstalled the SQLExpress Edition, however, when I try to install SQL Server 2005 again, I get the following message:
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
I have also installed Visual Studio NET, and I wonder if some component in the Studio app is holding back the SQL Server install. I have uninstalled all SQL components several times already, and I keep on getting this message.
Gregory
Gregory
Hi all,
I have an SQL Server 2005 mirroring config with "High availability". Twice over the past four weeks, the principal server started logging to the event log:
"All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT" I checked the database activity monitor and indeed there were lots of processes in DBMIRROR_DBM_EVENT.
The database was totally unresponsive to queries (all got timeouts), and no failover was done.
This are the log entries for all day:
05/04/2008 08:40:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:39:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:38:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:37:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:36:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:35:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:33:49,Server,Unknown,The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
05/04/2008 03:24:02,Backup,Unknown,Log was backed up. Database: TeleCable<c/> creation date(time): 2007/08/24(13:38:00)<c/> first LSN: 205089:18926:1<c/> last LSN: 209688:18463:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'\face01f$logbackup'}). This is an informational message only. No user action is required.
05/04/2008 00:00:14,spid24s,Unknown,This instance of SQL Server has been using a process ID of 1700 since 4/9/2008 3:18:54 AM (local) 4/9/2008 1:18:54 AM (UTC). This is an informational message only; no user action is required.
After that, the same entry once per minute.
I restarted the database and everything came to normal.
As you can imagine, the problem is really serious.
Should I forget about mirroring? Anyone has experienced something similar?
Thanks in advance and best regards.
Hi all
If i have a view:
CREATE VIEW vw_Users
AS
SELECT * FROM Users WITH(NOLOCK)
Is it suggested to use nolock in views?
And if i needed to use this view in stored procs is it then suggested to apply the nolock hint?
CREATE PROC [dbo] .[usp_GetCompanyUsers]
AS
SELECT * FROM Companies WITH(NOLOCK) JOIN
vw_Users WITH(NOLOCK) --<< ---is this suggested?
Is it possible to use With (NOLOCK) and With (READPAST) in the sameSELECT query and what whould be the syntax?@param intSELECTmyRowFROMdbo.myTable WITH (NOLOCK)WHEREmyRow = @paramThanks,lq
View 6 Replies View RelatedI have a Store Procedure on a Sql Server 2000 Where I use the Table Hint"NoLock" on all selects.One of my clients (OleDbConnection from C#) doesn't get the same Result Setas the others. The result Set should have 31 rows but this client only gets5!When I remove all the "NoLocks" everything works fine. How can that be?
View 5 Replies View RelatedI get the following error when installing SQL Server Express SP2 on Windows XP SP2 with all current updates. The only other thing installed is .NET 2.0. Does anyone know how to resolve the issue.
[Microsoft][SQL Native Client][SQL Server]Could not continue scan with NOLOCK due to data movement
Thanks,
Hi!
when WITH (NOLOCK) statement is used in a select statement, it allows to retrieved data from a set of data locked by another transaction. Is it possible to enforce this at connection level?
I mean to open a connection using sqlclient and enforce NOLOCK so that all select statements post to database using that connection are not locked by other transactions.
thanks in advance