Mysql里面的锁大致可以分为全局锁、表锁和行锁三类。

1、全局锁：

  • 1、全局锁是对数据库实例加锁。
  • 2、全局锁的典型使用场景：做全库逻辑备份。
  • 加全局读锁的方法：flush table with read lock。
  • set global readonly=true 也可以让库进入只读状态，它与RTWRL的区别是：
  • readonly的值会被用作其他逻辑判断，比如判断一个库是主库还是备库。
  • 执行FTWRL命令之后客户端异常断开时，mysql会自动释放全局锁；而设置readonly全局变量，客户端发生异常，数据库会一直保持readonly的状态，风险较高。
  • 3、当然可以在RR隔离级别下开启事务进行备份，但是需要数据库使用的引擎支持事务。

2、表级锁

Mysql里面有两种表级别的锁：一种是表锁；一种是原数据锁（MDL），在mysql 5.5版本中引入。
表锁一般是在数据库引擎不支持行锁的时候才会被用到。

  • 1、lock tables t1 read, t2 write：对表t1加读锁，对表t2加写锁。加锁的线程也只能对t1进行读取不能写入，对t2进行读写。
  • 2、MDL锁不需要显示使用。对表做增删改查的操作会加DML读锁，对表结构做变更时，会加DML写锁。
  • 读锁之间互相不排斥，即可以有多个线程对表进行增删改。
  • 但是若有线程对表结构进行修改，只有此线程可以读表，其他线程的所有操作都被阻塞。
  • 3、给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。
  • 4、事务中的MDL锁，在语句开始执行时申请，并不是在语句结束时释放，而是等事务提交时候才会释放。
    以下sql sessionC申请的写锁被DML读锁阻塞，而sessionD被也会被前面排队的写锁阻塞。因此当请求频繁且有重试机制时，数据库的线程很快就会爆满。
  • 5、在热点表上修改表结构时，由于请求比较频繁，需要小心使用MDL写锁，避免线上查询和更新阻塞。
  • 比较理想的机制是：在alter语句里面增加等待时间，在等待时间内未获取到MDL写锁，先放弃，不要阻塞后面的业务语句。

2.2、思考题

  • 1、备份一般都会在备库上执行，你在用–single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

3、行锁

行锁是在存储引擎层面实现的。比如MyISAM并不支持行锁，并发控制需要通过表所实现。而Innodb支持行锁，这也是MyISAM被Innodb取代的原因之一。

  • 1、两阶段锁协议：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
  • 2、知道两阶段锁之后有什么作用呢？
  • 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
  • 3、案例：服务区的CPU消耗接近100%，但是数据库每秒执行不到100个事务。
  • 原因之一可能是：死锁检测消耗CPU，并且可能产生了死锁。
  • 4、死锁和死锁检测：当出现死锁之后，一搬有两种办法：
  • 超时等待。设置innodb_lock_wait_timeout ,超时后会退出。一般是50s。
  • 死锁检测。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
  • 4、怎么解决热点行更新导致的性能问题？ 症结在于死锁检测需要大量的CPU资源。
  • 关闭死锁检测。
  • 控制并发度（控制在数据库服务端：对于相同行的更新，在进入存储引擎之前进行排队）。
  • 数据库服务端做并发控制：比如对相同行的更新，在进入引擎之前就行排队。
  • 在设计上优化：比如把一行数据拆分成多行，更新时随机选取一行更新。

3.2、思考题

  • 1、如果你要删除一个表里面的前10000行数据，有以下三种方法可以做到，选择哪种更合理？
    第一种，直接执行delete from T limit 10000;
    第二种，在一个连接中循环执行20次 delete from T limit 500;
    第三种，在20个连接中同时执行delete from T limit 500