全局锁 表锁 行锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

一、全局锁

典型使用场景是，做全库逻辑备份（在备份过程中整个库完全处于只读状态） 有两个风险点

1.如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；

2.如果在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

如果不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。下图就是个例子

 

 

 这个时候就想到了事务，在可重复读隔离级别下开启一个事务，（事务启动时的视图可以认为是静态的，不受其他事务更新的影响。）

mysql本身自带备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

一致性读是好，但前提是引擎要支持这个隔离级别（MyISAM 是不支持事务的引擎，所以只能使用FTWRL命令，这也是InnoDB 替代 MyISAM 的原因之一）

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

主要有两个原因：

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议使用。

二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

二、表锁MDL

MDL 不需要显式使用，在访问一个表的时候会被自动加上

读锁之间不互斥、读写锁之间、写锁之间是互斥的

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

所以如果一个表加字段/修改字段/加索引等需要扫描全表的数据的操作，一定要小心

1.首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁（有未提交的事务时无法修改表字段，而且在存在长事务时执行修改表字段命令是一个危险的操作，可能阻塞其它增删改查请求，或导致线程爆满）

2.如果是热点数据表，请求很频繁

比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后通过重试命令重复这个过程。

三、行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的，MyISAM 引擎就不支持行锁，InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

两阶段锁

先看下图例子

  事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放的。事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

也就是说，在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

所以如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。（减少锁冲突所造成的锁等待时间）

死锁和死锁检测

死锁的例子

 所以当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

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

所以一般采用第二种策略，主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了死锁，所以导致在有的时候并发量很高（操作同一行）的时候，每秒都执行不了几个事务，CPU使用率超高，死锁检测要耗费大量的 CPU 资源。

那怎么解决由这种热点行更新导致的性能问题呢

一种是临时把死锁检测关掉，但可能会出现大量的超时，这是业务有损的。

一种是控制并发度，在数据库服务端（中间件等方式来实现）或者在mysql源码里修改（进入引擎前排队）

更或者是把一行数据改成逻辑上的多行数据来减少锁冲突（比如账户金额分成多行，总额就是多行数据的总和），需要逻辑详细设计

问题：如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到，哪种方式比较好

第一种，直接执行 delete from T limit 10000;

第二种，在一个连接中循环执行 20 次 delete from T limit 500;

第三种，在 20 个连接中同时执行 delete from T limit 500。

答：

方案一，（即：直接执行 delete from T limit 10000），单个语句占用时间长，事务相对较长，锁的时间也比较长，会导致其他客户端等待资源时间较长，而且大事务还会导致主从延迟。
方案二，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。
方案三，（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。人为自己制造锁竞争，加剧并发量。
方案二相对比较好，具体还要结合实际业务场景。