DBA MySQL事务锁机制

功能概述

​ MySQL支持多线程操作，这就会造成数据安全问题。

​ 一个用户在修改记录数据时，如果另一个用户也修改相同的记录数据则可能造成数据不一致的问题。

​ 为了解决这个问题，可以使用锁操作来完成，即一个用户修改某一条记录数据时，其他用户只能排队等待上一个用户修改完成。

​ 这在网络购物的库存数量上尤为明显。

数据准备

​ 学习锁相关知识，准备如下数据：

# 库存表
CREATE TABLE in_stock(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    name CHAR(32) NOT NULL COMMENT "商品名称",
    num INT UNSIGNED NOT NULL COMMENT "商品数量",
    info CHAR(64) NOT NULL COMMENT "商品描述",
    version INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "版本号",
    INDEX idx(name) COMMENT "普通索引(name)"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 插入数据
INSERT INTO
    in_stock(name, num, info)
VALUES
    ("PHONE", 100, "手机"),
    ("BOOK", 100, "书籍"),
    ("FOOD", 100, "食物");
    
DROP TABLE IN_SOTCK;

表锁&行锁

基本概念

​ 行锁与表锁现象一定是根据筛选情况的不同而出现的。

​ 筛选条件是索引列时，将会触发行锁现象

​ 筛选条件不是索引列时，将会触发表锁现象

​ 行锁是指用户A修改表中一条记录且筛选条件为索引列时，该条记录不能被其他用户同时修改。

​ 区间行锁是指用户A修改表中多条记录且筛选条件为索引列时，这些记录不能被其他用户同时修改。

​ 表锁是指用户A修改表中一条记录且筛选条件不是索引列时，该表所有记录都不能被其他用户修改。

​ 用户A进行COMMIT提交事务或进行ROLLBACK回滚事务的操作时将会自动解锁

​ InnoDB引擎支持行锁，因此拥有更高的并发处理能力，相反，MyISAM存储引擎仅支持表锁，不支持行锁。

行锁现象

​ 为了模拟并发场景，需要开启两个终端进行测试。

​ 示例一，SESSION1开启事务，对库存表进行修改，筛选条件是普通索引列，此时会引发行锁，SESSION2不可对该行记录进行事务语句操作，但是可以对其他行的记录进行事务语句操作。

# SESSION 1
BEGIN;
UPDATE db1.in_stock SET num = db1.in_stock.num - 1 WHERE name = "BOOK";

# SESSION 2
BEGIN;
UPDATE db1.in_stock SET num = db1.in_stock.num - 1 WHERE name = "FOOD";
# 其他行操作：成功

UPDATE db1.in_stock SET num = db1.in_stock.num - 1 WHERE name = "BOOK";
# 卡住,行锁

表锁现象

​ 为了模拟并发场景，需要开启两个终端进行测试。

​ 示例一，SESSION1开启事务，对库存表进行修改，筛选条件不是索引列，将会引发表锁，对该表所有记录进行事务操作都将等待。

# SESSION 1
BEGIN;
UPDATE db1.in_stock SET num = db1.in_stock.num - 1 WHERE info = "书籍";

# SESSION 2
BEGIN;
UPDATE db1.in_stock SET num = db1.in_stock.num - 1 WHERE name = "FOOD";
# 修改其他记录卡住

UPDATE db1.in_stock SET num = db1.in_stock.num - 1 WHERE name = "BOOK";
# 自己这行也会卡住

UPDATE db1.in_stock SET num = db1.in_stock.num - 1 WHERE name = "PHONE";
# 修改其他记录卡住

悲观锁&乐观锁

基本概念

​ 悲观锁与乐观锁的出现与筛选条件无关，而是和SQL语句有关。

​ 悲观锁是指用户A指定锁住某些记录，其他用户则不能对这些记录进行任何事务操作。

​ 乐观锁更像是一种思路上解决的方案，而并不是用MySQL提供的内部功能进行解决。

​ 用户A进行COMMIT提交事务或进行ROLLBACK回滚事务的操作时将会自动解锁

悲观锁

​ 为了模拟并发场景，需要开启两个终端进行测试。

​ 示例一，SESSION1开启事务，使用悲观锁锁住了某些记录，此时其他的会话SESSION均不能操作该表中被上锁的记录。

# SESSION 1
BEGIN;
SELECT * FROM db1.in_stock WHERE id > 2 FOR UPDATE;
# 释义，我锁住了id > 2的所有记录，即使我只有三条记录

# SESSION 2
BEGIN;
UPDATE db1.in_stock SET num = db1.in_stock.num - 1 WHERE id = 1;
# 操作id < 2的记录，未被锁，执行成功

UPDATE db1.in_stock SET num = db1.in_stock.num - 1 WHERE id = 3;
# 操作id > 2的记录，被锁了，执行失败

INSERT INTO db1.in_stock(name, num, info) VALUES ("TV", 100, "电视");
# 现在新增的记录id为4，id > 2，被锁了，执行失败

乐观锁

​ 乐观锁的解决思路、添加额外的指定条件如version版本信息，在查询、修改时均带上该额外条件。

​ 至于为什么叫乐观锁，原因如下：

​ 在每次去拿数据的时候认为别人不会修改，不对数据进行上锁，但是在提交更新的时候会判断在此期间数据是否被更改，如果被更改则提交失败

​ 为了模拟并发场景，需要开启两个终端进行测试。

​ 示例一，SESSION1开启事务并对产品库存做出调整，同时更改了产品版本号，其他SESSION由于版本号不对则会进行阻塞：

# SESSION 1
BEGIN;
UPDATE db1.in_stock SET
  num = db1.in_stock.num - 10,
  version = db1.in_stock.version + 1
  WHERE version = 0 AND id = 1;

# SESSION 2
BEGIN;
UPDATE db1.in_stock SET
  num = db1.in_stock.num - 10,
  version = db1.in_stock.version + 1
  WHERE version = 0 AND id = 1;
# 版本号不对，阻塞

读锁&写锁

基本概念

​ 针对一些不支持事务的存储引擎，可以使用读锁与写锁的方式来控制业务。

​ 为表设置读锁后，当前会话和其他会话都不可以修改数据，但可以读取表数据。

​ 为表设置了写锁后，当前会话可以修改，查询表，其他会话将无法操作。

读锁

​ 基本语法格式如下：

# 设置读锁
LOCK TABLE 表名 READ;
  业务逻辑
UNLOCK TABLES;
# 解除读锁

写锁

​ 基本语法格式如下：

# 设置写锁
LOCK TABLE 表名 WRITE;
  业务逻辑
UNLOCK TABLES;
# 解除写锁