分析开始
2.1# 创建t1表,id是主键,price是唯一键,使用innodb引擎
create table t1(id int(20) not null auto_increment,price int(10) not null,primary key(id),unique key(price))engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci;
# m1插入1条数据
insert into t1(id,price) values(8,88888);
# m1、m2查询结果
select * from t1;
+----+-------+
| id | price |
+----+-------+
| 8 | 88888 |
+----+-------+
# m1开启事务,执行删除
begin;
delete from t1 where id=8 limit 1;
# m3查询事务
① select * from information_schema.innodb_trx\G;
② show engine innodb status\G;
------------
TRANSACTIONS
------------
Trx id counter 258C35
Purge done for trx's n:o < 258C33 undo n:o < 0
History list length 766
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started # m3事务未开启
MySQL thread id 3, OS thread handle 0x9ec0, query id 637 localhost 127.0.0.1 root
show engine innodb status
---TRANSACTION 258C31, not started # m2事务未开启
MySQL thread id 2, OS thread handle 0x9f84, query id 631 localhost 127.0.0.1 root
---TRANSACTION 258C34, ACTIVE 29 sec # m1事务开启,已持续29秒
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1 # 2个锁结构体,堆区占用320KB,1个X锁,1个回滚记录
MySQL thread id 1, OS thread handle 0xa2d0, query id 636 localhost 127.0.0.1 root
--------
# m1查询(当前读)
select * from t1;
empty set;
# m2查询有数据,m1提交后,m2就查询不到数据(幻读)
select * from t1;
+----+-------+
| id | price |
+----+-------+
| 8 | 88888 |
+----+-------+
2.2# 创建t2表,id是唯一键,price是主键,使用innodb引擎
create table t2(id int(20) not null ,price int(10) not null,primary key(price),unique key(id))engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci;
# m1插入1条数据
insert into t2(id,price) values(1,10);
# m1开启事务,执行删除
begin;
delete from t2 where id=1;
# m3查询事务
① select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 258C49
trx_state: RUNNING
trx_started: 2018-10-10 16:49:46
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 2
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 3
trx_lock_memory_bytes: 320
trx_rows_locked: 2 # 两个行锁,一个二级索引的X锁,一个主键的X锁
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 rows in set (0.00 sec)
2.3# 创建t3表,id是普通索引,price是主键,使用innodb引擎
create table t3(id int(20) not null ,price int(10) not null,primary key(price),key(id))engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci;
# m1插入4条数据
insert into t3(id,price) values(1,10),(1,20),(1,30),(1,40);
# m1开启事务,执行删除
begin;
delete from t3 where id=1;
# m3查询事务
① select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 258C59
trx_state: RUNNING
trx_started: 2018-10-10 17:11:31
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 6
trx_mysql_thread_id: 2
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 320
trx_rows_locked: 4 # 4行被锁定,实际应该有8个,4个普通索引X锁,4个主键X锁
trx_rows_modified: 4
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
2.4# 创建t4表,id无索引,price是主键,使用innodb引擎
create table t4(id int(20) not null ,price int(10) not null,primary key(price))engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci;
# m1插入5条数据
insert into t4(id,price) values(1,10),(1,20),(1,30),(1,40),(2,50);
# m1开启事务,执行删除
begin;
delete from t4 where id=1;
# m3查询事务
① select * from information_schema.innodb_trx\G;
② 查询事务id:SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
*************************** 1. row ***************************
trx_id: 258C61
trx_state: RUNNING
trx_started: 2018-10-10 17:21:22
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 6
trx_mysql_thread_id: 2
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 320
trx_rows_locked: 4 # 4个行锁,实际上是5个行锁,mysql数据引擎层会对所有行加X锁,mysql服务层会对不符合条件的解锁(使用了semi-consistent read)
trx_rows_modified: 4
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
2.5 在RR隔离级别下,2.1、2.2加锁情况相同,2.3、2.4会加上间隙锁,锁默认不会提前释放
2.6 死锁执行sql顺序(id为主键):
m1:delete from t1 where id=14;
m2:delete from t1 where id=13;
m1:delete from t1 where id=13;
m2:delete from t1 where id=14;
------------------------
LATEST DETECTED DEADLOCK
------------------------
181011 10:21:21
*** (1) TRANSACTION:
TRANSACTION 258CB6, ACTIVE 68 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0xa2d0, query id 985 localhost 127.0.0.1 root updating
delete from t1 where id=13
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 163855 n bits 72 index `PRIMARY` of table `trx`.`t1` trx id 258CB6 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 000000258cb4; asc % ;;
2: len 7; hex 1b000380022cea; asc , ;;
3: len 4; hex 80000309; asc ;;
*** (2) TRANSACTION:
TRANSACTION 258CB4, ACTIVE 154 sec starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x9f84, query id 986 localhost 127.0.0.1 root updating
delete from t1 where id=14
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 163855 n bits 72 index `PRIMARY` of table `trx`.`t1` trx id 258CB4 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 000000258cb4; asc % ;;
2: len 7; hex 1b000380022cea; asc , ;;
3: len 4; hex 80000309; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 163855 n bits 72 index `PRIMARY` of table `trx`.`t1` trx id 258CB4 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 8000000e; asc ;;
1: len 6; hex 000000258cb6; asc % ;;
2: len 7; hex 1c000380132b35; asc +5;;
3: len 4; hex 8000029a; asc ;;
*** WE ROLL BACK TRANSACTION (2)