示例（二）

MySQL示例

系统准备

---

数据初始

---

示例十一

• 行锁与表锁

// 自动提交状态（系统默认）：默认开启自动提交
mysql> select @@autocommit ; 
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set  

// 事务隔离级别：默认为可重复读
mysql> select @@tx_isolation ; 
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set

// example 1 仅使用主键索引且主键数据存在：行锁
mysql> SELECT * FROM T_ORDER WHERE ID = 1 FOR UPDATE ; 
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
+----+-------+--------------+--------+
1 row in set
// console 2 
mysql> SELECT * FROM T_ORDER WHERE ID = 1 FOR UPDATE ;
1205 - Unknown error 1205 // 等待一段时间后，出现此异常

// example 2 仅使用主键索引且主键数据不存在：无锁
// console 1 
mysql> SELECT * FROM T_ORDER WHERE ID = 4 FOR UPDATE ;
Empty set
// console 2 
mysql> SELECT * FROM T_ORDER WHERE ID = 4 FOR UPDATE ;
Empty set // 未出现等待

// example 3 仅使用主键索引且取值不明确，行锁，由explain 分析结果中的 range 结果得出
// console 1
mysql> SELECT * FROM T_ORDER WHERE ID >= 2 FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  2 | 订单2 | DD_000000002 |      2 |
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+
2 rows in set
// console 2
mysql> SELECT * FROM T_ORDER WHERE ID = 1 FOR UPDATE ; // 行锁
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

mysql> EXPLAIN SELECT * FROM T_ORDER WHERE ID >= 2 ;
// type = range 不是 all ，即不是全表扫描

// console 1
mysql> SELECT * FROM T_ORDER WHERE ID in( 2,3) FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  2 | 订单2 | DD_000000002 |      2 |
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+
2 rows in set
// console 2
mysql> SELECT * FROM T_ORDER WHERE ID = 1 FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

mysql> EXPLAIN SELECT * FROM T_ORDER WHERE ID IN (2,3) ; // type = range 不是 all ，即不是全表扫描

// 准备
// 添加辅助索引
mysql> alter table t_order add index indx_status(`status`) ;
Query OK, 0 rows affected

// example 4 存在辅助索引且索引值存在：行锁
// console 1
mysql> SELECT * FROM T_ORDER WHERE STATUS = 1 FOR UPDATE;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set
// console 2
mysql> SELECT * FROM T_ORDER WHERE STATUS = 2 FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  2 | 订单2 | DD_000000002 |      2 |
+----+-------+--------------+--------+
1 row in set

// example 5 存在辅助索引且索引值不存在：无锁
// console 1 
mysql> SELECT * FROM T_ORDER WHERE STATUS = 5 FOR UPDATE ;
Empty set
// console 2 
mysql> SELECT * FROM T_ORDER WHERE STATUS = 5 FOR UPDATE ;
Empty set

// example 6 存在辅助索引且索引值不确定
// console 1 
mysql> SELECT * FROM T_ORDER WHERE STATUS > 1 FOR UPDATE;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  2 | 订单2 | DD_000000002 |      2 |
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+
2 rows in set
// console 2
mysql> SELECT * FROM T_ORDER WHERE STATUS = 1 FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

// console 1
SELECT * FROM T_ORDER WHERE STATUS in(2,3) FOR UPDATE;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  2 | 订单2 | DD_000000002 |      2 |
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+
2 rows in set
// console 2
mysql> SELECT * FROM T_ORDER WHERE STATUS = 1 FOR UPDATE ;
mysql> // 等待一段时间后无异常输出且无结果输出；表锁

// console 1 ；分析此SQL 
mysql> EXPLAIN SELECT * FROM T_ORDER WHERE STATUS IN (2,3) ; // range = all 因为 in 的使用导致全表扫描；所以用不到行锁，直接表锁

// example 7 无索引，表锁
// console 1
mysql> SELECT * FROM T_ORDER WHERE NAME = "订单2" FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  2 | 订单2 | DD_000000002 |      2 |
+----+-------+--------------+--------+
1 row in set
// console 2 
mysql> SELECT * FROM T_ORDER WHERE STATUS = 1 FOR UPDATE ;
mysql> 

// example 8 索引与非索引混合使用
// console 1
mysql> SELECT * FROM T_ORDER WHERE NAME = "订单2" AND STATUS = 2 FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  2 | 订单2 | DD_000000002 |      2 |
+----+-------+--------------+--------+
1 row in set
// console 2 
mysql> SELECT * FROM T_ORDER WHERE STATUS = 1 FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

---

示例十二

• select for update 是排它锁，不是意向排它锁

// 若 select for update 是意向排它锁，select lock in share mode 是意向共享锁；那么，在获取一行记录的意向排它锁后，可以再次获取这行记录的意向共享锁；因为意向共享锁和意向排它锁之间是相互兼容的

// T1：CONSOLE 1
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
+----+-------+--------------+--------+
1 row in set

// T2：CONSOLE 2 
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 LOCK IN SHARE MODE ;
1205 - Unknown error 1205 // 阻塞，超时

// 与事务的隔离级别无关，在提交读与可重复读隔离级别分别进行上述操作流程，事务二中会出现等待

// 意向排它锁与意向排它锁之间兼容

// T1：CONSOLE 1
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 FOR UPDATE ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
+----+-------+--------------+--------+
1 row in set

// T2：CONSOLE 2
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 FOR UPDATE ;
1205 - Unknown error 1205