select holding 1000's of RID IX locks !?!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

select holding 1000's of RID IX locks !?!

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2002-02-26 : 10:44:37

Hi all,

I have run into a strange (imho) problem/issue.

On SQL 7.0 SP2 ( on NT4 SP5), we have a select statement :
***
Select top 1 document.document as document2,
recordid as recordid2,
userid as userid2,
planno,
*
from document
where document is not null and
datalength(document) <> 0 and
clientid <> 0 and
RECORDID not in (select RECORDID from DocumentOnDemand)
***
Which is taking IX locks on RID from the document table. This table is a 78
GB table. The document coumn is of data type IMAGE - when I exclude it's
criteria, the query seems significantly faster, but has the same kind of
locking.

An example of the locking information that I have extracted from the
syslockinfo table, in conjunction with sysprocesses,is:

UserId SQL_LoginName process_id objectname resource_type method_of_lock lock_request_status blocked login_time
last_batch_time hostname program_name
datetime_checked server_checked

-------- -------------------- ---------- ------------------------------ ------------- -------------- ------------------- ------- ------------------------------------------------------

------------------------------------------------------ --------------- ------------------------------ ------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------
sa 3869 DocumentOnDemand table S granted 0 2002-02-26
11:57:54.847 2002-02-26 12:07:26.253 IBM07767 SQL Query Analyzer
2002-02-26 12:08:16.097 YPFSHODB003
sa 3869 DOCUMENT table S granted 0 2002-02-26
11:57:54.847 2002-02-26 12:07:26.253 IBM07767 SQL Query Analyzer
2002-02-26 12:08:16.097 YPFSHODB003
sa 3869 DOCUMENT RID IX granted 0 2002-02-26
11:57:54.847 2002-02-26 12:07:26.253 IBM07767 SQL Query Analyzer
2002-02-26 12:08:16.097 YPFSHODB003
sa 3869 DOCUMENT RID IX granted 0 2002-02-26
11:57:54.847 2002-02-26 12:07:26.253 IBM07767 SQL Query Analyzer
2002-02-26 12:08:16.097 YPFSHODB003
sa 3869 DOCUMENT RID IX granted 0 2002-02-26
11:57:54.847 2002-02-26 12:07:26.253 IBM07767 SQL Query Analyzer
2002-02-26 12:08:16.097 YPFSHODB003
sa 3869 DOCUMENT page S granted 0 2002-02-26
11:57:54.847 2002-02-26 12:07:26.253 IBM07767 SQL Query Analyzer
2002-02-26 12:08:16.097 YPFSHODB003
sa 3869 DOCUMENT page S granted 0 2002-02-26
11:57:54.847 2002-02-26 12:07:26.253 IBM07767 SQL Query Analyzer
2002-02-26 12:08:16.097 YPFSHODB003
sa 3869 DOCUMENT page S granted 0 2002-02-26
11:57:54.847 2002-02-26 12:07:26.253 IBM07767 SQL Query Analyzer
2002-02-26 12:08:16.097 YPFSHODB003

The query was running with a SPID of 3869. We have MANY 1000's of RID locks
- but they are IX ! why - this is purely a select!! I would expect S and
IS locks.

any help/thoughts?

I can't seem to find any query hints that will help, but I don't have any
experience with them ...

TIA

Nazim
A custom title

1408 Posts

Posted - 2002-02-26 : 10:52:57

About the query hint you can use

Eg:

select * from tables with (nolock)

i would suggest you to go thru this website's faq on storing images. its not a good idea storing a image in your DB.

--------------------------------------------------------------

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2002-02-27 : 05:16:50

Thanx - nolock does remove the locks - no I just need to tune the D@#$@# thing :-)

still not clear why a select causes IX locks - I'll try and find the discussion thread you mentioned ...

As to using images - it's a packaged solution - we didn't get a say. We are setting up a process to archive data - in fact that's what this query is part of..

thanx for the response !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources