Difference between snapshot, pessimistic, and optimistic transactions

Question

I have been trying to understand these types of database transaction. From my understanding snapshot transaction maintain concurrency by sending a relatively small amount of time old snapshot of the database. Pessimistic blocks an operation of a transaction when there is a lot of calls where is optimistic lets you call multiple calls to the db without locks. Am I understanding these correctly? How does snapshot deal with locks?

Could someone explain how do these play into consistency, availability and even network partition (CAP theorem)?

Answer 1

Database Lock

In other systems, database locks serve to protect shared resource or objects. These protected resource could be

1. Table.
2. Data rows.
3. Data blocks.

A lock can be a read lock or write lock, is used when multiple users need to access a database concurrently. This prevents data from being corrupted or invalidated. This type of issues occur when multiple users try to read the or other users try to write the database.

Any single user can modify the database records , the items in the database to which they have applied a lock that gives them exclusive access to the record until the lock is released.

Pessimistic Locking

In pessimistic locking, a record or a page is locked immediately when the lock is requested.

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

Optimistic Locking

• Begin - time stamp marking.
• Modify.
• Validate
• Commit/rollback

Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.

If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it or rollback.

Snapshots

A snapshot is a read-only static view of a database.

Hope this will help you to understand!

Answer 2

Concurrency control is about controlling different phenomena that might cause issues, e.g. dirty read / non-repeatable read / phantom read / lost update. You can either be optimistic and assume that those issues will rarely occur or be pessimistic and assume they will occur more often.

When you are optimistic, it makes sense to first allow changes to happen and roll-back in case you detect issues. Rolling back is expensive, but that is ok if it happens rarely. Snapshot Isolation and Serializable Snapshot Isolation are two optimistic concurrency control algorithms.

The pessimistic concurrency control is done with locks.

Way more details are in https://www.interdb.jp/pg/pgsql05.html :

There are three broad concurrency control techniques, i.e. Multi-version Concurrency Control (MVCC), Strict Two-Phase Locking (S2PL), and Optimistic Concurrency Control (OCC)