MySQL不同操作分别用什么锁？

时间：10-11来源：作者：点击数：

总结一下，在MySQL的InnoDB存储引擎中，可以使用以下几种锁来保护并发操作：

共享锁允许多个事务同时读取同一行数据，但不允许对其进行修改。如果一个事务获取了一行数据的共享锁，其他事务也可以同时获取同一行数据的共享锁，但是任何尝试获取该行数据排他锁（即进行修改操作）的事务都将被阻塞，直到共享锁被释放。

总之，共享锁不互斥，多个事务可以同时获取同一行数据的共享。我们简记为：读读并行。

# 加锁语句
SELECT ... FOR SHARE

排他锁也称为写锁，它允许一个事务独占地对一行数据进行修改，其他事务无法同时获取该行的共享锁或排他锁。使用排他锁的事务可以确保在修改数据期间，没有其他事务能够读取或修改该行数据。

总之，排他锁互斥，同一行数据只能被一个事务获取排他锁，与其他任何锁互斥。我们简记为：写读、写写阻塞。

# 加锁语句
SELECT ... FOR UPDATE

意向锁是一种低级别的锁，用于表示事务意图对某个数据范围进行锁定。它可以是共享锁或排他锁的意图表示。意向锁的作用是避免其他事务在范围级别上进行修改操作，而具体的行级锁定则由应用程序根据需求自行决定。

加锁语句：在执行UPDATE或DELETE语句之前，可以使用如下语句获取意向锁。

SELECT ... FOR UPDATE
# 或
SELECT ... FOR SHARE

悲观锁假设最坏的情况，即在执行每个数据修改操作前都会先获取排他锁。这种锁的策略下，事务在修改数据时会先锁定该行数据，确保其他事务无法同时修改该行数据。乐观锁则假设最好的情况，即数据不会被其他事务同时修改，因此只在提交数据修改时才检查是否有冲突。

乐观锁在执行数据修改操作时不会先锁定数据，而是在提交修改时检查是否有冲突。如果检测到冲突，则事务会回滚并重新尝试。乐观锁适用于读多写少的应用场景，可以提高并发性能。

加锁语句：乐观锁没有特定的加锁语句，而是通过版本号机制来实现。在执行UPDATE或DELETE操作时，InnoDB会检查数据的版本号是否与最初读取到的版本号一致，如果不一致则表示有其他事务修改了该数据，此时会回滚事务并抛出异常。

需要注意的是，InnoDB还支持自动提交（AUTOCOMMIT）和显式提交（COMMIT）来控制事务的提交和回滚。在使用InnoDB时，可以通过调整隔离级别和并发控制参数来优化并发性能和数据一致性。

这个模式在读未提交(Read Uncommitted/RU) 和读已提交(Read Committed/RC)、可重复读（Repeated Read/RR）隔离级别下都是有效的。

另外一种隔离级别：串行化（Serializable），也是我们之前说过的，在InnoDB中，串行化是最高隔离级别，普通select会升级为select ... in share mode。

加锁的select，会使用排他锁（X锁）来保护数据，参照我们上面的介绍，主要有如下几种情况：

# 排他场景
selet ... for update

# 共享场景
select ... for share mode

这时候有如下情况：

在主键（primary key）和唯一索引(unique index)上使用唯一的查询条件(unique search condition)，会使用记录锁(record lock)，即行锁
如果条件为记录区间，则会封锁记录之间的间隔，即使用间隙锁(gap lock)与临键锁(next-key lock)

以例为证：

# 表结构
table (Id PK, Name , Company);

# 表中包含四条记录
5, Gates, Microsoft
7, Bezos, Amazon
11, Jobs, Apple
14, Elison, Oracle

记录锁，它封锁索引记录，例如：

select * from table where id=5 for update;

它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

区间范围的查询条件和索引条件，InnoDB会封锁被扫描的索引范围，并使用间隙锁与临键锁，避免索引范围区间插入记录

以例为证：

select * from table
where id between 7 and 13 
for update;

这样的话，会封锁数据的区间，以防止其他事务插入\修改\删除 id=8的记录。

和select加锁同理，唯一索引(unique index)上使用唯一的查询条件(unique search condition)，会使用记录锁(record lock)，即行锁

举例：

# 只会锁住id=1的这一行
update table set name='Brand' where id=5;

区间范围情况，符合查询条件的索引记录范围，都会加排他临键锁(exclusive next-key lock)，来封锁索引记录区间，来避免其他事务插入，更新，删除该区间内的索引记录。
如果update的是聚集索引(clustered index)记录，则对应的普通索引(secondary index)记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的。

Innodb中最好是采用主键索引查询，这样只需要一次索引，如果使用辅助索引检索，涉及多一步的回表操作，比主键查询要耗时一些。

所以，InnoDB的普通索引，实际上会扫描两遍：

第1遍，由普通索引找到PK：检索到name='Ellison'的数据，获取id为14

第2遍，由PK找到行记录：即到主键索引中检索id为14的记录