MySQL的全局锁、表锁和行锁，如何解决并发问题？
MySQL的全局锁、表锁和行锁，如何解决并发问题？
在数据库管理系统中，锁机制是保证数据一致性和完整性的重要手段。本文将详细介绍MySQL中的全局锁、表锁和行锁，以及如何通过这些锁机制来解决并发问题。
全局锁
全局锁是MySQL中最粗粒度的锁，当加锁之后，整个库都会处于只读状态，包括数据更新、修改表结构和新建表等语句都会被阻塞，直到数据库被释放。
加全局锁命令
FLUSH TABLES WITH READ LOCK;
释放全局锁命令
UNLOCK TABLES;
全局锁最经典的使用场景是做全库逻辑备份。但是这样做会导致备份数据出现延迟或数据不一致。如果要拿到一致性视图，可以使用可重复读的隔离级别（详见：3、事务的特性讲解，ACID之事务隔离）在这个隔离级别中通过创建事务，保证拿到的数据是一致的，同时还要确保当前的存储引擎支持事务，像对于MyISAM存储引擎就不支持事务。
mysqldump是官方自带的逻辑备份工具，使用参数--single-transaction，当前这个方法适用于所有的表使用事务引擎的库。
为什么不使用set global readonly=true方式做全库只读？
- 在有些系统中，
readonly的值会被用来做其他逻辑，用来判断一个库是主库还是备库； - 在异常处理机制上有差异，如果客户端发生异常，数据库会一直保持
readonly状态，会导致这个库长时间处于不可写状态，风险较高；
表级锁
MySQL里的表级锁主要包括表锁和元数据锁（meta data lock，MDL），确保在多个会话（或事务）同时访问同一张表时，数据的完整性和一致性得到保障。当前锁的粒度较粗，适用于并发不高的场景。
表锁的语法
LOCK TABLES ... READ/WRITE;
释放锁的语法
UNLOCK TABLES;
或客户端断开的时候自动释放
在MDL锁中，读锁之间不会互斥，只有写锁和读锁或写锁会互斥，MDL锁是系统默认添加的（MySQL5.5中引入的）。
上图中的MDL锁，sessionA在给表t申请读锁，sessionB也能够申请表t的读锁，在sessionC要申请写锁时，会被阻塞等待前面的读锁释放，sessionD在申请读锁时，由于sessionC被阻塞了，导致sessionD的读锁也需要等待。
行锁
正常情况下很多存储引擎不支持行锁，比如在MySQL5.5之前的MyISAM引擎只支持表锁，InnoDB是支持行锁的一个主要的存储引擎。
两阶段锁
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
在上图中事务B会被阻塞直到事务A的commit提交之后，才会释放锁，执行事务B的update。
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放；比如说在两个事务中，都需要同时操作两次update语句和一次的insert语句，在执行第二个insert语句时两个事务需要对同一个数据库作update操作，此时就会产生并发性的操作，就需要将这条语句排在整个事务的末尾顺序，最大程度地减少了事务之间的锁等待。
死锁和死锁检测
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数
innodb_lock_wait_timeout来设置。 - 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数
innodb_deadlock_detect设置为on，表示开启这个逻辑。
在 InnoDB 中，innodb_lock_wait_timeout的默认值是 50s，但是这个值设置的时间过长或者过短都不能很好的解决这个问题（推荐使用第二种方案，innodb_deadlock_detect的默认值本身就是on）。在第二种方案中每一个被堵住的线程进来都要判断一次，会极大的增加cpu的性能问题，导致cpu利用率很高，但是每秒执行的事务却很少。
推荐的一个思路就是控制并发度，可以在服务端添加中间件实现，或者修改MySQL源码，通过在进入引擎之前就控制并发数量；也可以通过在表逻辑设计上，将一条数据拆分开，每次随机选择一条数据做修改操作，这个操作在数据清零时需要有额外的操作。
本文原文来自CSDN