"MySQL" About optimistic lock

first, optimistic about the lock introduction

Optimistic lock (optimistic Locking) relative pessimistic lock, optimistic locking hypothesis that the data generally do not cause conflict, so when the data is submitted to update the data will be formally conflicting or not, optimistic lock for multi-read application type, so as to improve throughput, Like a database, if you provide a similar write_condition mechanism, you are actually providing an optimistic lock. Similar to SVN

Pessimistic locking assumes that other users are trying to access or change the object you are accessing, changing the probability is very high, so in a pessimistic lock environment, before you begin to change this object to lock the object, and until you commit the changes before releasing the lock.

Ii. means of realization

1. Using the data version recording mechanism is implemented, which is the most common way to implement optimistic locking. What is a data version? is to add a version identifier to the data, typically by adding a "version" field of a numeric type to the database table. When the data is read, the value of the version field is read together, and the data is updated each time, adding one to this version value. When we submit an update, the current version of the corresponding record of the database table is judged to be compared with the first fetch, if the current version number of the database table is equal to the first taken out, it is updated, otherwise it is considered to be outdated data

2. The second way to achieve optimistic locking is similar to the first one, as well as adding a field to the table that requires optimistic lock control, the name does not matter, the field type uses the timestamp (timestamp), and the above version is like It also checks the timestamp of the data in the current database when the update is submitted and compares it to the time stamp that was taken before updating it, or the version conflict if the consistency is OK.

Three, optimistic lock pessimistic lock difference

The pessimistic flaw is that whether it is a page lock or a row lock, the lock-up time can be very long, which may lead to limited access to other users, that is, pessimistic lock concurrent access is not good.

Optimistic locking holds the probability that other users are trying to change the object you are changing, so the optimistic lock locks the object until you are ready to commit the changes, and does not lock when you read and change the object. It can be seen that the optimistic lock and lock time is shorter than the pessimistic lock, and the optimistic lock may obtain better concurrent access performance with larger lock granularity.

For example, if the second user reads the object exactly before the first user commits the change, the database will find that the object has changed when he has made his own changes, so that the second user has to reread the object and make changes. In an optimistic lock environment, the number of times the object is read by the concurrent user is increased.

Version control system

What if the data is mutable and cannot be isolated? Two of the most common controls in this case are optimistic concurrency control and pessimistic concurrency control.

Suppose Xiao Zhang and Xiao Li want to modify the same file at the same time. If you use optimistic lock, both can open the file for modification, if the small Zhang Xian submitted content, no problem, his changes will be saved to the server. But Xiao Li will be in trouble when submitting, the version control server will detect two kinds of changes in the conflict, Xiao Li's submission will be specific, and by Xiao Li decide how to deal with this situation (for most version control software, will read and identify the changes made by Xiao Zhang, and then by the small Lee decide whether to merge).

If you are using a pessimistic lock, the small Zhang Xian check out file, then Xiao Li will not be able to check out the same file again, until the small Zhang submitted his changes

Usage recommendations

Optimistic locking as a means of detecting conflict, and pessimistic lock is a means to avoid conflict (strictly speaking, optimistic lock is not called "lock", but the name has been circulated, then continue to use it), optimistic lock can improve the efficiency of concurrent access, but if there is a conflict can only be thrown upward, and then again

Pessimistic locking avoids collisions, but reduces efficiency, and choosing which locks to use depends on the frequency of access and the severity of the conflict

If the probability of a system being accessed concurrently is low, or the consequences of a conflict are less severe (the so-called consequence should be that the commit that is detected by the conflict will fail and must be repeated), you can use optimistic locks, or use pessimistic locks

The limitation of optimistic locking is that the business transaction will fail only when the data is submitted, and in some cases it can be costly to find that the failure is too late. One approach is to use pessimistic locks, which can detect errors as early as possible, but are difficult to implement programmatically and reduce system flexibility

Four, the realization way in MVCC

The MySQL InnoDB storage engine, which implements the Concurrency Control Protocol--MVCC (Multi-version Concurrency Control) based on multiple versions (note: Compared to MVCC, is lock-based concurrency control, lock-based Concurrency Control). MVCC The greatest benefits, I believe it is also familiar: Read no lock, read and write no conflict. In an OLTP application that reads and writes less, read-write conflicts are important, greatly increasing the concurrency of the system, which is why at this stage, almost all RDBMS support the MVCC

In InnoDB, two additional hidden values are added after each row of data to implement MVCC, which is a record of when this row of data was created and when another record of when this row of data expires (or is deleted). In practice, it is not the time that is stored, but the version number of the transaction, and the version number of the transaction is incremented for each new transaction that is opened. Under the re-repeatable reads transaction ISOLATION level:

• Select, read create version number <= current transaction version number, delete version number is empty or > Current transaction version number.
• When you insert, save the current transaction version number to the created version number of the row
• Delete, save the deleted version number of the row for the current transaction version number
• UPDATE, insert a new record, save the current transaction version number for the line to create the version number, while saving the current transaction version number to the original deleted row

Through MVCC, although each row of records requires additional storage space, more row inspection work and some additional maintenance work, but can reduce the use of locks, most of the read operation is not locked, read data operation is very simple, good performance, and can be guaranteed only to read to meet the standard line, also only lock the necessary rows

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read. Snapshot reads, read the visible version of the record (possibly a historical version), without locking. The current read, read is the latest version of the record, and the current read returned records, will be added to the lock, to ensure that other transactions will no longer concurrently modify this record.

In a system that supports MVCC concurrency control, which read operations are snapshot reads? Which operations are currently read? Take MySQL InnoDB as an example:

Snapshot read: A simple select operation, which belongs to the snapshot read, without locking. (Of course, there are exceptions, which are analyzed below)

• SELECT * from table where?;

Current read: Special read operation, insert/update/delete operation, belongs to the current read, need to lock.

• SELECT * FROM table where? lock in Share mode;
• SELECT * FROM table where? for update;
• Insert into table values (...);
• Update table set? Where?;
• Delete from table where?;

All of the above statements belong to the current read, reading the latest version of the record. Also, after reading, it is necessary to ensure that other concurrent transactions cannot modify the current record and lock the read record. In addition to the first statement, the read record plus S lock (shared lock), the other operation, plus X lock (exclusive lock).

"MySQL" About optimistic lock