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;); // 連接數(shù)據(jù)庫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);??//測試????//假設(shè)還沒釋放鎖,開啟cmd進mysql（s2）可以查詢，但不能執(zhí)行更改和刪除操作，會等待這邊釋放鎖$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;); // 連接數(shù)據(jù)庫if(mysqli_connect_errno()){                                // 檢查連接錯誤
    printf("連接失?。?s<br>",?mysqli_connect_error());
????exit();}//案例1$id?=?1;?//明確指定主鍵，并且有此數(shù)據(jù)，row?lock?(行鎖)//$id?=?-1;???//明確指定主鍵，若查無此數(shù)據(jù)，無lock?(無鎖)$link->autocommit(0);???????????????????//?開始事務(wù)（s1）//FOR?UPDATE僅適用于InnoDB，且必須在交易區(qū)塊(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);?//測試????//假設(shè)還在事務(wù)處理中,開啟cmd進mysql（s2）執(zhí)行更改和刪除操作，會等待這邊釋放鎖$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!