mysql加锁分析 - 1_mysql 加锁分析-CSDN博客

本文链接：https://blog.csdn.net/qq_25911001/article/details/82998164

分析场景
1.1 工具：mysql5.5.53，在windows开启两个mysql命令行，m1、m2、m3，m1执行更新、删除操作，m2查询，m3查询事务
1.2 表结构：两个字段，一个id，一个price
1.3 删除SQL：delete from t1 where id=x limit 1
1.4 隔离级别
```
# 设置m1、m2隔离级别为RC
set session tx_isolation='read-committed';
# 查看m1、m2隔离级别是否设置成功
select @@session.tx_isolation;
```

分析开始
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)