SELECT … FOR UPDATE如何影响INNODB的锁级别
isvx2809
8年前
<h2>SELECT … FOR UPDATE如何影响INNODB的锁级别</h2> <p>如果 SELECT ... FOR UPDATE 生效，需要在noautocommit的环境下，即 BEGIN;COMMIT/ROLLBACK; 或者 SET AUTOCOMMIT=0 的前提下。本文使用 BEGIN;COMMIT/ROLLBACK; 创造noautocommit的环境研究 SELECT ... FOR UPDATE 对于INNODB的锁级别影响。</p> <p>约定</p> <ul> <li> <p>表结构如下</p> <pre> <code class="language-sql">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</code></pre> </li> <li> <p>表数据如下</p> <pre> <code class="language-sql">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)</code></pre> </li> </ul> <h3>实验</h3> <p>一、WHERE条件使用主键</p> <pre> <code class="language-sql"># 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> <p>二、WHERE条件使用唯一索引</p> <pre> <code class="language-sql"># 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> <p>三、WHERE条件使用普通索引</p> <pre> <code class="language-sql"># 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> <p>四、WHERE条件使用联合索引的前缀索引</p> <pre> <code class="language-sql"># 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> <p>五、WHERE条件不使用索引</p> <pre> <code class="language-sql"># 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> <h3>总结</h3> <ol> <li>WHERE条件使用主键， SELECT ... FOR UPDATE 为行级锁；</li> <li>WHERE条件使用唯一索引， SELECT ... FOR UPDATE 为行级锁；</li> <li>WHERE条件使用普通索引， SELECT ... FOR UPDATE 为行级锁；</li> <li>WHERE条件使用联合索引的前缀索引， SELECT ... FOR UPDATE 为行级锁；</li> <li>WHERE条件不使用索引， SELECT ... FOR UPDATE 为表级锁；</li> </ol> <p>即：WHERE条件能使用索引时， SELECT ... FOR UPDATE 表现为行级锁；WHERE条件不使用索引， SELECT ... FOR UPDATE 表现为表级锁；</p> <p> </p> <p>来自：https://github.com/wing324/helloworld_zh/blob/master/MySQL/SELECT … FOR UPDATE如何影响INNODB的锁级别.md</p> <p> </p>