LOCK

Question

LOCK

VastSQL

SSC-Dedicated

Points: 36097
More actions
March 11, 2009 at 5:05 am

#127012

Hi Experts,
I am getting an exclusive lock on a particular table whenever i ran a select * from statement. The table is having less than 3600 rows. I checked the activity monitor and in lock by processes i found an objects named dbname.dbo.spt_fallback_db.I am not able to find that object in sysobjects table.
Please help
TIA

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

That object exist in the master DB; however I am not sure the purpose for it. I can't find much information on these tables.

Thanks.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 2

SA-1

SSCrazy Eights

Points: 8113

March 11, 2009 at 11:58 am

#957766

Have you tried using query hints (nolock)?

noeld SSC Guru Points: 96590 More actions · Answer 3

spt_fallback_db is in master and is a system table.

* Noel

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

I know its in master database but in activity monitor its showing database_name.dbo.spt_fallback_db.

when giving NOLOCK hint there is no problem without that its worst.

The issue get solved when i reorganized the index in that table.

But i just want to know how and why that lock happened .The table is a very small one having less than 3600 rows.Now the query is taking less than a second.

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 5

SELECT * .. should have created a shared lock on the table; not an exlusive one if you find the answer please let us know :pinch:. But even shared lock would have casued blockage (mind you it would have been very breif).

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 6

Mohit ,the problem is that whoever runs a select * statement in that table creates lock.

jk.returns SSC Veteran Points: 230 More actions · Answer 7

Try update statistics and reorganize indexes.itokke njn paranju tarano.

i told you yesterday na 😛

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 8

- what isolation level are you using?

- also, I am presuming your sure that this is an exclusive lock not an index lock?

- the optimizer will lock objects if it deems it will yield the best query performance. Out of date statistics and a fragmented index are a prime combo for this type of thing.

Carlton ..