本文共 12416 字，大约阅读时间需要 41 分钟。
对于没有索引的表，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/