MySQL 5.5 InnoDB表锁行锁测试

「爱情、让人受尽委屈。」 2024-04-18 00:05 54阅读 0赞

**对于没有索引的表，MySQL会使用表级锁，写操作不会阻塞读操作，读操作不会阻塞写操作；一个会话的写操作会对整张表加锁，其他会话想修改表需要等到这个会话提交或回滚事务。**  
  
 会话①  
 mysql> create table t12(id tinyint(3) unsigned not null,  
     ->       name varchar(10) not null)  
     ->       engine=innodb auto\_increment=8 default charset=gbk;  
 Query OK, 0 rows affected (0.12 sec)  
  
 mysql> show keys from t12;  
 Empty set (0.00 sec)  
  
 mysql> show variables like '%commit%';  
 \+--------------------------------+-------+  
 | Variable\_name                  | Value |  
 \+--------------------------------+-------+  
 | autocommit                     | ON    |  
 | innodb\_commit\_concurrency      | 0     |  
 | innodb\_flush\_log\_at\_trx\_commit | 1     |  
 \+--------------------------------+-------+  
 3 rows in set (0.00 sec)  
  
 mysql> set autocommit=0;  
 Query OK, 0 rows affected (0.00 sec)  
  
 mysql> show variables like '%commit%';  
 \+--------------------------------+-------+  
 | Variable\_name                  | Value |  
 \+--------------------------------+-------+  
 | autocommit                     | OFF   |  
 | innodb\_commit\_concurrency      | 0     |  
 | innodb\_flush\_log\_at\_trx\_commit | 1     |  
 \+--------------------------------+-------+  
 3 rows in set (0.00 sec)  
  
 mysql> insert into t12 values(10,'Neo');  
 Query OK, 1 row affected (0.00 sec)  
  
 mysql> commit;  
 Query OK, 0 rows affected (0.00 sec)  
  
 mysql> select \* from t12;  
 \+----+------+  
 | id | name |  
 \+----+------+  
 | 10 | Neo  |  
 \+----+------+  
 1 row in set (0.00 sec)  
  
 会话②  
 mysql> show variables like '%commit%';  
 \+--------------------------------+-------+  
 | Variable\_name                  | Value |  
 \+--------------------------------+-------+  
 | autocommit                     | ON    |  
 | innodb\_commit\_concurrency      | 0     |  
 | innodb\_flush\_log\_at\_trx\_commit | 1     |  
 \+--------------------------------+-------+  
 3 rows in set (0.00 sec)  
  
 mysql> set autocommit=0;  
 Query OK, 0 rows affected (0.00 sec)  
  
 mysql> show variables like '%commit%';  
 \+--------------------------------+-------+  
 | Variable\_name                  | Value |  
 \+--------------------------------+-------+  
 | autocommit                     | OFF   |  
 | innodb\_commit\_concurrency      | 0     |  
 | innodb\_flush\_log\_at\_trx\_commit | 1     |  
 \+--------------------------------+-------+  
 3 rows in set (0.00 sec)  
  
 mysql> begin;  
 Query OK, 0 rows affected (0.00 sec)  
  
 mysql> select \* from t12;  
 \+----+------+  
 | id | name |  
 \+----+------+  
 | 10 | Neo  |  
 \+----+------+  
 1 row in set (0.00 sec)  
  
 会话①  
 mysql> update t12 set name='trinity' where id=10;  
 Query OK, 1 row affected (0.03 sec)  
 Rows matched: 1  Changed: 1  Warnings: 0  
  
 会话②  
 下面DML语句会一直阻塞  
 mysql> insert into t12 values(20,'Trinity');  
  
 过一段时间会出现超时提示  
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  
  
 会话①  
 mysql> rollback;  
 Query OK, 0 rows affected (0.00 sec)  
  
 会话②  
 mysql> insert into t12 values(20,'Trinity');  
 Query OK, 1 row affected (0.00 sec)  
  
 mysql> commit;  
 Query OK, 0 rows affected (0.01 sec)  
  
**对于有索引的表，写操作不会阻塞读操作，读操作不会阻塞写操作；如果在MySQL在写操作时使用索引扫描，则会使用行级锁，一个会话的写操作会对修改的行加锁，其他会话想修改这些行需要等到这个会话提交或回滚事务，其** **他会话对其他行的写操作不受影响，行锁会阻塞表锁** **；如果MySQL使用全表扫描，则会使用表级锁，一个会话的写操作会对整张表加锁，其他会话想修改表需要等到这个会话提交或回滚事务，表锁会阻塞行锁** **。**  
  
 会话①  
 mysql> create index idx\_t12\_id on t12(id);  
 Query OK, 0 rows affected (0.26 sec)  
 Records: 0  Duplicates: 0  Warnings: 0  
  
 查看索引  
 mysql> show keys from t12;  
 \+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | Table | Non\_unique | Key\_name   | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
 \+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 | t12   |          1 | idx\_t12\_id |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |  
 \+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
 1 row in set (0.00 sec)  
  
 查看执行计划  
 mysql> explain select \* from t12 where id=20;  
 \+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
 | id | select\_type | table | type | possible\_keys | key  | key\_len | ref  | rows | Extra       |  
 \+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
 |  1 | SIMPLE      | t12   | ALL  | idx\_t12\_id    | NULL | NULL    | NULL |    1 | Using where |  
 \+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
  
 mysql> delete from t12 where id=20;  
 Query OK, 2 rows affected (0.00 sec)  
  
 mysql> select \* from t12;  
 \+----+------+  
 | id | name |  
 \+----+------+  
 | 10 | Neo  |  
 \+----+------+  
 1 row in set (0.00 sec)  
  
 会话②  
  
 查看执行计划  
 mysql> explain select \* from t12 where id=10;  
 \+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+  
 | id | select\_type | table | type | possible\_keys | key        | key\_len | ref   | rows | Extra |  
 \+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+  
 |  1 | SIMPLE      | t12   | ref  | idx\_t12\_id    | idx\_t12\_id | 1       | const |    1 |       |  
 \+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+  
 1 row in set (0.00 sec)  
  
 mysql> update t12 set name='Jack' where id=10;  
 Query OK, 1 row affected (0.00 sec)  
 Rows matched: 1  Changed: 1  Warnings: 0  
  
 mysql> select \* from t12;  
 \+----+---------+  
 | id | name    |  
 \+----+---------+  
 | 10 | Jack    |  
 | 20 | Trinity |  
 | 20 | Trinity |  
 \+----+---------+  
 3 rows in set (0.00 sec)  
  
 mysql> rollback;  
 Query OK, 0 rows affected (0.01 sec)  
  
 mysql> show processlist;  
 \+----+-----------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+  
 | Id | User            | Host      | db   | Command | Time   | State                                                                       | Info             |  
 \+----+-----------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+  
 |  1 | system user     |           | NULL | Connect | 769140 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |  
 |  2 | system user     |           | NULL | Connect | 769141 | Connecting to master                                                        | NULL             |  
 | 13 | event\_scheduler | localhost | NULL | Daemon  | 621090 | Waiting on empty queue                                                      | NULL             |  
 | 76 | neo             | localhost | fire | Sleep   |    180 |                                                                             | NULL             |  
 | 78 | neo             | localhost | fire | Query   |      0 | NULL                                                                        | show processlist |  
 \+----+-----------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+  
 5 rows in set (0.00 sec)  
  
 mysql> explain select \* from t12;  
 \+----+-------------+-------+------+---------------+------+---------+------+------+-------+  
 | id | select\_type | table | type | possible\_keys | key  | key\_len | ref  | rows | Extra |  
 \+----+-------------+-------+------+---------------+------+---------+------+------+-------+  
 |  1 | SIMPLE      | t12   | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |  
 \+----+-------------+-------+------+---------------+------+---------+------+------+-------+  
 1 row in set (0.00 sec)  
  
 下面的更新没有使用索引而使用全表扫描，这样会加表级锁，会处于阻塞状态。  
 mysql> update t12 set name='Jack';  
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  
  
 会话①  
  
 mysql> rollback;  
 Query OK, 0 rows affected (0.05 sec)  
  
 会话②  
 随着会话①的回滚操作，会话②执行成功  
 mysql> update t12 set name='Jack';  
 Query OK, 3 rows affected (12.41 sec)  
 Rows matched: 3  Changed: 3  Warnings: 0  
  
 mysql> rollback;  
 Query OK, 0 rows affected (0.00 sec)  
  
 会话①  
 mysql> update t12 set name='Jack';  
 Query OK, 3 rows affected (0.00 sec)  
 Rows matched: 3  Changed: 3  Warnings: 0  
  
 会话②  
 插入操作会一直处于阻塞状态  
 mysql> insert into t12 values(30,'Lily');  
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  
  
 会话①  
 mysql> rollback;  
 Query OK, 0 rows affected (0.00 sec)  
  
 会话②  
 mysql> insert into t12 values(30,'Lily');  
 Query OK, 1 row affected (0.09 sec)  
  
两行数据使用了同一个索引，对两个不同的行加锁，也会引起锁等待  
mysql> show create table tab\_with\_index\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: tab\_with\_index  
Create Table: CREATE TABLE \`tab\_with\_index\` (  
  \`id\` int(11) DEFAULT NULL,  
  \`name\` varchar(10) DEFAULT NULL,  
  KEY \`id\` (\`id\`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1  
1 row in set (0.00 sec)  
  
mysql> select \* from tab\_with\_index where id=1;  
\+------+------+  
| id   | name |  
\+------+------+  
|    1 | 1    |  
|    1 | 4    |  
\+------+------+  
2 rows in set (0.00 sec)  
  
mysql> show keys from tab\_with\_index;  
\+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table          | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
\+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| tab\_with\_index |          1 | id       |            1 | id          | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |  
\+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
1 row in set (0.00 sec)  
  
会话①  
使用where id=1 and name='1'条件进行查询  
mysql> select \* from tab\_with\_index where id=1 and name='1' for update;  
\+------+------+  
| id   | name |  
\+------+------+  
|    1 | 1    |  
\+------+------+  
1 row in set (0.00 sec)  
  
会话②  
查询where id=1 and name='4'条件进行查询，由于和会话①使用了相同的索引，即使查询了不同的字段，也会引起锁等待  
mysql> select \* from tab\_with\_index where id=1 and name='4' for update;  
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  
  
在这种情况下，可以考虑创建联合索引  
 会话①  
mysql> create index idx\_id\_name on tab\_with\_index(id,name);  
Query OK, 0 rows affected (0.24 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> select \* from tab\_with\_index where id=1 and name='4' for update;  
\+------+------+  
| id   | name |  
\+------+------+  
|    1 | 4    |  
\+------+------+  
1 row in set (0.00 sec)  
  
会话②  
mysql> select \* from tab\_with\_index where id=1 and name='1' for update;  
\+------+------+  
| id   | name |  
\+------+------+  
|    1 | 1    |  
\+------+------+  
1 row in set (0.00 sec)  
  
InnoDB存储引擎的表使用不同索引的阻塞例子  
  
会话①  
mysql> show create table tab\_with\_index\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: tab\_with\_index  
Create Table: CREATE TABLE \`tab\_with\_index\` (  
  \`id\` int(11) DEFAULT NULL,  
  \`name\` varchar(10) DEFAULT NULL,  
  KEY \`id\` (\`id\`),  
  KEY \`idx\_name\` (\`name\`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1  
1 row in set (0.00 sec)  
  
mysql> desc tab\_with\_index;  
\+-------+-------------+------+-----+---------+-------+  
| Field | Type        | Null | Key | Default | Extra |  
\+-------+-------------+------+-----+---------+-------+  
| id    | int(11)     | YES  | MUL | NULL    |       |  
| name  | varchar(10) | YES  | MUL | NULL    |       |  
\+-------+-------------+------+-----+---------+-------+  
2 rows in set (0.01 sec)  
  
mysql> select \* from tab\_with\_index;  
\+------+------+  
| id   | name |  
\+------+------+  
|    1 | 1    |  
|    2 | 2    |  
|    3 | 3    |  
|    4 | 4    |  
|    1 | 4    |  
\+------+------+  
5 rows in set (0.00 sec)  
  
mysql> explain select \* from tab\_with\_index where id=1 for update;  
\+----+-------------+----------------+------+---------------+------+---------+-------+------+-------+  
| id | select\_type | table          | type | possible\_keys | key  | key\_len | ref   | rows | Extra |  
\+----+-------------+----------------+------+---------------+------+---------+-------+------+-------+  
|  1 | SIMPLE      | tab\_with\_index | ref  | id            | id   | 5       | const |    2 | NULL  |  
\+----+-------------+----------------+------+---------------+------+---------+-------+------+-------+  
1 row in set (0.00 sec)  
  
mysql> select \* from tab\_with\_index where id=1;  
\+------+------+  
| id   | name |  
\+------+------+  
|    1 | 1    |  
|    1 | 4    |  
\+------+------+  
2 rows in set (0.00 sec)  
  
mysql> select \* from tab\_with\_index where id=1 for update;  
\+------+------+  
| id   | name |  
\+------+------+  
|    1 | 1    |  
|    1 | 4    |  
\+------+------+  
2 rows in set (0.01 sec)  
  
会话②  
mysql> explain select \* from tab\_with\_index where name='4' for update;  
\+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+  
| id | select\_type | table          | type | possible\_keys | key      | key\_len | ref   | rows | Extra                 |  
\+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+  
|  1 | SIMPLE      | tab\_with\_index | ref  | idx\_name      | idx\_name | 13      | const |    2 | Using index condition |  
\+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+  
1 row in set (0.00 sec)  
  
mysql> select \* from tab\_with\_index where name='4' for update;  
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2109505/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2109505/