Adding this section based on @AppleBook89's comment.
so what does Bill Paetzke mean when he says "READ COMMITTED SNAPSHOT
does optimistic reads and pessimistic writes.
Read operations do not acquire shared (S) locks on the data being read, and therefore do not block transactions that are modifying data.
The selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read. This is the same as a read-committed transaction that does not use row versioning. If the data row does not meet the update criteria, the update lock is released on that row and the next row is locked and scanned.
In contrast, SNAPSHOT does optimistic reads and optimistic writes."
Thats what I am trying to understand
For read same answer as above.
Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Otherwise, locks are not acquired on data until the data is to be modified. When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the Database Engine and there is no way to disable the update conflict detection.
and how did his response get 70+ thumbs up?
I do not know :)
I would rephrase your question to Can someone define:
- Pessimistic concurrency control (or pessimistic locking)
Pessimistic concurrency control (or pessimistic locking) is called
"pessimistic" because the system assumes the worst — it assumes that
two or more users will want to update the same record at the same
time, and then prevents that possibility by locking the record, no
matter how unlikely conflicts actually are. The locks are placed as
soon as any piece of the row is accessed, making it impossible for two
or more users to update the row at the same time. Depending on the
lock mode (shared, exclusive, or update), other users might be able to
read the data even though a lock has been placed.
- Optimistic concurrency control (or optimistic locking)
Optimistic concurrency control (or optimistic locking) assumes that although conflicts are possible, they will be very rare. Instead of locking every record every time that it is used, the system merely looks for indications that two users actually did try to update the same record at the same time. If that evidence is found, then one user's updates are discarded and the user is informed.
Even though the optimistic concurrency control mechanism is sometimes called optimistic locking, it is not a true locking scheme—the system does not place any locks when optimistic concurrency control is used. The term locking is used because optimistic concurrency control serves the same purpose as pessimistic locking by preventing overlapping updates.
When you use optimistic locking, you do not find out that there is a conflict until just before you write the updated data. In pessimistic locking, you find out there is a conflict as soon as you try to read the data.
I find this explanation by Kendra Little very easy to follow.
Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide
Common questions and misconceptions are explained by Robert Sheldon.
Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask
PESSIMISTIC vs. OPTIMISTIC concurrency control
Understanding Row Versioning-Based Isolation Levels