Huh... You should read a bit more about transactions in Books OnLine. You
have some misconcepts here.

[quoted text, click to view]

All transactions use locks, even in read uncommitted level you get exclusive
locks for updates.

[quoted text, click to view]

In SQL Server, all updates are automatically transactions. You should
specify your own transactions when you need higher grain of atomicity, i.e.
if you want to have more than one update joined in a single transaction, so
all updates are committed or all are rolled back. Locks are the mechanism
for concurrency management. Some isolation levels (read uncommitted, read
committed, repeatable read, serializable) are pessimistic, some (read
committed snapshot, snapshot) are optimistic. It is not locks that define
whether the locking is optimistic or pessimistic; it is the fact whether you
have more than one copy of the same data or not.

--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/

This may be a naive question. Since DB Transactions with higher isolation
levels internally use locks, does it make sense to use Transactions for
concurrency management , in place of using some kind of pessimistic locks ?

Thanks,
Raj.

In addition to what Dejan said, you should always try to avoid using
pessimistic locking in your applications. There is no simple
one-size-fits-all solution because every application is different.
However, designing your table schema at the outset with concurrency in
mind can eliminate locking and blocking headaches down the road.

--Mary

On Fri, 12 Jan 2007 11:24:56 +0530, "Chakravarthy"
[quoted text, click to view]