MySQL database sync pessimistic lock and optimistic lock

Test Required: Open two test windows locally

Pessimistic lock

Pessimistic lock it refers to the data is conservative in the external (including the system's current other transactions, as well as the transaction from the External System), the data is locked during the entire data processing process. Pessimistic lock implementation, often rely on the lock mechanism provided by the database (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, or even in this system to implement the locking mechanism, there is no guarantee that the external system will not modify the data).
A typical pessimistic lock call that relies on a database:
SELECT * FROM table where name= ' Who am I ' for update;
This SQL statement locks all records in table tables that meet the search criteria (name= ' Who am I '). These records cannot be modified by the outside world before the transaction commits (the locks in the transaction are freed during transaction commits). Hibernate's pessimistic lock is also a database-based lock mechanism implementation.

A window that opens a transaction but does not commit a transaction, executes a SELECT statement that contains the FOR Update child statement

Another window tries to update a table

We can see the error content is: Lock wait timeout exceeded; Try to restart the transaction

This time we commit the transaction

The update succeeds when you try the update again

MySQL database sync pessimistic lock and optimistic lock