D有主键有索引
D有主键有索引 普通索引
create table t (id int(11) not null,name char(20) default null,primary key (id),key idx_name(name))engine=innodb default charset=utf8;
insert into t values(10,'donghongyu'),(20,'lichun'),(30,'luoxiaobo');
无where条件
select * from t for update;
RR
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| 139674832772312:3132:139674752459776 | 82964416 | 64 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139674832772312:2071:5:1:139674752456720 | 82964416 | 64 | yqtest | t | idx_name | RECORD | X | GRANTED | supremum pseudo-record |
| 139674832772312:2071:5:2:139674752456720 | 82964416 | 64 | yqtest | t | idx_name | RECORD | X | GRANTED | 'donghongyu ', 10 |
| 139674832772312:2071:5:3:139674752456720 | 82964416 | 64 | yqtest | t | idx_name | RECORD | X | GRANTED | 'lichun ', 20 |
| 139674832772312:2071:5:4:139674752456720 | 82964416 | 64 | yqtest | t | idx_name | RECORD | X | GRANTED | 'luoxiaobo ', 30 |
| 139674832772312:2071:4:2:139674752457064 | 82964416 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| 139674832772312:2071:4:3:139674752457064 | 82964416 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 20 |
| 139674832772312:2071:4:4:139674752457064 | 82964416 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 30 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
8 rows in set (0.00 sec)
RC
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| 139859491454168:3132:139859368944640 | 82964418 | 65 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139859491454168:2071:5:2:139859368941584 | 82964418 | 65 | yqtest | t | idx_name | RECORD | X,REC_NOT_GAP | GRANTED | 'donghongyu ', 10 |
| 139859491454168:2071:5:3:139859368941584 | 82964418 | 65 | yqtest | t | idx_name | RECORD | X,REC_NOT_GAP | GRANTED | 'lichun ', 20 |
| 139859491454168:2071:5:4:139859368941584 | 82964418 | 65 | yqtest | t | idx_name | RECORD | X,REC_NOT_GAP | GRANTED | 'luoxiaobo ', 30 |
| 139859491454168:2071:4:2:139859368941928 | 82964418 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| 139859491454168:2071:4:3:139859368941928 | 82964418 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 20 |
| 139859491454168:2071:4:4:139859368941928 | 82964418 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 30 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
7 rows in set (0.00 sec)
D有主键有索引 普通索引
where 条件是普通索引
begin;
select * from t where name='donghongyu' for update;
RR
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| 139674832772312:3132:139674752459776 | 82964417 | 64 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139674832772312:2071:5:2:139674752456720 | 82964417 | 64 | yqtest | t | idx_name | RECORD | X | GRANTED | 'donghongyu ', 10 |
| 139674832772312:2071:4:2:139674752457064 | 82964417 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| 139674832772312:2071:5:3:139674752457408 | 82964417 | 64 | yqtest | t | idx_name | RECORD | X,GAP | GRANTED | 'lichun ', 20 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
4 rows in set (0.00 sec)
mysql>
RC
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| 139859491454168:3132:139859368944640 | 82964419 | 65 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139859491454168:2071:5:2:139859368941584 | 82964419 | 65 | yqtest | t | idx_name | RECORD | X,REC_NOT_GAP | GRANTED | 'donghongyu ', 10 |
| 139859491454168:2071:4:2:139859368941928 | 82964419 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
3 rows in set (0.00 sec)
D有主键有索引 普通索引
where 条件是主键
begin;
select * from t where id=10 for update;
RR
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 139674832772312:3132:139674752459776 | 82964418 | 64 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139674832772312:2071:4:2:139674752456720 | 82964418 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
mysql>
RC
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 139859491454168:3132:139859368944640 | 82964420 | 65 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139859491454168:2071:4:2:139859368941584 | 82964420 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
D有主键有索引 普通索引
where 条件是主键字段+普通索引字段 --使用主键 略难以模拟
RR
RC
D有主键有索引 普通索引
where 条件是主键字段+普通索引字段 --使用普通索引字段 略难以模拟
RR
RC
D有主键有索引 唯一索引
alter table t drop index idx_name;
create unique index idx_name on t (name);
D有主键有索引 唯一索引
不带where 条件
RR
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| 139674832772312:3132:139674752459776 | 82964436 | 64 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139674832772312:2071:5:1:139674752456720 | 82964436 | 64 | yqtest | t | idx_name | RECORD | X | GRANTED | supremum pseudo-record |
| 139674832772312:2071:5:2:139674752456720 | 82964436 | 64 | yqtest | t | idx_name | RECORD | X | GRANTED | 'donghongyu ', 10 |
| 139674832772312:2071:5:3:139674752456720 | 82964436 | 64 | yqtest | t | idx_name | RECORD | X | GRANTED | 'lichun ', 20 |
| 139674832772312:2071:5:4:139674752456720 | 82964436 | 64 | yqtest | t | idx_name | RECORD | X | GRANTED | 'luoxiaobo ', 30 |
| 139674832772312:2071:4:2:139674752457064 | 82964436 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| 139674832772312:2071:4:3:139674752457064 | 82964436 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 20 |
| 139674832772312:2071:4:4:139674752457064 | 82964436 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 30 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
8 rows in set (0.00 sec)
RC
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| 139859491454168:3132:139859368944640 | 82964438 | 65 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139859491454168:2071:5:2:139859368941584 | 82964438 | 65 | yqtest | t | idx_name | RECORD | X,REC_NOT_GAP | GRANTED | 'donghongyu ', 10 |
| 139859491454168:2071:5:3:139859368941584 | 82964438 | 65 | yqtest | t | idx_name | RECORD | X,REC_NOT_GAP | GRANTED | 'lichun ', 20 |
| 139859491454168:2071:5:4:139859368941584 | 82964438 | 65 | yqtest | t | idx_name | RECORD | X,REC_NOT_GAP | GRANTED | 'luoxiaobo ', 30 |
| 139859491454168:2071:4:2:139859368941928 | 82964438 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| 139859491454168:2071:4:3:139859368941928 | 82964438 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 20 |
| 139859491454168:2071:4:4:139859368941928 | 82964438 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 30 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
7 rows in set (0.00 sec)
D有主键有索引 唯一索引
where 条件是唯一索引 select * from t where name='donghongyu' for update;
RR
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| 139674832772312:3132:139674752459776 | 82964437 | 64 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139674832772312:2071:5:2:139674752456720 | 82964437 | 64 | yqtest | t | idx_name | RECORD | X,REC_NOT_GAP | GRANTED | 'donghongyu ', 10 |
| 139674832772312:2071:4:2:139674752457064 | 82964437 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
3 rows in set (0.00 sec)
mysql>
RC
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
| 139859491454168:3132:139859368944640 | 82964439 | 65 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139859491454168:2071:5:2:139859368941584 | 82964439 | 65 | yqtest | t | idx_name | RECORD | X,REC_NOT_GAP | GRANTED | 'donghongyu ', 10 |
| 139859491454168:2071:4:2:139859368941928 | 82964439 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+----------------------------+
3 rows in set (0.00 sec)
D有主键有索引 唯一索引
where 条件是主键 select * from t where id=10 for update;
RR
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 139674832772312:3132:139674752459776 | 82964438 | 64 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139674832772312:2071:4:2:139674752456720 | 82964438 | 64 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
mysql>
RC
mysql> select engine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 139859491454168:3132:139859368944640 | 82964440 | 65 | yqtest | t | NULL | TABLE | IX | GRANTED | NULL |
| 139859491454168:2071:4:2:139859368941584 | 82964440 | 65 | yqtest | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+------------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
