SELECT … FOR UPDATE如何影響INNODB的鎖級別

isvx2809 7年前發布 | 25K 次閱讀 InnoDB

SELECT … FOR UPDATE如何影響INNODB的鎖級別

如果 SELECT ... FOR UPDATE 生效，需要在noautocommit的環境下，即 BEGIN;COMMIT/ROLLBACK; 或者 SET AUTOCOMMIT=0 的前提下。本文使用 BEGIN;COMMIT/ROLLBACK; 創造noautocommit的環境研究 SELECT ... FOR UPDATE 對于INNODB的鎖級別影響。

約定

表結構如下

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  `num1` int(11) NOT NULL,
  `num2` int(11) NOT NULL,
  `num3` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_name` (`name`),
  KEY `ix_num1_num2` (`num1`,`num2`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8

表數據如下

select * from t;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  1 | AAAAA |    0 |    2 |    2 |
|  2 | BBBBB |    1 |    2 |    2 |
|  3 | CCCCC |    0 |    0 |    0 |
|  4 | DD    |    4 |    1 |    1 |
|  5 | EE    |    0 |    5 |    5 |
| 66 | FFFFF |    0 |    5 |    5 |
+----+-------+------+------+------+
6 rows in set (0.00 sec)

實驗

一、WHERE條件使用主鍵

# session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from t where id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> select * from t where id=1 for update;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  1 | AAAAA |    0 |    2 |    2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)
session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id=2 for update;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  2 | BBBBB |    1 |    2 |    2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from t where name="DD" for update;
+----+------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+------+------+------+------+
|  4 | DD   |    4 |    1 |    1 |
+----+------+------+------+------+
1 row in set (0.00 sec)
mysql> select  from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select  from t where name="AAAAA" for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select  from t where num1=0 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select  from t where num3=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction</code></pre> 
  
二、WHERE條件使用唯一索引 
  # session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from t where name='AAAAA' ;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | const | ux_name       | ux_name | 50      | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where name ="DD" for update;
+----+------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+------+------+------+------+
|  4 | DD   |    4 |    1 |    1 |
+----+------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from t where id=2 for update;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  2 | BBBBB |    1 |    2 |    2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select  from t where name='AAAAA' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select  from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select  from t where num1=0 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select  from t where num3=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction</code></pre> 
  
三、WHERE條件使用普通索引 
  # session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from t where num1=0 and num2=5;
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key          | key_len | ref         | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------+
|  1 | SIMPLE      | t     | ref  | ix_num1_num2  | ix_num1_num2 | 8       | const,const |    2 | NULL  |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------+
1 row in set (0.00 sec)
mysql> select * from t where num1=0 and num2=5 for update;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  5 | EE    |    0 |    5 |    5 |
| 66 | FFFFF |    0 |    5 |    5 |
+----+-------+------+------+------+
2 rows in set (0.00 sec)
session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id=1 for update;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  1 | AAAAA |    0 |    2 |    2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from t where name="AAAAA" for update;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  1 | AAAAA |    0 |    2 |    2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select  from t where num1=0 and num2=5 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select  from t where num2=5 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where num3=5 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction</code></pre> 
  
四、WHERE條件使用聯合索引的前綴索引 
  # session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from t where num1=1 ;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | ix_num1_num2  | ix_num1_num2 | 4       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> select * from t where num1=1 for update;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  2 | BBBBB |    1 |    2 |    2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)
session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id =3;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  3 | CCCCC |    0 |    0 |    0 |
+----+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from t where id =3 for update
    -> ;
+----+-------+------+------+------+
| id | name  | num1 | num2 | num3 |
+----+-------+------+------+------+
|  3 | CCCCC |    0 |    0 |    0 |
+----+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from t where num1=4 for update; -- 使用了普通索引
+----+------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+------+------+------+------+
|  4 | DD   |    4 |    1 |    1 |
+----+------+------+------+------+
1 row in set (0.00 sec)
mysql> explain select * from t where num1=4 for update;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | ix_num1_num2  | ix_num1_num2 | 4       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> select * from t where num1=0 for update; -- 使用了全表掃描
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> explain select * from t where num1=0 for update;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | ix_num1_num2  | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)</code></pre> 
  
五、WHERE條件不使用索引 
  # session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from t where num3=1 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> select * from t where num3=1 for update;
+----+------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+------+------+------+------+
|  4 | DD   |    4 |    1 |    1 |
+----+------+------+------+------+
1 row in set (0.00 sec)
session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select  from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select  from t where name='BBBBB' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction</code></pre> 
  
總結 
   
   WHERE條件使用主鍵， SELECT ... FOR UPDATE 為行級鎖； 
   WHERE條件使用唯一索引， SELECT ... FOR UPDATE 為行級鎖； 
   WHERE條件使用普通索引， SELECT ... FOR UPDATE 為行級鎖； 
   WHERE條件使用聯合索引的前綴索引， SELECT ... FOR UPDATE 為行級鎖； 
   WHERE條件不使用索引， SELECT ... FOR UPDATE 為表級鎖； 
   
  即：WHERE條件能使用索引時， SELECT ... FOR UPDATE 表現為行級鎖；WHERE條件不使用索引， SELECT ... FOR UPDATE 表現為表級鎖； 
    
  來自：https://github.com/wing324/helloworld_zh/blob/master/MySQL/SELECT … FOR UPDATE如何影響INNODB的鎖級別.md


                    
                         本文由用戶 isvx2809 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
                         轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
                         本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！
                         本文地址：http://www.baiduhome.net/lib/view/open1488508163714.html
                         InnoDB

SELECT … FOR UPDATE如何影響INNODB的鎖級別

SELECT … FOR UPDATE如何影響INNODB的鎖級別

實驗

session 2

session 2

session 2

session 2

session 2

總結

相關經驗

相關資訊

相關文檔

目錄