MySQL lock implementation

1. Pessimistic lock

   Pessimistic Locking is always pessimistic. Every time you read data, you think others will modify it, so you lock it every time you read data. In this way, others will block the data they want to read until it obtains the lock (shared resources are only used by one thread at a time, blocked by other threads, and then transfer the resources to other threads after they are used up). Many Pessimistic Locking mechanisms are used in traditional relational databases, such as row lock, table lock, read lock and write lock. They are locked before operation.

2. Optimistic lock
   Optimistic Locking is always optimistic that every time you read data, you think others will not modify it, so you will not lock it. However, when updating, you will judge whether there are other threads updating the data during this period, which can be realized by using version number mechanism and CAS algorithm. Optimistic Locking is suitable for multi read applications, which can improve throughput.

   CAS is the abbreviation of the English word Compare and Swap, which is translated to compare and replace. Three basic operands are used in CAS mechanism: memory address V, old expected value A and new value B to be modified. When updating A variable, the value corresponding to memory address V will be changed to B only when the expected value A of the variable is the same as the actual value in memory address v.

Pessimistic lock use

1. Create a database

   DROP DATABASE IF EXISTS locktest;
   CREATE DATABASE locktest;
   
   USE locktest;
   
   DROP TABLE IF EXISTS warehouse;
   
   CREATE TABLE IF NOT EXISTS warehouse (
     id      INTEGER NOT NULL,
     stock   INTEGER DEFAULT 0,
     version INTEGER DEFAULT 1,
     PRIMARY KEY (id)
   )
     ENGINE = INNODB;
   
   INSERT INTO warehouse VALUE (1, 200, 1);
   
   SELECT * FROM warehouse;

   id	stock	version
   1	200	1

2. Open 2 MySQL terminals and turn off automatic submission: set autocommit = 0;

   Autocommit only works for engines that support transactions, such as InnoDB. By default, the value of autocommit is 1. MySQL will automatically start transactions for write operations by default. When autocommit = 1, the commit operation will be executed during the operation

   set autocommit = 0;

3. The first terminal executes the query. Here, we use the for update keyword to lock the table

   select * from warehouse where id = 1 for update; 

   mysql> select * from warehouse where id = 1 for update; 
   +----+-------+---------+
   | id | stock | version |
   +----+-------+---------+
   |  1 |     0 |       1 |
   +----+-------+---------+
    1 row in set (0.04 sec)
   

4. At this time, we also execute a query at the second terminal, and we will find that the query is suspended

   mysql> select * from warehouse where id = 1 for update; 
   ............Infinite waiting................

5. This is because we first turn off automatic submission, and the query operation of the first terminal is not submitted. At this time, the query statement executed at the second terminal will be suspended and wait for the previous operation to be submitted

6. We submitted the operation update of the first terminal

   update warehouse set stock = stock - 1 where id = 1;
   commit; 

7. We will find that the query of the second terminal is finally executed. We can find that this operation is suspended for 15 seconds until the previous operation commits the transaction

   mysql> select * from warehouse where id = 1 for update; 
   +----+-------+---------+
   | id | stock | version |
   +----+-------+---------+
   |  1 |   199 |       1 |
   +----+-------+---------+
    1 row in set (15.32 sec)
   
   mysql> 

8. Here we can draw A conclusion: when performing operation A, MySQL pessimistic lock can prevent operation B from being executed until the transaction is committed for the first time. The actual use can solve the concurrency problem of overbought shopping malls (of course, Redis and other cache servers must be used for high concurrency, otherwise the performance and speed of the database are not enough)

High concurrency test

1. Prepare interface

   • Here we use PHP to write a snapping interface

     $dsn = array(
     'host' => '127.0.0.1',         //Set server address
     'port' => '3306',              //Set port
     'dbname' => 'locktest',             //Set database name
     'username' => 'root',           //Set account
     'password' => 'root',      //Set password
     'charset' => 'utf8',             //Set encoding format
     'dsn' => 'mysql:host=127.0.0.1;dbname=locktest;port=3306;charset=utf8',
     );
     //connect
     $options = array(
     PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //The default is PDO::ERRMODE_SILENT, 0, (ignore error mode)
     PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // The default is PDO::FETCH_BOTH, 4
     );
     
     try{
     $db = new PDO($dsn['dsn'], $dsn['username'], $dsn['password'], $options);
     }catch(PDOException $e){
     die('Database connection failed:' . $e->getMessage());
     }
     
     
     $list = $db->query('SELECT * FROM `warehouse` WHERE `id` = 1')->fetchAll();
     if ($list[0]['stock'] >= 1)
     {
     $db->exec("UPDATE `warehouse` SET `stock` = `stock` - 1 WHERE `id` = 1");
     print_r("Bought one");
     }else{
     print_r("Inventory is gone:" . $list[0]['stock']);
     }
     

2. Install jmeter

   • JMeter is a Java based stress testing tool developed by Apache. It was originally designed for Web application testing, but later extended to other testing fields.
   • Download jmeter
   • Click here to download
     
   • Unzip JMeter to a place, enter the bin directory, and open jmeter.sh or jmeter.bat (SH needs to install the SH script interpreter in advance, and bat can be opened with cmd)
     
   • Open it like this
     
   • Right click to add a thread group
     
   • Add an http request at
     
   • Add a query result tree
     
   • We can set concurrency here. The number of threads is set to 500, which means that 500 threads access the interface at the same time, and the number of cycles is set to 5. This means that each thread is accessed 5 times in a loop
     
   • Fill our interface into the HTTP request
     

3. Start test

   • Click the green start button
     
   • If you click start for the first time, you will be prompted whether to save the jmx configuration file. We can save it somewhere
   • View results
     
   • We find that the inventory becomes negative, which is not allowed. If this is a mall, overbought will bear a lot of losses. In fact, we judge the inventory in the interface, but because it is executed concurrently, there will be the problem of querying the database at the same time and overbought
     

   • The pessimistic lock is used in the query SQL in the interface, where the transaction needs to be opened
     After modification:

     // Open transaction
     $db->beginTransaction();
     
     $list = $db->query('SELECT * FROM `warehouse` WHERE `id` = 1 FOR UPDATE')->fetchAll();
     if ($list[0]['stock'] >= 1)
     {
     $db->exec("UPDATE `warehouse` SET `stock` = `stock` - 1 WHERE `id` = 1");
     print_r("Bought one");
     $db->commit();
     }else{
     print_r("Inventory is gone:" . $list[0]['stock']);
     }
     

   • Let's add back the inventory in the database to 200. After trying again, we will find that the problem of overbought has been solved
     

4. Conclusion of using pessimistic lock

   • In the SELECT query in the interface, we lock the inventory table with a table lock, which will make others stop first. After we commit the transaction, others can query, so as to prevent the problem of overbought
   • You can know that every query will lock the table, so pessimistic locking will have additional unnecessary overhead in the scenario of multiple reads, which is more suitable for the environment of multiple writes
   • Because pessimistic locks are very aggressive in locking the table, if there is a problem with the interface of the locking table, subsequent queries will be suspended forever, resulting in deadlock
   • The lock table can only be used under the database engine that supports transactions
   • For scenarios with high concurrency, such as seckill activity, Redis and other cache servers are recommended