exclusive lock

(pls correct if u feel anything here is incorrect)
i know in SQL2000, locks are well managed internally, and many DML/DDL
statements will auto use lock.
just want to know, why and how a DBA will need to exclusively use lock,
under what kind of situations ?
tks for sharing.you may change locking behaviour by changing isolation levels for the
connection
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (others doc'd in books on
line)
You may also change locking behaviour for a single table in a single
transaction using lock hints ie
Select * from titles with ( nolock)
In my experience the most common two reasons for adjusting locking is
1. to increase locking so that no-one can touch the data until you are
through with it... Perhaps you are doing some kind of adjustments to the
money fields of many rows ( like cost allocation)... Any changes made by
others during your process will skew your work and make it inaccurate...
Therefore you may choose to increase the locking level to serializable so
no-one else can touch the data until you are through..
Another similar case is when you are doing a select ( but intend to
update later) and do not wish the values to change, you might select with
holdlock.
2. the second reason is that you wish to read some data and NOT be blocked
by updaters. You may choose to ignore exclusive locks and read dirty data.
The select above will do that..
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:utAG1ewEEHA.2768@.tk2msftngp13.phx.gbl...
> (pls correct if u feel anything here is incorrect)
> i know in SQL2000, locks are well managed internally, and many DML/DDL
> statements will auto use lock.
> just want to know, why and how a DBA will need to exclusively use lock,
> under what kind of situations ?
> tks for sharing.
>