A brief analysis of PHP's simple operation of mysql lock mechanism

Lock mechanism
Shared lock and exclusive lock
Shared lock (read lock): Other transactions can read, but cannot write.
Exclusive lock (write lock): other transactions cannot read or write.

For MySQL, there are three lock levels: page level, table level, row level

• The typical representative engine of the page level is BDB.
• The typical representative engine at the row level is INNODB.
• Typical representative engines at the table level are MyISAM, MEMORY and the long-ago ISAM.
• BDB storage engine uses page-level locking, but also supports table-level locks
• InnoDB storage engine supports both row-level locking (row-level locking) and table-level locking. Table-level locks, but row-level locks are used by default.
• MyISAM and MEMORY storage engines use table-level locks

Related free learning recommendations: php programming (video)

1. MyISAM table lock

MyISAM table-level lock mode:

• Table shared read lock (Table Read Lock): No Will block other users' read requests to the same table, but will block other users' write requests to the same table;
• Table exclusive write lock (Table Write Lock): will block other users' read and write operations on the same table;

MyISAM table lock method:

• Use the LOCK TABLE command to explicitly lock the MyISAM table
• LOCK TABLES real_table (READ|WRITE), insert_table (READ|WRITE); //Lock
• UNLOCK TABLES; //Unlock

Example:
For example, there is account(id,name ,cash), hero (number, name, country) these two tables

1. lock tables account read; add account as a read-only lock
   Current process query: select * from hero; will Report Table 'hero' was not locked with LOCK TABLES.
   The current process changes other tables: update hero set name="ss" where number=1; will report Table 'hero' was not locked with LOCK TABLES
   The current process changes the table: update account set name="ssss" where id=1; it will be reported that Table 'account' was locked with a READ lock and can't be updated
   If another mysql process comes in, you can query other tables and accounts, but you cannot change the account, it will wait forever and needs to be released. The lock is executed

1.1PHP operation

<?php/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2021/4/29 0029
 * Time: 11:20
 */$link = new mysqli(&#39;127.0.0.1&#39;, &#39;root&#39;, &#39;123&#39;, &#39;db_school&#39;); // 连接数据库if(mysqli_connect_errno()){                                // 检查连接错误
    printf("连接失败：%s<br>", mysqli_connect_error());
    exit();}//(s1)$table = "account";$type = "read";$sql1 = "LOCK TABLES $table $type";$link->query($sql1);//处理逻辑//$sql1 = "select * from $table;";        //(s1)true//$sql1 = "select * from hero;";        //false//$sql1 = "update hero set name='ss' where number=1; ";     //false$sql1 = "update account set name='ssss' where id=1;";   //false$result = $link->query($sql1);var_dump($result);sleep(20);  //测试    //假设还没释放锁,开启cmd进mysql（s2）可以查询，但不能执行更改和删除操作，会等待这边释放锁$link->query("unlock tables");    //取消全部的锁//解锁后正常操作//$result = $link -> query($sql1);//var_dump($result);$link->close();

Query table-level lock contention
show status like 'Table%';

• Table_locks_immediate refers to the number of times that table-level locks can be obtained immediately
• Table_locks_waited refers to the number of times that table-level locks cannot be obtained immediately and need to wait

2.InnoDB locking method:

For ordinary SELECT statements, InnoDB will not add any locks; Locking can only be used during transaction execution
The lock can only be used during transaction execution It will only be released when commit or rollback is executed, and all locks will be released at the same time.

• Shared lock(S): SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE. Other sessions can still query the record and add share mode shared locks to the record. However, if the current transaction needs to update the record, it is likely to cause a deadlock. Everyone can read it, but it cannot change it. It can only be changed when one of the exclusive shared locks is locked;
• Exclusive lock (X): SELECT * FROM table_name WHERE ... FOR UPDATE. Other sessions can query the record, but they cannot add shared locks or exclusive locks to the record, but wait to obtain the lock. I want to change it, but you can’t change it, and you can’t read it
  #select … lock in share mode //Shared lock
  #select … for update //Exclusive lock
  ##In MySQL 8.0
• Shared lock (S): SELECT * FROM table_name WHERE … FOR SHARE
• Exclusive lock (X): SELECT * FROM table_name WHERE … FOR UPDATE[NOWAIT|SKIP LOCKED]
  –NOWAIT: Discovery After waiting for a lock, an error will be returned immediately. There is no need to wait for the lock to time out and report an error.
  –SKIP LOCKED: Skip the locked rows and update other rows directly, but be careful whether the update results will not meet expectations.

2.1PHP operation

<?php/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2021/4/29 0029
 * Time: 10:06
 */$link = new mysqli(&#39;127.0.0.1&#39;, &#39;root&#39;, &#39;123&#39;, &#39;db_school&#39;); // 连接数据库if(mysqli_connect_errno()){                                // 检查连接错误
    printf("连接失败：%s<br>", mysqli_connect_error());
    exit();}//案例1$id = 1; //明确指定主键，并且有此数据，row lock (行锁)//$id = -1;   //明确指定主键，若查无此数据，无lock (无锁)$link->autocommit(0);                   // 开始事务（s1）//FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。$sql = "select * from account where id=$id for update";$link->query($sql);/***
 * 此时其他mysql进程可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。
 *///（s1）可以进行更改，和查询等操作//$sql1 = "update account set name='aaa' where id=$id;";  //进行更改//$sql1 = "select * from account where id=$id;";  //进行查询$sql1 = "delete from account where id=$id;";  //进行删除$result = $link -> query($sql1);var_dump($result);sleep(20); //测试    //假设还在事务处理中,开启cmd进mysql（s2）执行更改和删除操作，会等待这边释放锁$link->commit();$link->close();

View the tables being locked
show OPEN TABLES where In_use > 0;
SHOW PROCESSLIST display Which threads are running.

The above is the detailed content of A brief analysis of PHP's simple operation of mysql lock mechanism. For more information, please follow other related articles on the PHP Chinese website!