The optimistic lock and pessimistic lock of MySQL

Optimistic lock

Always think that there is no concurrency problem, every time you go to fetch data, always think that there will be no other threads to modify the data, so it will not be locked, but in the update will be judged that other threads before the data is modified, generally using the version number mechanism or CAS operation implementation.

For example:

There is a table like this:

Update each time the condition is appended with a time condition:

Update user_info set password= ' Somelog ' where username= ' somelog ' and time= ' 2018-07-11 ';

　　

Because if the concurrent operation is the same as the time of the query at the same moment, but the time is changed when the first insert, then the updated operation is not possible because the value of the Times is changed by the previous operation.

This is how optimistic locks are implemented, with a version number appended to the update.

Pessimistic lock

Always assume that the worst case scenario is that every time the data is taken it is assumed that other threads will be modified, so the locking (read lock, write lock, row lock, etc.) will need to be blocked when other threads want to access the data. Can rely on database implementation, such as row locks, read locks and write locks, are locked before operation, in Java, synchronized thought is pessimistic lock.
Note: To use the pessimistic lock of the database, we must turn off the auto-commit property of the MySQL database, because MySQL uses the autocommit mode by default, that is, when you perform an update operation, MySQL will immediately submit the result.

Pessimistic locks are divided into two types: shared and exclusive locks

Shared locks are other transactions that can be read but cannot be written
An exclusive lock is a transaction that has permission to read and write to this data.

SQL notation
Shared Lock (S):

SELECT * FROM table_name WHERE ... LOCK in SHARE MODE;

Exclusive Lock (X):

SELECT * FROM table_name WHERE ... For UPDATE;

The lock must be opened first: Begin, locking, Operation->commit; (COMMIT TRANSACTION, return lock)

The lock is divided into explicit lock and implicit lock, the above writing is explicit lock. MySQL performs insert, Update automatically locks, and MySQL does not lock the select.

For example:

No lock

SELECT * from data where username= ' Somelog ';

Plus Lock:

SELECT * from data where username= ' somelog ' for update;

If a transaction adds an exclusive lock, the other transaction does not lock the Select can also query to the data

As can be seen above, the lock is not for the transaction, exclusive lock only one, who took who can be updated, but did not get the lock of the transaction as long as the select does not add the lock can also query data.

　　

The optimistic lock and pessimistic lock of MySQL