Repeatable reading vs Optimistic

Question

What is the difference between management levels and concurrency transaction isolation level?

I understand each of them clearly, but I am having some problems with each other. In particular, I see some coincidence in my functions, and I'm not sure when to use one over the other. Or should both be used together?

And what does it mean to say pessimistic blocking with repeated reading? Is repeat reading repeated, meaning that all values ​​to be edited will be locked? So why is there a need for pessimistic blocking?

Answer 1

The problem arises due to the presence of two concurrency control models, which are sometimes mixed with the SQL implementation.

• blocking, as in 2PL (two-phase blocking)
• as in MVCC (Multiversion concurrency Control)

Pessimistic means that read lines are blocked. Optimistic means that the lines that are counted are not locked.

The classic 2PL Repeatable Read implementation is always pessimistic. The multi-version of Repeatable Read is optimistic. It does not block the lines that are read for the SELECT statement, and allows other transactions to change the lines that were read in the SELECT. Such changes are not visible to the transaction that performed the SELECT until it is completed.

Answer 2

Concurrency control is a generic term for any mechanism that handles problems arising from parallel connections.

Transaction isolation levels are the mechanism by which MySQL implements the concurrency control.

See Consistent REPEATABLE READ for documentation on how MySQL implements REPEATABLE READ without pessimistic locking:

Consistent reading does not set any locks on the tables it accesses, and therefore other sessions can modify these tables, while sequential reading is performed on the table.

Suppose you are using the default REPEATABLE READ isolation level. When you issue a sequential read (that is, a regular SELECT ), InnoDB gives your transaction a temporary point according to which your query sees the database. If another transaction deletes the row and is committed after assigning your time point, you do not see that the row has been deleted. Insertions and updates are handled similarly.