Locking

Hi all,
SQL server doesnt provide any kind of configuration to
control granularity of locking on tables. As per books
online it is managed dynamically.
I have a major problem with my SQL Server, during
execution of one SP, on one table with 400+ rows it
acquires 600,000 locks. My profiler shows
whooping 'Lock:Acquired' on this particular table.
Can somebody please throw some light on this? Why SQL
server doesn't promote it to single table level lock? Is
there any bug in SQL?
thanks very much
Himanshu JaniYou probably want to keep working on why this happens in the first place (although there's not much
to go on for us), but here are a couple of options for immediate fix:
Specify lock hint in the query (TABLOCK, for instance).
Configure the index used to not allow row locks (sp_indexoption).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Himanshu Jani" <anonymous@.discussions.microsoft.com> wrote in message
news:2c84301c393cd$c746cab0$a601280a@.phx.gbl...
> Hi all,
> SQL server doesnt provide any kind of configuration to
> control granularity of locking on tables. As per books
> online it is managed dynamically.
> I have a major problem with my SQL Server, during
> execution of one SP, on one table with 400+ rows it
> acquires 600,000 locks. My profiler shows
> whooping 'Lock:Acquired' on this particular table.
> Can somebody please throw some light on this? Why SQL
> server doesn't promote it to single table level lock? Is
> there any bug in SQL?
> thanks very much
> Himanshu Jani

Locking

Can you change the locking granularity on a single table
at the database level? I want to set up a single table at
table level locking without the programmer having to send
a hint.
Thanks for the help,
JoelThis is a multi-part message in MIME format.
--=_NextPart_000_003D_01C354F8.393E6980
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out sp_indexoption in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Joel" <joel.griffin@.teijinmonofil.com> wrote in message =news:00b201c35518$a96b8d80$a101280a@.phx.gbl...
Can you change the locking granularity on a single table at the database level? I want to set up a single table at table level locking without the programmer having to send a hint.
Thanks for the help,
Joel
--=_NextPart_000_003D_01C354F8.393E6980
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_indexoption in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Joel" wrote in message news:00b201c35518$a9=6b8d80$a101280a@.phx.gbl...Can you change the locking granularity on a single table at the database =level? I want to set up a single table at table level locking =without the programmer having to send a hint.Thanks for the help,Joel

---

--=_NextPart_000_003D_01C354F8.393E6980--