Talk about the pessimistic and optimistic lock on MySQL

Pessimistic lock and optimistic lock are two common methods of resource concurrent lock design, and also a very basic concept in concurrent programming. Before I wrote an article about the processing ideas and solutions of concurrency, here I will separate the two common locking mechanisms in the database data on the implementation of the system is introduced once.

Pessimistic lock (Pessimistic lock)

Pessimistic lock is characterized by the first acquisition of locks, and then business operations, that is, "pessimistic" that the acquisition of locks is very likely to fail, so make sure to obtain the lock successfully before the business operation. Usually referred to as "one lock two check three update" refers to the use of pessimistic lock. In general, pessimistic locks on the database require support from the database itself, which means that pessimistic locks are implemented through a common select ... for update operation. When the database executes a select for update, it gets the row lock of the data row in the Select, so the other concurrent execution of select for update occurs when an attempt is made to select the same row (waiting for the row lock to be released), thus achieving the effect of the lock. The row lock obtained by the Select for update is automatically freed at the end of the current transaction and must therefore be used in the transaction.

One thing to note here is that different databases make a difference to the implementation and support of select for update, such as Oracle supports select for Update no wait, which means that if you don't get the lock immediately, instead of waiting, MySQL has no Wait for this option. Another problem with MySQL is that all scanned rows are locked during the execution of the Select FOR UPDATE statement, which can easily cause problems. So if you're using pessimistic locking in MySQL it's important to make sure that you've gone through the index, not the full table scan.

Optimistic lock (Optimistic lock)

The characteristics of the optimistic lock first business operations, not the last resort to take locks. That is, "optimistic" that the lock is likely to be successful, so in the end of the business operations need to actually update the data of the last step to get the lock.

The implementation of optimistic locking on a database is completely logical and does not require special support from a database. The general practice is to add a version number, or timestamp, to the data that needs to be locked, and then implement it as follows:

1.SELECTData asOld_data, version asOld_version from. ..;2New_data and New_version based on acquired data for business operations3.UPDATE SETData=New_data, version=New_versionWHEREVersion=old_versionif(Updated row> 0) {    //optimistic lock get successful, Operation complete}Else {    //optimistic lock get failed, rollback and retry}

Whether optimistic locks are in fact irrelevant in the transaction, the underlying mechanism is this: when updating the same row in the database is not allowed concurrency, that is, each time the database executes an UPDATE statement will get the update row of the write lock, until the row was successfully updated before release. Therefore, before a business operation takes the current version number of the data that needs to be locked, and then actually updates the data again, the version number confirms the same as the previous fetch, and the version number is updated to confirm that no concurrent modifications have occurred. If the update fails to assume that the old version of the data has been modified in parallel and does not exist, it is assumed that the acquisition of the lock failed, you need to roll back the entire business operation and can retry the entire process as needed. Summarize

• Optimistic locks are less expensive than pessimistic locks when no lock failures occur, but when a failure rollback cost is larger, it is suitable for scenarios where the probability of lock failure is relatively small, which can improve the system concurrency performance

• Optimistic locking also applies to some of the more specific scenarios, such as the inability to maintain a connection with the database during business operations, where pessimistic locks cannot be applied

Talk about the pessimistic and optimistic lock on MySQL