Optimistic and pessimistic locks of MySQL are used according to business scenarios. Use locks for each service scenario.

But both types of locks solve concurrency problems. Now let’s see how to use optimistic locks and pessimistic locks properly

What is pessimism lock

Pessimistic Lock: You are Pessimistic. Each time you fetch data, you assume that someone else will change it. Therefore, each time you fetch data, you Lock it so that someone else will want to fetch the data and block it until the Lock is fetched. For example, in the inventory increase or decrease problem, the use of pessimistic lock can effectively prevent the inventory reduction problem.

In simple terms, pessimistic locking assumes that concurrency conflicts will occur and blocks any operations that might violate data integrity. Pessimistic concurrency control is actually a conservative strategy of “lock first, access later”, which guarantees the security of data processing.

In terms of efficiency, the mechanism for handling locking imposes additional overhead on the database, as well as the possibility of deadlocks. To reduce parallelism, if a transaction locks a row, other transactions must wait for the transaction to complete before processing that row.

The difference between pessimistic and optimistic locks

1 Advantages and disadvantages The two kinds of locks have their own advantages and disadvantages, and one can not be considered better than the other. For example, optimistic lock is suitable for the situation where there are few writes and conflicts really rarely occur. In this way, the overhead of locking can be saved and the overall throughput of the system can be increased. However, if there are frequent conflicts, the upper application will continue to retry, which can degrade performance, so pessimistic locking is appropriate in this case.

2 Implementation mode Implementation mode of pessimistic lock: The realization of pessimistic lock depends on the locking mechanism provided by the database. In a database, the pessimistic locking process is as follows: 1 Try to add exclusive locks before modifying data. 2 Failed to lock, indicating that data is being modified, waiting, or an exception is being thrown. 3 The lock is successfully added, the data is modified, the transaction is committed, and the lock is released. If we successfully lock the data, other threads can only wait or throw an exception.

Optimistic locking can be implemented in the following ways: 1) Version mode: Generally, a version field is added to the data table, indicating the number of times the data is modified. When the data is modified, the version value will be increased by one. When thread A wants to update the data value, it also reads the version value. When submitting the update, it updates the version value only when the version value it just read is the same as the version value in the current database. Otherwise, the update operation is retried until the update succeeds.

SQL implementation code

update table set n=n+1, version=version+1 where id=#{id} and version=#{version};
Copy the code

2) CAS (see definition below) operation mode: namely compare and swap or compare and set, involving three operands, memory value where data is located, expected value and new value. When update is required, check whether the current memory value is equal to the previous value. If it is equal, update the memory with the new value. If it fails, try again.

Rational use of pessimistic and optimistic locks

In essence, both optimistic and pessimistic locks of MySQL are mainly used to solve concurrent scenarios and avoid missing updates. Optimistic lock: Applies to scenarios where read operations are frequent. If a large number of write operations occur, data conflicts are more likely. To ensure data consistency, the application layer needs to continuously obtain data, which increases a large number of query operations and reduces the throughput of the system.

Pessimistic lock: It is suitable for scenarios with frequent write operations. If a large number of read operations occur, locks will be added each time the read operations occur, which increases the overhead of locks and reduces the throughput of the system.

Use a scene and code to introduce how to use it in detail. Suppose there is such a scene of commodity killing and buying: in the buying scene, there are only 100 commodities in total. At the last moment, 99 commodities have been consumed and only one is left. At this time, the system sends multiple concurrent requests, which read 99 goods with a margin, and then pass the margin judgment, resulting in overdelivery of goods. That is, concurrent user B also “buys successfully”, allowing one more person to obtain the goods.

**1 Pessimistic locking scheme: ** Pessimistic locking, that is, when modifying data, adopt the lock state, excluding external request modification. When a locked state is encountered, it must wait.

Solution: Use MySQL transactions to lock rows for operations

<? php include('./mysql.php'); Function build_order_no(){return date('ymd'). Substr (implode(NULL, array_map('ord',)) str_split(substr(uniqid(), 7, 13), 1))), 0, 8); Function insertLog($event,$type=0){global $conn; $sql="insert into ih_log(event,type) values('$event','$type')"; mysqli_query($conn,$sql); Mysqli_query ($conn,"BEGIN"); $SQL ="select number from ih_store where goods_id='$goods_id' and sku_id='$sku_id';  FOR UPDATE"; $rs=mysqli_query($conn,$sql); $row=$rs->fetch_assoc(); If ($row [' number '] > 0) {/ / generated order $order_sn = build_order_no (); $sql="insert into ih_order(order_sn,user_id,goods_id,sku_id,price) values('$order_sn','$user_id','$goods_id','$sku_id','$price')"; $order_rs=mysqli_query($conn,$sql); $SQL ="update ih_store set number=number-{$number} where sku_id=' sku_id'"; $store_rs=mysqli_query($conn,$sql); If ($store_rs){echo 'inventory reduction succeeded '; InsertLog (' Inventory reduction success '); mysqli_query($conn,"COMMIT"); }else{echo 'inventory reduction failed '; InsertLog (' Inventory reduction failed '); }}else{echo 'stock not enough '; InsertLog (' out of stock '); mysqli_query($conn,"ROLLBACK"); }Copy the code

The above solution addresses the issue of thread safety, but our scenario is “high concurrency.” That is, there will be many such modification requests, and each request will have to wait for a “lock” that some thread may never have a chance to grab, and the request will die there. At the same time, the number of such requests can increase the average response time of the system in an instant, and as a result, the number of available connections can be exhausted and the system can run into exceptions.

To slightly modify the above scenario, we queue requests directly and use FIFO (First Input First Output) so that we don’t end up with some requests never getting the lock.

All requests are processed in a “first in, first out” queue, which solves the lock problem. However, a new problem arises. In a high-concurrency scenario, a large number of requests can “burst” the queue memory in an instant, and then the system falls into an abnormal state. At this point, we can use optimistic locks to solve the problem.

As mentioned above, optimistic locking is a looser locking mechanism than “pessimistic locking”, which is mostly updated with Version number. Implementation is this data all requests are eligible to modify, but will get a version number of the data, only the version number can be updated successfully, the other return snap up failed. This way, we do not need to worry about queues, but it can increase CPU computation overhead. But, all in all, it’s a better solution.

We use Watch in Redis to realize optimistic lock and ensure data security through this implementation.

<? php $redis = new redis(); $result = $redis - > connect (127.0.0.1, 6379); echo $mywatchkey = $redis->get("mywatchkey"); If ($mywatchKey >0){$redis->watch("mywatchkey"); // Start a new transaction. $redis->multi(); $redis->set("mywatchkey",$mywatchkey-1); $result = $redis->exec(); If ($result) {$redis - > hSet (" watchkeylist ", "user_". Mt_rand (1999 99), time ()); $watchkeylist = $redis->hGetAll("watchkeylist"); Echo "Buy it! <br/>"; $re = $mywatchkey - 1; Echo ".$re."<br/>"; Echo "user: <pre>"; print_r($watchkeylist); }else{echo "Bad luck, buy again!" ; exit; }} else {/ / $redis - > hSet (" watchkeylist ", "user_". Mt_rand (1999 99), "12"); // $watchkeylist = $redis->hGetAll("watchkeylist"); Echo "fail! <br/>"; echo ".no result<br/>"; Echo "user: <pre>"; //var_dump($watchkeylist); }*/ $rob_total = 100; If ($rob_total){$redis->watch("mywatchkey"); $redis->multi(); // Start a new transaction on the current connection. $redis->set(" mywatchKey ",$mywatchKey +1); $rob_result = $redis->exec(); if($rob_result){ $redis->hSet("watchkeylist","user_".mt_rand(1, 9999),$mywatchkey); $mywatchlist = $redis->hGetAll("watchkeylist"); Echo "Buy it! <br/>"; $rob_total-$myWatchKey -1)."<br/>"; Echo "user: <pre>"; var_dump($mywatchlist); }else{ $redis->hSet("watchkeylist","user_".mt_rand(1, 9999),'meiqiangdao'); Echo "Out of luck, snap up more!" ; exit; }}Copy the code

conclusion

1. Remember that the locking mechanism must work in a transaction, which is based on the MySQL InnoDB engine. 2. Pessimistic locks should be used when traffic is low and there is no pressure. Optimistic locks should be used when there is high concurrency. 3. Optimistic lock is used for frequent reads and pessimistic lock is used for frequent writes. One more thing: Optimistic locking does not solve the problem of dirty reads.