1、说一下数据库表锁和行锁吧

表锁，是在数据表上加锁。
特点：不会出现死锁，发生锁冲突几率高，并发低。
MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：表共享读锁和表独占写锁。 读锁会阻塞写，写锁会阻塞读和写

  • 对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
  • 对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

MyISAM不适合做写为主表的引擎，因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
行锁
会出现死锁，发生锁冲突几率低，并发高。
在MySQL的InnoDB引擎支持行锁，与Oracle不同，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。
行锁的实现需要注意：

  • 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
  • 两个事务不能锁同一个索引。

  • insert，delete，update在事务中都会自动默认加上排它锁。

    2、什么是死锁？如何解决死锁？

    死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
    常见的解决死锁的方法

  • 如果不同程序并发存取多个表，尽量约定 以相同的顺序访问表，可以大大降低死锁机会；

  • 在同一个事务中，尽可能做到 一次锁定所需要的所有资源，减少死锁产生概率；

  • 对于非常容易产生死锁的业务部分，可以尝试使用 升级锁定颗粒度，通过 表级锁 定来减少死锁产生的概率。

    3、什么是乐观锁和悲观锁？如何实现？

    DBMS 中的 并发控制 的任务是确保在 多个事务同时存取数据库中同一数据 时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
    悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。这对于长事务来讲，可能会严重影响系统的并发处理能力。实现方式：使用数据库中的锁机制。
    先获取锁，再进行业务操作，一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。当数据库执行SELECT … FOR UPDATE时会获取被select中的数据行的行锁，select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
    乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁适用于 读多写少 的应用场景，这样可以提高吞吐量。实现方式：一般会使用版本号机制或 CAS 算法实现。先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过。Java 并发包中的AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。

    4、数据库锁的种类，加锁的⽅式

    当多个⽤户同时对数据库并发操作时，会带来数据不⼀致的问题，锁主要⽤于多⽤户环境下保证数据库完整性和⼀致性。
    数据库锁出现的⽬的：处理并发问题；
    并发控制的主要采⽤的技术⼿段：乐观锁、悲观锁和时间戳。

  • 从数据库系统⻆度分为三种：排他锁、共享锁、更新锁。

  • 从程序员⻆度分为两种：⼀种是悲观锁，⼀种乐观锁。

06 讲全局锁和表锁：给表加个字段怎么有这么多阻碍
07 讲行锁功过：怎么减少行锁对性能的影响
数据库锁设计的初衷是处理并发问题。数据库需要合理地控制资源的访问规则。
根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

  • 全局锁就是对整个数据库实例加锁。
  • MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)
  • 典型使用场景是，做全库逻辑备份。
    全局只读的几种方法
  1. mysqldump

备份为什么要加锁？在一致性视图上进行备份。
官方自带的逻辑备份工具是mysqldump，使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
为什么还需要FTWRL？不是所有引擎都支持可重复读隔离级别。single-transaction方法只适用于所有的表使用事务引擎的库。不支持就得用FTWRL进行备份。

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

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

    表级锁

    两种：表锁，和元数据锁（meta data lock，MDL)。

    表锁

    语法是 lock tables … read/write。
    在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发。

    MDL

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

  • 作用是保证读写的正确性
  • MySQL 5.5版本后，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
    • 读锁之间不互斥，可以有多个线程同时对一张表增删改查。
    • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
  • 安全地给小表加字段
    • 解决长事务，kill或暂停DDL
    • 对于请求频繁的事务，设定等待时间去拿MDL写锁。要支持DDL NOWAIT/WAIT n这个语法。
      问题：
      当备库用–single-transaction做逻辑备份的时候，如果从主库的binlog传来一个DDL语句会怎么样？
      假设这个DDL是针对表t1的， 这里我把备份过程中几个关键的语句列出来：
      1. Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
      2. Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
      3. /* other tables */
      4. Q3:SAVEPOINT sp;
      5. /* 时刻 1 */
      6. Q4:show create table `t1`;
      7. /* 时刻 2 */
      8. Q5:SELECT * FROM `t1`;
      9. /* 时刻 3 */
      10. Q6:ROLLBACK TO SAVEPOINT sp;
      11. /* 时刻 4 */
      12. /* other tables */
      在备份开始的时候，为了确保RR（可重复读）隔离级别，再设置一次RR隔离级别(Q1);
      启动事务，这里用 WITH CONSISTENT SNAPSHOT确保这个语句执行完就可以得到一个一致性视图（Q2)；
      设置一个保存点，这个很重要（Q3）；
      show create 是为了拿到表结构(Q4)，然后正式导数据 （Q5），回滚到SAVEPOINT sp，在这里的作用是释放 t1的MDL锁 （Q6。当然这部分属于“超纲”，上文正文里面都没提到。
      DDL从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。
      参考答案如下：
  1. 如果在Q4语句执行之前到达，现象：没有影响，备份拿到的是DDL后的表结构。
  2. 如果在“时刻 2”到达，则表结构被改过，Q5执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump终止；
  3. 如果在“时刻2”和“时刻3”之间到达，mysqldump占着t1的MDL读锁，binlog被阻塞，现象：主从延迟，直到Q6执行完成。
  4. 从“时刻4”开始，mysqldump释放了MDL读锁，现象：没有影响，备份拿到的是DDL前的表结构。

    行锁

    MySQL的行锁是在引擎层由各个引擎自己实现的。
    但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。
    行锁就是针对数据表中行记录的锁。
    两阶段锁
    在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
    如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
    这并没有完全解决问题。
    死锁和死锁检测
    当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
    解决死锁：
  • 直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。

在InnoDB中，innodb_lock_wait_timeout的默认值是50s，太长。

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

怎么解决由这种热点行更新导致的性能问题呢？问题的症结在于，死锁检测要耗费大量的CPU资源。

  • 如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但可能业务有损。
  • 控制并发度。在客户端做不一定成功，尝试放到数据库服务端。例如将一行改成逻辑上的多行来减少锁冲突。

问题：

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

  • 第一种，直接执行delete from T limit 10000;
  • 第二种，在一个连接中循环执行20次 delete from T limit 500;
  • 第三种，在20个连接中同时执行delete from T limit 500。

你会选择哪一种方法呢？为什么呢？
方案一，事务相对较长，则占用锁的时间较长，会导致其他客户端等待资源时间较长。
方案二，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。
方案三，人为自己制造锁竞争，加剧并发量。
方案二相对比较好，具体还要结合实际业务场景。