Mysql-mysql pessimistic lock and optimistic lock

1, MySQL four types of transaction isolation level
I. For running multiple transactions concurrently, when these transactions access the same data in the database, it can cause various concurrency problems if the necessary isolation mechanisms are not taken.
(1) Dirty read: For two things T1, T2, T1 read the fields that have been T2 updated but have not yet been submitted. Then, if T2 rolls back, the content read by T1 is temporary and invalid.
(2) Non-repeatable reading: For two things T1, T2, T1 read a field, and T2 updated the field. After that, T1 reads the same field again, and the value is different.
(3) Phantom reading: For two things T1, T2, T1 reads a field from a table, and then T2 inserts some new rows into the table. Then, if T1 reads the same table again, it will have a few more rows.
　　
II. Database transaction Isolation: The database must be isolated from the ability to run multiple transactions concurrently, so that they do not affect each other and avoid various concurrency problems.
　　
Iii. Isolation Level: the degree to which a transaction is isolated from other transactions is known as the isolation level, the database specifies a variety of isolation levels, the different levels of isolation correspond to the different levels of interference, the higher the isolation level, the better the number scrambling consistency, but the weaker the concurrency.
(1) Read uncommitted data, allow transactions to read the changes not committed by other transactions, the above three problems will appear.
(2) Read the submitted data, allow the transaction to read the arguments submitted by other transactions, avoid dirty read, but there will be non-repeatable read and Phantom read.
(3) Repeatable read to ensure that a transaction can read the same value from a field, which prevents other transactions from manipulating the field during the duration of the transaction, and the Phantom read problem persists
(4) serialization, low efficiency
　　
Iv.mysql The default isolation level is repeatable read, there are two ways to set the database isolation level
(1) Use the SQL statement to modify the isolation level of the database.
(2) Setting the isolation level in Hibernate
READ uncommited
READ commited
Repeatable READ
Serializeable

2, MySQL pessimistic lock

The pessimistic lock for MySQL is only executed when the SELECT * from table for UPDATE statement is executed.
To use pessimistic locks, 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, MySQL commits the results immediately.

(1) MySQL lock level, Row lock and table lock, MySQL default lock level is Row-level lock
Table A has three field IDs, name, age, where ID is the primary key, name has index, age is normal field
A. Execute select * from A where id=1 for update
This situation will lock the row
B. Execute select * from A where name=1 for update
This situation will lock the row
C. Execute SELECT * from A where age=1 for update
This situation will lock the table

(2) Pessimistic lock, which refers to a transaction, the data is modified by another transaction conservative attitude, that is, each time the query will lock the record or lock the table, until the modification is complete
It is important to note that if transaction A executes a select * from A WHERE id = 3 for UPDATE, the B transaction is executed if
SELECT * from A WHERE id = 3 for update waits for the end of a transaction to execute
3. mysql optimistic lock
(1) Concept:
Optimistic locking assumes that the data generally does not cause conflict, so when the data is submitted to update, the data will be formally conflicting or not detected,
If a conflict is found, let the user decide what to do by returning the user with the wrong information.
(2) Realization mode
Way One:
Using the version record mechanism implementation, the value of the version field is read together, and the data is updated every time the version value is added one.
When we submit an update, we determine that the current version of the database table corresponding to the record is compared to the first one taken out,
If the current version number of the database table is equal to the first fetch, it is updated, otherwise it is considered to be outdated data
Way two:
Add a field to the table that requires optimistic lock control, the name doesn't matter, the field type uses a timestamp (timestamp),
Similar to the above version, it also checks the timestamp of the data in the current database when the update is submitted and compares it to the timestamp that was taken before the update.
If the consistency is OK, otherwise the version conflict.
4. Examples of pessimistic lock usage scenarios
There are two tables, a list of goods goods, a user form account, a user name and a user's money.
In case of a lock is not used:
(1) A User Query goods table, found that the goods are still one piece, and then a user to perform payment operations, at this time, B users also found that the goods table also has a, also perform payment operations,
(2) b action than a faster, at this time B pay, goods table minus 1, at this time a also paid, found that the goods are gone, so there is a problem
Associating things with a usage scene,
A Access table, modify the remaining amount of the goods table, is a thing,
A payment is a thing, to ensure that two transactions are completed, or not finished, two small transactions to run in a large transaction inside

5. Summary of pessimistic lock and optimistic lock
Optimistic locking is suitable for less-than-write situations where collisions are really rare, which eliminates the overhead of locks.
Increases the overall throughput of the system. However, if there is frequent conflict, the upper application will continue to retry,
This reduces performance, so it's more appropriate to use pessimistic locking in this case.

Mysql-mysql pessimistic lock and optimistic lock