本篇文章給大家分享的是有關(guān)MySQL中怎么實(shí)現(xiàn)偽行級(jí)鎖，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)！專注于網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、成都小程序開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了沈丘免費(fèi)建站歡迎大家使用！

 
一.數(shù)據(jù)準(zhǔn)備
mysql> use test;
Database changed
mysql> show create table t_kenyon \G
*************************** 1. row ***************************
       Table: t_kenyon
Create Table: CREATE TABLE `t_kenyon` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
  www.2cto.com  
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> select * from t_kenyon;
+------+
| id   |
+------+
|    1 |
|  123 |
|  789 |
|  345 |
|   78 |
|   78 |
+------+
6 rows in set (0.00 sec)
以上是測(cè)試表t_kenyon,設(shè)置提交方式為手動(dòng)提交. 
 
二.過程(開啟兩個(gè)session,分別設(shè)置autocommit=off) 
  www.2cto.com  
1.session one update
mysql> update t_kenyon set id = 999 where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from t_kenyon;
+------+
| id   |
+------+
|  999 |
|  123 |
|  789 |
|  345 |
|   78 |
|   78 |
+------+
6 rows in set (0.00 sec)
2.session two update
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
  www.2cto.com  
mysql> select * from t_kenyon;
+------+
| id   |
+------+
|    1 |
|  123 |
|  789 |
|  345 |
|   78 |
|   78 |
+------+
6 rows in set (0.00 sec)
 
mysql> update t_kenyon set id = 88888 where id = 345;
第二個(gè)session更新的值是345，但是也一直被阻塞，直到session1被rollback或者commit,如果session1未做回滾或者提交，session2中的該阻塞在超出mysql的鎖時(shí)間限制時(shí)自動(dòng)回滾,該參數(shù)為innodb_lock_wait_timeout，默認(rèn)值50秒 現(xiàn)象如下
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
加索引后的測(cè)試 
3.session one update
 
mysql> create index ind_kenyon on t_kenyon(id);
Query OK, 0 rows affected (28.58 sec)
Records: 0  Duplicates: 0  Warnings: 0
  www.2cto.com  
mysql> update t_kenyon set id = 999 where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from t_kenyon;
+------+
| id   |
+------+
|   78 |
|   78 |
|  123 |
|  345 |
|  789 |
|  999 |
+------+
6 rows in set (0.00 sec)
4.session two update
 mysql> select * from t_kenyon;
+------+
| id   |
+------+
|    1 |
|   78 |
|   78 |
|  123 |
|  345 |
|  789 |
+------+
6 rows in set (0.00 sec)
  www.2cto.com  
mysql> update t_kenyon set id = 7777 where id = 345;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from t_kenyon;
+------+
| id   |
+------+
|    1 |
|   78 |
|   78 |
|  123 |
|  789 |
| 7777 |
+------+
6 rows in set (0.00 sec)
執(zhí)行計(jì)劃
mysql> explain select * from t_kenyon where id = 345 \G
*************************** 1. row ***************************
           id: 1  www.2cto.com  
  select_type: SIMPLE
        table: t_kenyon
         type: ref
possible_keys: ind_kenyon
          key: ind_kenyon
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)
可以看到加了索引后，不同的數(shù)據(jù)更新并沒有被阻塞，實(shí)現(xiàn)了真正意義上行鎖 

以上就是MYSQL中怎么實(shí)現(xiàn)偽行級(jí)鎖，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。