MySQL 5.5 InnoDB表鎖行鎖測試
對於沒有索引的表，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
會話①
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 5.5 MyISAM表鎖測試MySql
- MySQL鎖：03.InnoDB行鎖MySql
- MySQL 5.5 -- innodb_lock_wait 鎖 等待MySqlAI
- mysql innodb的行鎖MySql
- MySQL行級鎖測試MySql
- MySQL/InnoDB中，樂觀鎖、悲觀鎖、共享鎖、排它鎖、行鎖、表鎖、死鎖概念的理解MySql
- MySQL鎖：InnoDB行鎖需要避免的坑MySql
- Mysql在InnoDB引擎下索引失效行級鎖變表鎖案例MySql索引
- InnoDB常用鎖總結（行鎖、間隙鎖、臨鍵鎖、表鎖）
- mysql innodb的行鎖(2)MySql
- mysql innodb的行鎖(3)MySql
- mysql innodb的行鎖(4)MySql
- MySQL 5.7 查詢InnoDB鎖表MySql
- MySQL全域性鎖、表鎖以及行鎖MySql
- Mysql研磨之InnoDB行鎖模式MySql模式
- mysql innodb的行鎖(5) --next-Key 鎖MySql
- MySQL索引失效行鎖變表鎖MySql索引
- MySQL InnoDB行鎖優化建議MySql優化
- mysql行鎖和死鎖檢測MySql
- MySQL InnoDB設定死鎖檢測的方法MySql
- mysql innodb的行鎖(6) --不安全語句加鎖MySql
- Mysql innodb引擎（二）鎖MySql
- mysql for update是鎖表還是鎖行MySql
- MySQL中InnoDB鎖機制介紹及一些測試MySql
- mysql innodb 索引失效問題引起表級鎖MySql索引
- 詳解 MySql InnoDB 中的三種行鎖（記錄鎖、間隙鎖與臨鍵鎖）MySql
- MySQL行鎖、表鎖、間隙鎖，你都瞭解嗎MySql
- mysql事務和鎖InnoDBMySql
- mysql innodb間隙鎖示例MySql
- 測試MySQL鎖的問題MySql
- MySQL優化篇系列文章（二）——MyISAM表鎖與InnoDB鎖問題MySql優化
- 全域性鎖、表鎖、行鎖
- 【問答分享第一彈】MySQL鎖總結:MySQL行鎖、表鎖、排他鎖、共享鎖的特點MySql
- 一文搞懂MySQL行鎖、表鎖、間隙鎖詳解MySql
- MySQL表鎖MySql
- MySQL -- 表鎖MySql
- Mysql不鎖表進行MysqldumpMySql
- InnoDB行鎖實現方式