MySQL的锁

1、MySQL的锁的基本介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

（InnoDB 存储引擎（MySQL 默认存储引擎，默认可重复读隔离级别）中）数据库事务的锁是执行某语句时就会加锁，而且只有当整个事务完成（提交或回滚）后，锁（排他行锁、共享行锁等等）才会被释放。例如，有一个事务 T1，在其中执行了UPDATE users SET user_email = 'new_email@example.com' WHERE user_id = 1001。当这条UPDATE语句执行后，排他行锁就会锁住user_id = 1001对应的行。即使UPDATE语句执行完成，只要事务 T1 还没有提交或者回滚，这个排他行锁就会一直存在。

1.1、数据库锁的分类

MySQL 的锁按照范围可以分为全局锁（对整个数据库实例进行加锁）、表锁、行锁，其中行锁是由数据库引擎实现的，并不是所有的引擎都提供行锁，MyISAM 就不支持行锁。

从对数据操作的类型：

共享锁（读锁）：针对同一行数据，多个读操作可以同时进行而不会互相影响，但会阻塞写操作。
排它锁（写锁）：当前写操作没有完成前，它会阻断其他会话的写和读。

共享锁会阻塞涉及到的数据的写，但是不会堵塞读；而排它锁则会把涉及到的数据的读和写都堵塞。也就是共享锁和排它锁都会阻塞涉及到的数据的写操作。

从对数据操作的粒度分：

表级锁：开销小，加锁快；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；锁定粒度界于表锁和行锁之间，并发度一般

2、表级锁（共享锁、排它锁）

2.1、表级锁的分类

2.1.1、共享表锁（读锁）

假设有会话 A 和会话 B，会话 A 对 mylock 表加了读锁，则：

会话A ：可以查该表的数据，但是无法增删改该表的数据，并且也无法查询其他表的数据，即使其他表未被锁定也无法查询，直到对该锁表进行释放锁。
会话B ：可以查询该锁表的数据，也可以查询、增删改其他表的数据。但是在对已锁的数据进行增删改时，会一直阻塞，导致SQL无法执行结束，直到该表锁被释放后，SQL会自动执行结束。

	已锁定表	其他表
会话A（加锁的会话）	可查；但无法增删改	无法增删改查（会报错）
其他会话	可查；但无法增删改（会一直阻塞）	可增删改查

也就是加共享锁的会话可以查锁表数据，但加锁会话无法增删改该表，也无法查询其他表。其他的会话可以查询任何表的数据，但其他会话对锁表进行增删改时也会阻塞，直到锁表的锁被释放。

总结：共享锁会阻塞写，不会阻塞读。

2.1.2、排它表锁（写锁）

假设有会话 A 和会话 B，会话 A 对 mylock 表加了写锁，则：

会话A ：可以查该表的数据，也可以对该锁定表进行增删改，但是无法查询其他表的数据，即使其他表未被锁定也无法查询，直到对已锁定表进行释放锁。
会话B ：对已锁定表进行增删改查都会一直阻塞，直到该表锁被释放。可以对其他表进行增删改查。

	已锁定表	其他表
会话A（加锁的会话）	可增删改查	无法增删改查（会报错）
其他会话	无法增删改查（会一直阻塞）	可增删改查

也就是加排它锁的会话可以增删改查锁定表的数据，但加锁会话无法查询其他表。其他的会话对锁定表进行增删改查时会一直阻塞，直到锁表的锁被释放，但可以增删改查其他表的数据。

总结：写锁会阻塞其他会话的读和写。

2.2、表级锁之MyISAM 引擎表的读写操作和锁

MyISAM 存储引擎在执行查询语句（select）时，会自动给涉及的所有表都加上共享锁，在执行增删改操作前，会自动给涉及到的所有表都加上排它锁。

所以对 MyISAM 表进行操作，会有以下情况：

对 MyISAM 表进行读操作时（会自动加共享锁）：不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
对 MyISAM 表进行写操作时〈会自动加排它锁）：会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程对该表的读写操作。

简而言之，就是读锁会阻塞涉及到的表的写，但是不会堵塞读；而写锁则会把涉及到的表的读和写都堵塞。

MyISAM 的读写锁调度是写优先，这也是 MyISAM 不适合做写为主的表的引擎的原因。因为写操作会自动加写锁，加锁后其他线程就没办法做任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。

2.3、表级锁的相关SQL语句

2.3.1、查看被锁定的表

可以通过以下命令来查看哪些表被锁定：

show open tables;

结果示例如下：

结果说明如下：

Database：含有该表的数据库
Table：表名称
In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，即当前没有锁表。如果该列的值大于 0，则表示表正在被使用，有可能被锁住。
Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作

在 MySQL 的 InnoDB 引擎中，information_schema.INNODB_LOCKS表存储了当前 InnoDB 存储引擎中存在的锁信息。可以通过查询这个表来确定表是否被锁以及被何种锁锁住。

SELECT * FROM information_schema.INNODB_LOCKS

通过查询结果可以看到哪些表被锁住，并且可以看到锁的类型（如行锁、表锁）、锁定的事务 ID 等信息。

2.3.2、给表加表级锁

可以使用以下命令给表加锁：

lock table 表名 read(write), 表名2 read(write), 其他;

-- 示例
lock table lock_table_test read, lock_table_test2 write;

示例：

先创建一个表 mylock：

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

然后给该表加读锁：

lock table mylock read;

使用 show open tables; 来查看表锁情况：

可以看到，mydbtest.mylock 表已被锁定。

2.3.3、释放表锁

释放所有表的锁：

unlock tables;

2.3.4、分析表锁

我们可以通过以下命令行来分析表锁的情况：

show status like 'table_locks%';

结果示例：

可以通过检查 table_locks_waited（主要分析该值）和 table_locks_immediate 状态变量来分析系统上的表锁定，说明如下：

table_locks_waited：出现表级锁定争用而发生等待的次数(也可以说是不能立即获取锁的次数，每触发一次需等待锁的SQL时该值就加1)，此值高则说明存在着较严重的表级锁争用情况。
table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。

3、行级锁

MyISAM 引擎支持表级锁，但不支持行级锁，InnoDB 引擎支持行级锁和表级锁。在默认的可重复读隔离级别下，InnoDB 会尽量使用行级锁（而不是表级锁）和 MVCC（多版本并发控制）机制来保证事务的隔离性。

在 MySQL 的 InnoDB 存储引擎中，行级锁的使用通常与索引密切相关。当通过索引条件来查询和修改数据时，InnoDB 更倾向于使用行级锁。这是因为索引能够帮助数据库快速定位到要操作的行，从而精确地对这些行加锁。例如，在一个employees表中有employee_id索引，当执行UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 1001这样的更新操作时，InnoDB 可以通过employee_id索引快速找到employee_id = 1001对应的行，并对该行加排他行锁（X 锁），其他事务对这一行的读写操作（不符合 MVCC 机制下的可读条件）就会被阻塞。

当查询或修改数据的条件没有使用索引时，InnoDB 也有可能会升级为表锁。这是因为如果没有索引来定位行，为了保证操作的一致性和正确性，数据库可能会选择更粗粒度的锁，即表锁。例如，在employees表中，如果执行UPDATE employees SET department = 'New Dept' WHERE age > 30，假设age列没有索引，InnoDB 可能会对整个employees表加表锁。这样做的原因是，在没有索引的情况下，数据库难以确定哪些行满足条件，通过加表锁可以防止其他事务对表进行不兼容的操作，确保当前事务能够正确地完成更新操作。不过，这种行为也可能因 MySQL 的版本、配置参数以及具体的操作场景而有所不同。

示例：

下面建一张表，并且建立索引，因为只有通过索引来检索数据，InnoDB 才会使用行级表。

CREATE TABLE test_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

INSERT INTO test_lock VALUES(1,'b2');
INSERT INTO test_lock VALUES(3,'3');
INSERT INTO test_lock VALUES(4, '4000');
INSERT INTO test_lock VALUES(5,'5000');
INSERT INTO test_lock VALUES(6, '6000');
INSERT INTO test_lock VALUES(7,'7000');
INSERT INTO test_lock VALUES(8, '8000');
INSERT INTO test_lock VALUES(9,'9000');
INSERT INTO test_lock VALUES(1,'b1');

-- 建立索引列
CREATE INDEX test_lock_a_ind ON test_lock(a);
CREATE INDEX test_lock_b_ind ON test_lock(b);

建立两个会话，并且设置不自动提交事务（这样才能模拟出在一个事务内的情况，否则事务立马提交就看不出效果了），先在 A 会话中对某一行数据进行修改但不提交，然后在 B 会话中也对同一行数据进行修改，如下：

此时，会话 B 的修改会一直阻塞，直到 A 提交事务 commit，会话 B 的进程才能执行成功，否则会一直阻塞直到超时报错，这就是加了行级锁。

当然，如果会话 B 修改其他行的数据则是不会受到影响的，因为行级锁只会锁定指定行的数据。而且如果只是查询则没问题，比如A只是查询，则不会锁行，或者A修改B查询，则B的查询不会受到影响。

3.1、行级锁的分类

共享行锁（Shared Row Lock，S，共享行锁也称为 S 锁）

定义与原理：共享行锁用于对一行数据进行读取操作时的并发控制。当一个事务对某一行数据加上共享行锁后，允许其他事务也对这一行加共享行锁来读取数据。这是基于并发读取数据的需求，多个事务可以同时读取同一行数据而不会相互干扰，只要它们都只进行读取操作。

应用场景和示例：以一个在线图书阅读平台为例，多个用户可能同时查看某一本书的详细信息（如书名、作者、简介等）。假设这些信息存储在books表中的一行数据中，当用户 A 的事务对这行数据加共享行锁进行读取时，用户 B 的事务也可以对同一行数据加共享行锁来读取相同的信息。这样可以满足多个用户同时获取图书信息的需求，并且在没有写操作介入时，不会产生冲突。但是，如果有一个事务想要对这行数据进行修改（如更新图书简介），则需要获取排他行锁，此时该事务会被阻塞，直到所有的共享行锁都被释放。

排他行锁（Exclusive Row Lock，X）

定义与原理：排他行锁用于对一行数据进行写操作（如插入、更新、删除）时的并发控制。当一个事务对某一行数据加上排他行锁后，只有该事务能够对这一行进行读写操作，其他事务不能对这一行加共享行锁进行读取，也不能加排他行锁进行写入，直到持有排他行锁的事务完成并释放锁。这种锁机制确保了在对数据进行修改时的独占性，防止其他事务对正在修改的数据进行干扰，从而保证数据的准确性和完整性。

应用场景和示例：在一个银行账户管理系统中，当一个事务需要更新某个账户的余额时，它会对存储该账户余额的行数据加上排他行锁。例如，事务 A 要从账户 1 向账户 2 转账，事务 A 会对账户 1 和账户 2 余额所在的行分别加上排他行锁。在事务 A 完成转账操作（包括更新两个账户的余额并提交事务）之前，其他事务（如同时进行的另一个转账事务或者查询账户余额的事务）对这两个账户余额行的读写操作都会被阻塞。这样可以确保转账操作的原子性，即要么全部完成，要么全部不做，避免数据不一致的情况出现。

意向共享行锁（Intention Shared Row Lock，IS）

定义与原理：意向共享行锁是一种表级的锁意向表示，该锁是为了提高效率用的。当一个事务对表中的一行或多行加上共享行锁时，会在表级自动加上意向共享行锁。它的主要作用是为了提高数据库在处理更高层次（表级）锁冲突检查时的效率。通过在表级设置意向共享行锁，数据库在进行表级锁操作（如另一个事务试图对整个表加排他表锁）时，可以快速判断表内是否有行已经被加上共享行锁，而不需要逐行检查。

应用场景和示例：假设在一个电商系统的库存管理模块中，有一个事务需要查询多个商品的库存信息行。事务在对这些商品库存信息行加上共享行锁的同时，会在inventory表级加上意向共享行锁。这样，当其他事务想要对整个inventory表进行排他操作（如更新所有库存信息）时，数据库通过检查表级的意向共享行锁，就可以快速判断出表内有行正在被读取，从而避免了可能的锁冲突，提高了系统的并发性能和锁管理效率。

意向排他行锁（Intention Exclusive Row Lock，IX）

定义与原理：意向排他行锁同样是一种表级的锁意向表示，该锁是为了提高效率用的。当一个事务对表中的一行或多行加上排他行锁时，会在表级自动加上意向排他行锁。它的主要作用是在更高层次上表明表内有行正在进行排他操作，方便数据库快速进行锁冲突检查。当其他事务尝试对整个表进行不兼容操作（如加共享锁或排他锁）时，数据库可以通过检查表级的意向排他行锁及时发现并阻止，从而避免复杂的逐行检查过程。

应用场景和示例：在一个订单处理系统中，当事务 A 对订单表中的某一订单行进行更新操作（如修改订单状态）时，会对该订单行加上排他行锁，并在订单表级加上意向排他行锁。此时，如果另一个事务 B 想要对整个订单表进行读取操作（加共享锁）或者进行其他修改操作（加排他锁），数据库通过检查表级的意向排他行锁，就可以快速知道表内有行正在进行排他操作，从而避免了可能的锁冲突，确保数据的一致性和操作的正确性。

3.2、索引失效行锁变表锁

InnoDB 行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件来查询修改数据（select xxx where 索引字段），InnoDB才使用行级锁，否则，InnoDB将使用表锁！行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

比如，上面的例子当中，如果是修改 a 的值，并且条件不加引号，如条件语句为 where b = 1000，由于 b 是 varchar 类型，而此时不使用引号引起来会导致 mysql 底层自动进行类型转换，相当于发生了计算，由此索引会失效。当没有使用到索引时，此时 InnoDB 表不会使用行级锁，而是使用表锁。

如下：

上面 A 会话中由于 SQL 语句中的条件发生了类型转换，导致索引失效，行锁变表锁，所以 B 会话的修改会一直阻塞，直到 A 会话提交事务 B 的修改才能执行结束。

3.3、间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件来修改数据，InnoDB 会给符合范围条件的所有数据记录的索引项加锁，那些值在条件范围内但并不存在在表里的数据，叫做“间隙（GAP）”，InnoDB 也会对这些 “间隙” 加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。可以理解是一个范围锁。

比如，A会话对某个范围条件的数据进行修改，但 A 还未提交事务，此时 B 会话想新增一条数据，但刚好这条数据符合 A 修改语句的条件范围，则 B 的新增会一直阻塞，因此此时 InnoDB 对这些范围数据加了间隙锁。

如下：

3.4、如何分析行锁

可通过以下命令查看数据库中行锁的情况：

show status like 'innodb_row_lock%';

说明如下：

Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：从系统启动到现在锁定总时间长度。
Innodb_row_lock_time_avg：每次等待所花平均时长。
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits：系统启动后到现在总共等待的次数

当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

4、MySQL 默认事务的锁

mysql 默认事务隔离（Repeatable Read）下，增删改查操作对应的锁机制：

查询（SELECT）操作的锁机制
- 普通查询（非锁定读）：
  - 在 MySQL 默认的可重复读隔离级别下，普通查询并不会加锁（在大多数正常情况下），InnoDB 存储引擎通常会基于多版本并发控制（MVCC）机制进行普通查询。MVCC 允许事务读取到某个时间点上数据的快照。每个事务在开始时会被分配一个事务 ID，数据库中的每一行数据都有两个隐藏列，分别记录创建版本号（Create Version）和删除版本号（Delete Version）。当一个事务进行查询时，它会读取创建版本号小于等于当前事务 ID，并且删除版本号大于当前事务 ID 或者为空的数据。例如，在一个简单的员工信息管理系统中，事务 A 和事务 B 同时查询员工表employees中的数据。事务 A 开始查询时，根据 MVCC 机制获取了当时数据的快照视图。即使事务 B 在事务 A 查询期间对员工表中的数据进行了修改并提交，事务 A 仍然可以按照自己开始查询时的数据快照进行读取，而不会受到事务 B 修改操作的影响，并且这个过程中不需要对数据行加锁。
- 锁定查询（锁定读）：
  - 共享锁（S 锁）的使用：如果在查询语句中使用LOCK IN SHARE MODE子句，事务会对查询的行加上共享行锁。这意味着其他事务可以对这些行加共享行锁进行读取，但不能加排他行锁进行写操作，直到共享行锁被释放。例如，SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE会对满足条件的行加共享行锁。这种锁定读主要用于需要在读取数据期间防止数据被其他事务修改的场景，比如在统计报表生成过程中，需要确保读取的数据在统计期间不被修改。
  - 排他锁（X 锁）的使用：当使用FOR UPDATE子句进行查询时，事务会对查询的行加上排他行锁。这样只有当前事务可以对这些行进行读写操作，其他事务对这些行的读写操作都会被阻塞，直到排他行锁被释放。例如，SELECT * FROM table_name WHERE condition FOR UPDATE用于在事务需要对查询到的数据进行后续更新操作的场景，如在一个订单处理系统中，先查询出未处理的订单，然后对这些订单进行更新操作，就可以使用FOR UPDATE来确保在更新之前这些订单不会被其他事务修改。
插入（INSERT）操作的锁机制
- 排他行锁（X 锁）：
  - 在 InnoDB 中，当插入一条新记录时，会自动为新插入的行加上排他行锁。这是为了确保在插入操作期间，其他事务不会同时对同一行进行插入、更新或删除操作，保证数据的完整性。例如，在一个用户注册系统中，当插入新用户信息时，新用户记录所在的行就会被加上排他行锁。这种锁在插入操作完成（事务提交或者回滚）后会自动释放。

删除（DELETE）操作的锁机制
- 排他行锁（X 锁）和间隙锁（Gap Lock）：
  - 首先会对要删除的行加上排他行锁。这可以防止其他事务在删除操作进行期间对同一行进行读取（违反隔离级别要求的读取）、更新或删除操作。例如，在一个内容管理系统中，当删除一篇文章记录时，对应的文章记录行就会被加上排他行锁，其他事务想要读取这篇文章（如果不符合 MVCC 机制下的可读条件）或者修改、删除这篇文章都会被阻塞，直到删除操作完成并且锁被释放。
  - 与插入操作类似，如果删除操作涉及索引间隙，也会使用间隙锁。这是为了防止在删除操作过程中，其他事务在间隙中插入数据，从而导致幻读问题。例如，在一个按照时间顺序存储记录的索引中，删除某个时间区间内的记录时，InnoDB 会对该时间区间对应的索引间隙加上间隙锁，确保在删除操作期间，不会有新的记录插入到这个区间，保证事务在执行过程中的数据一致性。
更新（UPDATE）操作的锁机制
- 排他行锁（X 锁）和 MVCC 机制：
  - 对要更新的行加上排他行锁。这保证了在更新过程中，只有执行更新操作的事务能够对该行进行读写操作，其他事务对同一行的读取（不符合 MVCC 机制下的可读条件）、更新或删除操作都会被阻塞。例如，在一个电商系统中，当更新商品价格时，对应的商品记录行就会被加上排他行锁，防止其他事务同时修改价格或者读取不符合隔离级别要求的价格数据。
  - 在更新操作时，InnoDB 会利用 MVCC（多版本并发控制）机制。如果事务在更新过程中需要读取该行数据的旧版本（例如在一个触发器或者复杂的业务逻辑中），它可以通过 MVCC 机制读取到该行数据在事务开始时的快照版本。这是因为在可重复读隔离级别下，事务在整个执行过程中应该看到的数据是一致的，MVCC 机制可以保证这一点，同时减少了对共享锁的依赖，提高了并发性能。
  - 间隙锁（Gap Lock）：同样，如果更新操作涉及索引间隙，也会使用间隙锁来防止幻读。例如，在一个按照范围查询频繁的表中，更新某个范围内的记录时，InnoDB 会对对应的索引间隙加上间隙锁，确保在更新操作期间，不会有新的记录插入到这个范围内，保证事务的隔离性和数据一致性。

4.1、SELECT ... FROM

1）SELECT ... FROM ...

该语句是一个快照读，通过读取数据库的一个快照，不会加任何锁，除非将隔离级别设置成了 SERIALIZABLE 。

2）SELECT ... FROM ... LOCK IN SHARE MODE

如果使用到索引并且条件是范围，则在所有索引扫描范围的索引记录上加上共享的间隙锁（next key锁）。如果是唯一索引，只需要在相应记录上加排他行锁。这些被共享锁锁住的行无法进行update/delete。
允许其它事务对这些记录再加SHARE锁

3）SELECT ... FROM ... FOR UPDATE

如果使用到索引并且条件是范围，则在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加排他行锁

4.2、UPDATE ... WHERE ...

对要更新的行加上排他行锁，的next key锁。
如果涉及范围更新，则会对索引间隙加上间隙锁。

4.3、DELETE FROM ... WHERE ...

语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加排他行锁

4.4、INSERT

在插入的记录上加一把排他行锁。

总结：

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

可参考：https://www.cnblogs.com/frankyou/p/9603784.html

5、数据库的事务隔离和锁的关系

事务隔离级别是为了解决多并发时查到的数据不一致的问题，而锁是为了阻塞其他事务对同一数据进行修改。一个是为查，一个是为改。

6、多并发事务产生死锁

6.1、分析和示例

死锁产生的原因
- 资源竞争：在事务中多条更新语句并发执行时，死锁主要是由于多个事务对资源（数据库中的行或表）的相互竞争和等待引起的。每个事务都持有一些资源的锁，同时又在等待其他事务释放它们所需的资源锁。例如，事务 A 获取了资源 X 的排他行锁，正在等待资源 Y 的排他行锁；而事务 B 获取了资源 Y 的排他行锁，正在等待资源 X 的排他行锁，这样就形成了死锁。
- 更新顺序不一致：如果多个事务对相同的资源进行更新，且更新的顺序不一致，很容易导致死锁。假设在一个银行转账系统中，事务 A 先更新账户 1 的余额，再更新账户 2 的余额；而事务 B 先更新账户 2 的余额，再更新账户 1 的余额。当两个事务并发执行时，就可能出现死锁情况。
示例场景
- 假设有一个包含accounts表的数据库，表中有account_id（主键）、balance两个列。有两个事务T1和 T2 同时进行转账操作。
- 事务 T1 的 SQL 语句如下：
  - START TRANSACTION;
  - UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
  - UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
  - COMMIT;
- 事务 T2 的 SQL 语句如下：
  - START TRANSACTION;
  - UPDATE accounts SET balance = balance - 100 WHERE account_id = 2;
  - UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
  - COMMIT;
- 在这个场景中，当 T1 执行第一条更新语句锁住account_id = 1对应的行后，T2 执行第一条更新语句锁住account_id = 2对应的行。然后 T1 等待account_id = 2的行锁释放来执行第二条更新语句，T2 等待account_id = 1的行锁释放来执行第二条更新语句，这样就产生了死锁。

6.2、如何预防死锁

固定更新顺序
- 原理：确保所有事务按照相同的顺序访问和更新资源。这样可以避免循环等待的情况，因为每个事务都在等待另一个事务持有的锁，而这个等待关系是由于访问顺序不一致导致的。
- 例如，银行转账中，可以规定所有转账事务都按照账户 ID 从小到大的顺序来更新账户余额，这样可以避免因更新顺序不一致而导致的死锁。
减少事务持有锁的时间
- 原理：缩短事务的执行时间可以降低在同一时间内多个事务竞争资源的可能性。通过优化查询语句、减少复杂的业务逻辑在事务中的占比等方式，可以使事务更快地完成，从而减少死锁的发生。
- 示例和方法：
  - 优化查询性能：在事务中的更新语句里，确保所涉及的查询能够高效执行。例如，为更新操作涉及的列添加合适的索引，这样可以加快数据的定位和更新速度。如果更新customers表中根据customer_id查找的客户信息，为customer_id列添加索引可以加快UPDATE语句的执行。
  - 合理拆分事务：如果一个事务包含了多个可以独立执行的更新操作，并且这些操作对数据一致性的要求允许它们分开执行，那么可以将事务拆分成多个较小的事务。例如，一个订单处理事务包含更新订单状态、更新库存和更新客户积分三个操作。如果业务允许，可将其拆分为三个独立的事务，分别处理订单状态更新、库存更新和客户积分更新，这样每个事务持有锁的时间就会缩短。
使用合适的锁粒度和锁类型
- 原理：选择合适的锁粒度（行锁、表锁等）和锁类型（共享锁、排他锁等）可以平衡并发性能和数据一致性。细粒度的行锁通常可以提高并发度，但可能会增加锁管理的复杂性；粗粒度的表锁可以简化锁管理，但会降低并发性能。根据业务需求合理选择可以减少死锁。
- 示例：在一个以读取为主的报表系统中，大部分操作是查询数据，偶尔会有少量的更新操作用于数据修正。对于这种情况，在更新操作时可以使用排他行锁，这样在更新数据行时不会影响其他事务对其他行的读取操作，同时也能减少因为对整个表加锁而导致的死锁风险。如果不分情况地对整个表加排他表锁，在高并发读取场景下很容易导致死锁。

posted @ 2022-02-28 23:48 wenxuehai 阅读(606) 评论(0) 编辑收藏举报

刷新页面返回顶部

提升自己，才有选择的能力和勇气