Row and Table Level Locking

When we do Insert or Update How SQL optimizer when optimizer lock the table
and
when optimizer lock the row?
I know we can force these using Hints.
TIA
By default it will lock at the row level. Certain things may cause it to
escalate to a table level lock such as:
No valid indexes or Primary Keys defined.
Too many rows updated at one time.
There are some hints along the lines of what you were asking and you can
find out more in booksonline under "hints" or "ROWLOCK".
Andrew J. Kelly SQL MVP
"ss" <sandeep_shankar@.yahoo.com> wrote in message
news:O$tzYumJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> When we do Insert or Update How SQL optimizer when optimizer lock the
table
> and
> when optimizer lock the row?
> I know we can force these using Hints.
> TIA
>
|||To add to Andrew's answer, I would use locking hints as a last resort after
all other tuning options are exhausted. Also, make sure you fully
understand what each locking hint does.
If you're experiencing problems with excessive blocking, refer to these
articles:
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems http://support.microsoft.com/?id=224453
263889 INF: SQL Blocking Due to [[COMPILE]] Locks
http://support.microsoft.com/?id=263889
75722 INF: Reducing Lock Contention in SQL Server
http://support.microsoft.com/?id=75722
323630 INF: Resolving Blocking Problems That Are Caused by Lock Escalation
in http://support.microsoft.com/?id=323630
295108 INF: Incomplete Transaction May Hold Large Number of Locks and Cause
http://support.microsoft.com/?id=295108
317375 INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL
Server http://support.microsoft.com/?id=317375
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.