1

What is the difference between optimistic read and optimistic write?

Or probably more so, can someone define these 4?

(a) Optimistic Reading

(b) Pessimistic Reading

(c) Optimistic Writing

(d) Pessimistic Writing

Referring to question and answer here: https://stackoverflow.com/questions/2741016/read-committed-snapshot-vs-snapshot-isolation-level

'READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes.

3

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

Reference:

PESSIMISTIC vs. OPTIMISTIC concurrency control

Understanding Row Versioning-Based Isolation Levels

4

Or probably more so, can someone define these 4?

No. Those are not terms with precise definitions. "Optimistic Concurrency" means to make some change even though you might not be able to save or commit the change later. And "Pessimistic Concurrency" means to acquire locks before you make the change to ensure you will be able to save/commit it.

In SQL Server no isolation level uses exclusive locking when reading data (though you can force it with a lock hint).

Both RCSI and SNAPSHOT use Pessimistic Concurrency. You will always acquire the X locks needed to change and commit a row before actually making the change.

Client-Side libraries often use Optimistic Concurrency, reading data, changing it in memory, and then, on save, checking if those changes would conflict with another sessions changes. If the changes are found to conflict, an error is thrown. See eg Optimistic Concurrency

In the SQL Server Database Engine the only Optimistic Concurrency is the model introduced by Memory-Optimized Tables, which don't require locks to write data, and instead use a first-writer-wins Optimistic Concurrency model, described here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy