一、锁机制简介

1.定义

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

加锁本身也需要消耗资源，锁策略就是在锁的开销和安全性之间寻求平衡。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

2.锁的分类

对于myisam的表select 是会锁定表的，会导致其他操作挂起，处于等待状态。对于innodb的表select 是不会锁表的(基于快照)。

(1)表锁和行锁

从对数据操作的粒度分，分为表锁和行锁

①表锁（偏读）——DDL操作触发

会锁定整张表，在对表进行写操作之前，需要先获得写锁，获得写锁后将会阻塞其他用户对该表的读写操作。只有没有写锁时，其他用户才能获取读锁，读锁之间是不相互阻塞的。写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面。

虽然不同的存储引擎都有自己的锁实现，MySQL自身仍然会在服务器层使用表锁并忽略存储引擎的锁机制，例如当执行ALTER TABLE时，服务器会使用表锁。表锁偏向MyISAM存储引擎，开销小，加锁快，无思索，锁定粒度大，发生锁冲突的概率最高，并发度最低。

表级锁中的MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

给一个小表加个字段也有可能导致整个库挂了。假设表 t 是一个小表。 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C需要 MDL 写锁，因此只能被阻塞。如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。所有对表的增删改查操作都需要先申请MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session 再请求的话，这个库的线程很快就会爆满。事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

如何安全地给小表加字段？

首先要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

②行锁（偏写）

行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MYISAM的最大不同有
两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。

但是Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

(2)自增锁

是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

与此同时，InnoDB提供了innodb_autoinc_lock_mode配置，可以调节与改变该锁的模式与行为。

在InnoDB中按主键顺序插入可能会导致明显的争用，当前主键的上界会成为“热点”，导致锁竞争。

解决自增锁引起的插入性能问题

自己写一个分布式自增id的发号器，去掉AUTO_INCREMENT 去掉；
避免 insert … select … ，这样会导致Bulk inserts，产生表锁；
如果binlog-format是row模式，而且不关心一条bulk-insert的auto值连续（一般不用关心），那么设置innodb_autoinc_lock_mode = 2 可以提高更好的写入性能

(3)共享/排它锁——读读并发

从对数据库操作的类型分，分为读锁和写锁

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

select * from table where id<6 lock in share mode;--共享锁 select * from table where id<6 for update;--排他锁

这两种方式主要的不同在于LOCK IN SHARE MODE多个事务同时更新同一个表单时很容易造成死锁。

申请排他锁的前提是，没有线程对该结果集的任何行数据使用排它锁或者共享锁，否则申请会受到阻塞。在进行事务操作时，MySQL会对查询结果集的每行数据添加排它锁，其他线程对这些数据的更改或删除操作会被阻塞（只能读操作），直到该语句的事务被commit语句或rollback语句结束为止。

SELECT... FOR UPDATE 使用注意事项：

for update 仅适用于innodb，且必须在事务范围内才能生效。
根据主键进行查询，查询条件为like或者不等于，主键字段产生表锁。
根据非索引字段进行查询，会产生表锁。

(4)意向锁

InnoDB支持多粒度锁，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

①意向锁种类

意向锁，是一个表级别的锁(table-level locking)；意向锁分为：

意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

举个例子：

select ... lock in share mode，要设置IS锁；

select ... for update，要设置IX锁；

②意向锁协议

事务要获得某些行的S锁，必须先获得表的IS锁
事务要获得某些行的X锁，必须先获得表的IX锁

③兼容互斥表

IS IX

IS 兼容兼容

IX 兼容兼容

S X

IS 兼容互斥

IX 互斥互斥

(5)插入意向锁——插入并发

对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，无须进行强互斥性。插入意向锁，是间隙锁(一种实施在索引上，锁定索引某个区间范围的锁)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

在MySQL，InnoDB，RR下：

t(id unique PK, name);

数据表中有数据：

10, wangwu

20, zhangsan

30, lisi

事务A先执行，在10与20两条记录中插入了一行，还未提交：

insert into t values(11, xxx);

事务B后执行，也在10与20两条记录中插入了一行：

insert into t values(12, ooo);

虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：

使用的是插入意向锁
并不会阻塞事务B

(6)记录锁

记录锁，它封锁索引记录

select * from t where id=1 for update;

它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。如果没有for update则是快照读(SnapShot Read)，它并不加锁

(7)间隙锁

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

t(id PK, name KEY, sex, flag);

表中有四条记录：

1, wangwu, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

这个SQL语句

select * from t      where id between 8 and 15      for update;

会封锁区间，以阻止其他事务id=10的记录插入。如果能够插入成功，头一个事务执行相同的SQL语句，会发现结果集多出了一条记录，即幻影数据。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

(8)临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。

如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

t(id PK, name KEY, sex, flag);

表中有四条记录：

1, wangwu, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

PK上潜在的临键锁为：

(-infinity, 1]

(1, 3]

(3, 5]

(5, 9]

(9, +infinity]

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

3.死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤

所以，正常情况下采用第二种策略，即：主动死锁检测，而且innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

4.MVCC——读写并发

在数据库中，并发控制是指在多个用户/进程/线程同时对数据库进行操作时，如何保证事务的一致性和隔离性的，避免了加锁操作从而最大程度地并发。MVCC只在REPEATABLE和READ COMMITED两个隔离级别下工作，其他两个隔离级别和MVCC不兼容。

当多个用户/进程/线程同时对数据库进行操作时，会出现3种冲突情形：

读-读，不存在任何问题
读-写，有隔离性问题，可能遇到脏读（会读到未提交的数据），幻读等。
写-写，可能丢失更新

(1)多版本并发控制（MVCC）

是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，写任务操作新克隆的数据，直至提交。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的，这两个列一个保存行的创建版本号，一个保存行的过期版本号，每开始一个新的事务，系统版本号就会自动递增。事务开始时刻的版本号会作为事务的版本号用来和查询到的每行记录的版本号进行比较。

MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决时不彻底的。

(2)乐观并发控制（OCC）

是一种用来解决写-写冲突的无锁并发控制，认为事务间争用没有那么多，所以先进行修改，在提交事务前，检查一下事务开始后，有没有新提交改变，如果没有就提交，如果有就放弃并重试。乐观并发控制类似自选锁。乐观并发控制适用于低数据争用，写冲突比较少的环境。

多版本并发控制可以结合基于锁的并发控制来解决写-写冲突，即MVCC+2PL，也可以结合乐观并发控制来解决写-写冲突。

(3)快照读和当前读

表记录有两种读取方式。

快照读：读取的是快照版本。普通的SELECT就是快照读。通过mvcc来进行并发控制的，不用加锁。
当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

快照读情况下，InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。

在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。Serializable隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。

5.一条SELECT语句是如何加锁的

加锁是解决并发事务执行过程中引起的脏写、脏读、不可重复读、幻读这些问题的一种解决方案

普通的SELECT语句在：

READ UNCOMMITTED隔离级别下，不加锁，直接读取记录的最新版本，可能发生脏读、不可重复读和幻读问题。
READ COMMITTED隔离级别下，不加锁，在每次执行普通的SELECT语句时都会生成一个ReadView快照读，这样解决了脏读问题，但没有解决不可重复读和幻读问题。
REPEATABLE READ隔离级别下，不加锁，只在第一次执行普通的SELECT语句时生成一个ReadView，这样把脏读、不可重复读和幻读问题都解决了。

mysql默认隔离级别是REPEATABLE READ

SERIALIZABLE隔离级别下，需要分为两种情况讨论：

- 在系统变量autocommit=0时，也就是禁用自动提交时，普通的SELECT语句会被转为SELECT ... LOCK IN SHARE MODE这样的语句，也就是在读取记录前需要先获得记录的S锁，具体的加锁情况和REPEATABLE READ隔离级别下一样，我们后边再分析。
- 在系统变量autocommit=1时，也就是启用自动提交时，普通的SELECT语句并不加锁，只是利用MVCC来生成一个ReadView去读取记录。

为啥不加锁呢？因为启用自动提交意味着一个事务中只包含一条语句，一条语句也就没有啥不可重复读、幻读这样的问题了。

二、锁进阶

1.锁定读的语句

语句一：SELECT ... LOCK IN SHARE MODE;
语句二：SELECT ... FOR UPDATE;
语句三：UPDATE ...
语句四：DELETE ...

语句一和语句二是MySQL中规定的两种锁定读的语法格式，而语句三和语句四由于在执行过程需要首先定位到被改动的记录并给记录加锁，也可以被认为是一种锁定读。

READ UNCOMMITTED/READ COMMITTED隔离级别下值得注意的是，采用加锁方式解决并发事务带来的问题时，其实脏读和不可重复读在任何一个隔离级别下都不会发生（因为读-写操作需要排队进行）。

2.锁与隔离级别

(1)锁是如何实现隔离级别的

未提交读（Read Uncommitted）：在事务 A 读取数据时，事务 B 读取和修改数据加了共享锁。这种隔离级别，会导致脏读、不可重复读以及幻读。

已提交读（Read Committed）：在事务 A 读取数据时增加了共享锁，一旦读取，立即释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务 A 在读取数据时，事务 B 只能读取数据，不能修改。当事务 A 读取到数据后，事务 B 才能修改。这种隔离级别，可以避免脏读，但依然存在不可重复读以及幻读的问题。

可重复读（Repeatable Read）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务 A 在没有结束事务时，事务 B 只能读取数据，不能修改。当事务 A 结束事务，事务 B 才能修改。这种隔离级别，可以避免脏读、不可重复读，但依然存在幻读的问题。

可序列化（Serializable）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了表级排他锁，直到事务结束才释放锁。可序列化解决了脏读、不可重复读、幻读等问题，但隔离级别越来越高的同时，并发性会越来越低。

(2)锁具体实现算法

InnoDB 既实现了行锁，也实现了表锁。行锁是通过索引实现的，如果不通过索引条件检索数据，那么 InnoDB 将对表中所有的记录进行加锁，其实就是升级为表锁了。

行锁的具体实现算法有三种：record lock、gap lock 以及 next-key lock。record lock 是专门对索引项加锁；gap lock 是对索引项之间的间隙加锁(避免索引范围区间插入记录)；next-key lock 则是前面两种的组合，对索引项以其之间的间隙加锁。

只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock，在 Select 、Update 和 Delete 时，除了基于唯一索引的查询之外，其他索引查询时都会获取 gap lock 或 next-key lock，即锁住其扫描的范围。

(3)优化高并发事务

①结合业务场景，使用低级别事务隔离

在高并发业务中，为了保证业务数据的一致性，操作数据库时往往会使用到不同级别的事务隔离。隔离级别越高，并发性能就越低。

在修改用户最后登录时间的业务场景中，这里对查询用户的登录时间没有特别严格的准确性要求，而修改用户登录信息只有用户自己登录时才会修改，不存在一个事务提交的信息被覆盖的可能。所以我们允许该业务使用最低隔离级别。

而如果是账户中的余额或积分的消费，就存在多个客户端同时消费一个账户的情况，此时我们应该选择 RR 级别来保证一旦有一个客户端在对账户进行消费，其他客户端就不可能对该账户同时进行消费了。

②避免行锁升级表锁

③控制事务的大小，减少锁定的资源量和锁定时间长度

你是否遇到过以下 SQL 异常呢？在抢购系统的日志中，在活动区间，我们经常可以看到这种异常日志：

MySQLQueryInterruptedException: Query execution was interrupted

由于在抢购提交订单中开启了事务，在高并发时对一条记录进行更新的情况下，由于更新记录所在的事务还可能存在其他操作，导致一个事务比较长，当有大量请求进入时，就可能导致一些请求同时进入到事务中。

又因为锁的竞争是不公平的，当多个事务同时对一条记录进行更新时，极端情况下，一个更新操作进去排队系统后，可能会一直拿不到锁，最后因超时被系统打断踢出。

在用户购买商品时，首先我们需要查询库存余额，再新建一个订单，并扣除相应的库存。这一系列操作是处于同一个事务的。

以上业务若是在两种不同的执行顺序下，其结果都是一样的，但在事务性能方面却不一样：

这是因为，虽然这些操作在同一个事务，但锁的申请在不同时间，只有当其他操作都执行完，才会释放所有锁。因为扣除库存是更新操作，属于行锁，这将会影响到其他操作该数据的事务，所以我们应该尽量避免长时间地持有该锁，尽快释放该锁。

又因为先新建订单和先扣除库存都不会影响业务，所以我们可以将扣除库存操作放到最后，也就是使用执行顺序 1，以此尽量减小锁的持有时间。

SQL中的锁机制是确保数据库并发操作下数据一致性与完整性的关键工具。合理使用和优化锁能显著提升高并发场景的性能并避免死锁。以下是核心概念与优化策略：

三、锁的总结

一、锁的核心类型

共享锁（S-Lock）

- 语法：SELECT ... LOCK IN SHARE MODE (MySQL), SELECT ... WITH (SHARELOCK) (SQL Server)
- 作用：允许多事务并发读取，阻塞其他事务的写操作。
- 场景：读取数据且不希望被修改时使用。

排他锁（X-Lock）

- 语法：自动加在UPDATE/DELETE/INSERT语句上，或显式SELECT ... FOR UPDATE
- 作用：禁止其他事务读写该数据，直到当前事务结束。
- 场景：修改数据前确保数据不被其他事务更改。

意向锁（Intention Locks）

- 表级锁（如IS、IX），用于快速判断表中是否有行级锁，避免逐行检查。

间隙锁（Gap Locks）

- 锁定索引记录间的"间隙"，防止幻读（Phantom Read）。
- 触发条件：在REPEATABLE READ及以上隔离级别中使用范围查询。

二、事务隔离级别对锁的影响

隔离级别	锁行为特点	典型问题
READ UNCOMMITTED	不加锁（脏读）	数据不一致
READ COMMITTED	语句结束释放锁	不可重复读
REPEATABLE READ	事务结束释放锁 + 间隙锁	可能死锁
SERIALIZABLE	最高锁粒度，类似表锁	并发性能差

注：MySQL默认隔离级别为REPEATABLE READ，Oracle/SQL Server默认为READ COMMITTED。

三、锁优化策略

1. 缩短事务执行时间

问题：长事务占用锁资源，增加阻塞和死锁概率。

优化：

-- 反例：事务中包含耗时操作（如外部API调用）
START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE id = 100;
CALL ExternalApi(); -- 外部调用（耗时）
COMMIT;-- 正例：拆分事务，尽早提交
UPDATE orders SET status = 'processing' WHERE id = 100; -- 快速完成
COMMIT;
-- 再执行外部调用

2. 精确锁定范围

问题：UPDATE ... WHERE condition 条件不精确导致锁范围过大。

优化：

-- 使用索引精准锁定（避免全表扫描升级为表锁）
CREATE INDEX idx_user_active ON users(active);
UPDATE users SET score = score + 10 WHERE active = 1; -- 利用索引锁定行

3. 避免死锁的编码实践

原则：按固定顺序访问多张表（如始终先A后B）。

示例：

-- 事务1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;-- 事务2（按相同顺序执行）
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1; -- 等待事务1释放id=1的锁
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

4. 合理使用锁超时

设置锁等待超时，避免无限阻塞：

-- MySQL
SET innodb_lock_wait_timeout = 5; -- 超时5秒
-- SQL Server
SET LOCK_TIMEOUT 5000; -- 超时5秒（毫秒）

5. 索引优化减少锁冲突

无索引的UPDATE/DELETE会锁表！

案例：

-- 无索引字段导致全表锁
DELETE FROM logs WHERE create_time < '2023-01-01'; -- 全表扫描+表锁-- 添加索引后
CREATE INDEX idx_logs_time ON logs(create_time);
DELETE FROM logs WHERE create_time < '2023-01-01'; -- 行锁/间隙锁

6. 选择合适的事务隔离级别

高并发读场景：使用READ COMMITTED（减少间隙锁）
需要避免幻读：REPEATABLE READ + 精确索引

四、诊断锁争用

MySQL:

SHOW ENGINE INNODB STATUS; -- 查看LATEST DETECTED DEADLOCK
SELECT * FROM information_schema.INNODB_LOCKS; -- 当前锁信息

SQL Server:

SELECT * FROM sys.dm_tran_locks; -- 活动锁信息
EXEC sp_lock; -- 快速查看锁

五、高级技巧

乐观锁（无锁机制）
使用版本号/时间戳控制并发修改：

UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 100 AND version = 5; -- 若版本号变化则更新失败

分区表（Partitioning）
将数据分散到不同物理分区，减少锁竞争范围。

关键总结

场景	优化建议
长事务阻塞	拆分事务，尽早提交
全表锁	为WHERE条件字段添加索引
高频死锁	固定资源访问顺序
高并发写入冲突	降级隔离级别 + 乐观锁
间隙锁导致性能下降	改用`READ COMMITTED` （需业务允许）