RR与RC隔离级别下MySQL不同的加锁解锁方式

|  RC与RR隔离级别下MySQL不同的加锁解锁方式

---

• MySQL5.7.21

• 数据准备

root@localhost : pxs 05:26:27> show create table dots\G
*************************** 1. row ***************************
  Table: dots
Create Table: CREATE TABLE `dots` (
`id` int(11) NOT NULL,
`color` varchar(20) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
root@localhost : pxs 05:27:34> select * from dots;
+----+-------+
| id | color |
+----+-------+
|  1 | black |
|  2 | white |
|  3 | black |
|  4 | white |
+----+-------+
4 rows in set (0.00 sec)
root@localhost : pxs 01:57:02> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF  |
+--------------------------------+-------+
1 row in set (0.00 sec)

1.RC隔离级别

• 确认隔离级别

  
  

root@localhost : pxs 05:27:35> show variables like '%iso%';
+-----------------------+----------------+
| Variable_name        | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.01 sec)

• 同时开启两个会话，按下图的流程开始操作。

2.RR隔离级别

• 确认隔离级别

root@localhost : pxs 05:24:41> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name        | Value          |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.01 sec)

• 同时开启两个会话，按下图的流程开始操作。