在实际运行的数据库系统中，锁定机制不断在工作，例如当一个用户正在修改某些数据时，MySQL会通过锁定数据来避免其他用户同时读取这些信息此锁定系统包含共享锁（读锁）和排他锁（写锁）。

读锁允许多个客户端同时读取同一资源，不会相互阻塞。而写锁是排他性的，以确保任何时刻只有一个用户可以执行写入操作，并防止其他用户读取正在写入的资源。

锁从不同角度又可以划分为不同的类型，

• 按锁的粒度可分为：全局锁、行锁
• 按锁的属性可分为：共享锁、排它锁
• 按加锁机制可分为：乐观锁、悲观锁

乐观锁&悲观锁

乐观锁和悲观锁是两种常用的并发控制机制。它们的命名来源于它们对数据并发修改时的"态度"。

悲观锁

它总是假设最坏的情况，即认为数据在被多次读取的期间内总会被其他线程修改，所以在数据读取的整个过程中都会进行加锁(读锁或写锁)操作，以确保数据的并发一致性。

适用场景：写多读少

对于读少写多的场景，数据竞争（conflict）的概率会比较高，因为有更多的操作试图修改数据。悲观锁在每次尝试修改数据时都会预先加锁，这种策略下，只有一个事务（transaction）可以执行写入操作，其他尝试写入的操作会被阻塞直到已经获取锁的事务完成并释放锁。

如果不使用悲观锁，那么在写操作很频繁的情况下，可能会出现很多冲突，导致需要反复重试，从而降低性能。悲观锁通过提前预防冲突，可以在写多的场景下保持较好的性能。

# 加读锁
select * from user where id=1 lock in share mode;
# 加写锁
select * from user where id=1 for update;

乐观锁

总是假设别人不会修改当前数据，所以每次读取数据的时候都不会加锁，只是在更新数据的时候通过version判断别人是否修改过数据。

适用场景：读多写少

在读操作比写操作多的应用场景中，因为大部分操作都是读取数据而不会修改数据，所以冲突的几率更小，乐观锁可以有效地提高系统的并发性能。另外，由于乐观锁在整个数据处理过程中并不持有实际的锁，所以不会阻塞其他的读操作，这对于读多写少的场景来说是非常有利的。然而，如果在一个写操作比读操作多的系统中使用乐观锁，由于频繁的写操作会增加冲突的概率，从而导致很多操作需要进行回滚和重试，这将降低系统的整体性能。

MySQL本身并未直接支持乐观锁，但可以通过在应用程序中引入版本号version或时间戳字段等方式来实现。

一个简单的乐观锁例子如下：

1. 在表中添加一个版本号字段version。
2. 当读取数据时，获取该数据行的version值。
3. 在更新时，带上version作为条件，同时将version值加1。一个典型的SQL语句可能是这样的：UPDATE table SET column = value, version = version + 1 WHERE id = ? AND version = ?
4. 检查此次更新是否成功（即影响的行数是否大于0）。如果更新失败，那么说明在你的事务操作期间，可能有其他事务修改了该行数据。

在使用乐观锁时，当发生版本冲突（即有其他事务修改了数据）导致更新失败，常见的解决策略有以下几种：

1. 重试：重新读取数据并尝试再次更新。这个策略在冲突概率较低的情况下比较有效，但如果经常发生冲突，频繁的重试可能会影响性能。
2. 显示错误：向用户显示一个错误消息，告知他们操作失败。这种方法通常用于用户交互的环境，例如网页或应用程序，其中用户可以选择不同的操作以解决冲突。
3. 合并更改：如果可能，可以尝试合并此次更新与同时进行的其他更新，从而解决冲突。这可能需要复杂的业务逻辑来支持。
4. 放弃操作：简单地接受失败，并停止尝试更新。这可能是在无法或无需解决冲突的情况下最简单的策略。

共享锁、排它锁

共享锁

共享锁，又称之为读锁、S 锁， 就是说一条数据被加了S 锁之后，其他事务也能来读数据，可以共享一把锁。语语法：

select * from test where id=1 LOCK IN SHARE MODE;

特点：一旦上锁，任何事务（包括当前事务）无法对其修改，其他事务可以并发读取数据，也可在对此数据再加共享锁

排他锁

排他锁,又称之为写锁、X 锁, 是说一条数据被加了 X 锁之后，其他事务想来访问这条数据只能阻塞等待锁的释放， 具有排他性。

当我们在修改数据，如：insert,update,delete 的时候 MySQL 就会自动加上排他锁，同样的， 我们可以通过如下 sql 语句手动加上排他锁：select * from test where id=1 for update;

特点：如果事务对数据A加上排他锁后，则其他事务不可并发读取数据，也不能再对A加任何类型的锁。获准排他锁的事务既能读数据，又能修改数据。

全局锁&行级锁

全局锁：

• 锁整Database，由MySQL的SQL layer层实现表级锁：
• 锁某Table，由MySQL的SQL layer层实现。

行级锁：锁某Row的索引，也可锁定行索引之间的间隙，由存储引擎实现。记录锁、间隙锁、临键锁均属于行锁。

全局锁

锁整Database

应用场景：全局逻辑备份(mysqldump)

sql语句：Flush tables with read lock，这条命令可以对整个数据库加全局读锁，使得库处于只读状态。

使用这个命令进行备份存在风险：

• 若在主库备份，则备份期间无法执行更新操作，业务会基本停滞；
• 若在从库备份，备份期间不能同步主库的binlog，会导致主从延迟。

解决方案是使用mysqldump的--single-transaction参数启动一个事务来获取一致性视图，由于MVCC（多版本并发控制）的支持，这个过程中数据可正常更新。

锁某Table

MySQL中的表级锁主要有两种类型：表锁（Table Lock）和元数据锁（Metadata Lock，MDL）

表锁

• 加锁：LOCK TABLES table_name READ/WRITE;
• 解锁：UNLOCK TABLES; (客户端断开的时候也会自动释放)

lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象

如果在某个线程A中执行lock tables t1 read,t2 wirte;这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许。

MDL

从MySQL 5.5版本开始引入，它主要用于保护表结构（table structure），使得当一个线程正在对一个表进行访问时，其他线程不能执行可能改变表结构的操作，如DROP TABLE、ALTER TABLE(包括增删索引)等命令。MDL主要在内部使用，用户无法直接控制其行为。它自动在访问一个表的时候获得，并在不再需要该表的时候释放。

当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。事务中的MDL锁，在语句执行开始时申请，会等到整个事务提交后再释放

• 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性

session A	session B	session C	session D
begin;
select * from t limit 1;
---	select * from t limit 1;
		alter table t add f int; (Blocked)
			select * from t limit 1; (Blocked)

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

因为MDL锁的存在，所以对热点表进行ALTER TABLE操作可能会导致严重的性能问题，因为这个操作会锁定数据库表，阻止其他查询在此期间执行。以下是一些策略来解决或规避这个问题：

1. 在线DDL: 在MySQL 5.6及以上版本，你可以使用在线DDL特性。这允许大多数ALTER TABLE语句在表上执行时允许并发DML。
2. 工具使用: 有一些第三方工具如Percona Toolkit中的pt-online-schema-change和GitHub的gh-ost，它们都可以通过创新的方式实现在线修改表结构，极大地减少了数据库的压力。
3. 读写分离: 在从库上进行ALTER TABLE，避免直接影响主库。完成后通过主从切换，将从库提升为新的主库。
4. 维护窗口: 在系统低峰时间执行ALTER TABLE操作，尽量减少对业务的影响。

注意，在采取任何行动之前，都应该在测试环境中验证其效果，并确保有有效的数据备份，以防出现意外情况。

行锁