您的位置: 首页  >  文章  >  MySQL面试之锁的详解

MySQL面试之锁的详解

分类: 文章 • 2025-01-27 21:37:52

什么是锁？MySQL中提供了几类锁？

锁是实现数据库并发控制的重要手段，可以保证数据库在多人同时操作时能够正常运行。MySQL提供了全局锁、行级锁、表级锁。其中InnoDB支持表级锁和行级锁，MyISAM只支持表级锁。其详细解析如下所示：
MySQL面试之锁的详解

什么是加锁粒度？

所谓加锁粒度就是你要锁住的范围是多大。exp：上厕所，只需要锁住卫生间就行，这卫生间就是加锁的粒度。

MySQL操作加锁情况

update、delete、insert都会自动给涉及到的数据加上排他锁。select语句不会加任何类型的锁。

什么是死锁？

  • 指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象。(A需要B中的一个资源才能释放，同时B需要A中的一个资源才能释放，而A、B同时等待对方释放资源)

常见的死锁案例有哪些？

  • 1.将投资的钱拆封几份借给借款人，这是处理业务逻辑就要把若干个借款人一起锁住select * from xxx where id in (xx,xx,xx) for update。
  • 2.批量入库，存在则更新，不存在则插入。解决方法insert into tab(xx,xx) on duplicate key update xx='XX'。

如何处理死锁？

  • 1.通过innodb lockwait_timeout 来设置超时时间，一直等待直到超时（默认设置50s）。
  • 2.发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事物，让其他事物继续执行（innodbdeadlockdetect设置为on，可以主动检测死锁）。

如何查看死锁？

  • 使用命令 show engine innodb status 查看最近的一次死锁。
  • InnoDB Lock Monitor 打开锁监控，每15s输出一次日志。使用完毕后关闭。

如何避免死锁？

  • 为了在单个InnoDB表上执行多个并发写操作时避免死锁，可以在事物开始时通过为预期要修改的每个元祖（行）使用 SELECT ... FOR UPDATE 语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
  • 在事物中，如果要更新记录，应该直接申请足够级别的锁，级排他锁，而不应先申请共享锁，更新时再申请排他锁，因为这时候当用户再申请排它锁时，其他事物可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
  • 如果事物需要修改或锁定多个表，则应在每个事物中以相同的顺序使用加锁语句。在应用中，如果不同的程序并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
  • 通过SELECT ... LOCK IN SHARE MODE 获取行的读锁后，如果当前事物再需要对该记录进行更新操作，很有肯能造成死锁。
  • 改变事物隔离级别

什么是全局锁？它的应用场景有哪些？

全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份。这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句、更新类事物的提交语句等操作都会被阻塞。

使用：

  • FTWRL就可以实现设置数据库为全局只读锁 flush tables with read lock
  • set global readonly=true (需要手动取消)

表级锁/行级锁/页面锁

表级锁:开销小，加锁快；不会出现死锁；锁的粒度大，发生锁冲突的概率最高，并发度低。
使用：

  • lock tables read/write
  • unlock tables主动释放锁

行级锁：开销大，加锁慢；会出现死锁；锁的粒度最小，发生锁冲突的概率最低，并发度也最高。包含：共享锁和排它锁。
页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁的粒度介于表锁和行锁之间，并发度一般。

悲观锁/乐观锁

悲观锁：每次去拿数据都会认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到拿到锁。因此，悲观锁需要消耗较多的时间，另外与乐观锁相应的，悲观锁由数据库自己实现，要用的时候直接调用数据库相关的语句。行锁、表锁、读锁、写锁都属于悲观锁。
乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁。但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。可以使用版本号机制和CAS算法来实现。

  • 版本号机制：为数据增加一个标识，一般通过为数据库表增加一个数字类型的version字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
  • CAS算法：CAS就是一种非阻塞算法，也是一种乐观锁计划，是一种无锁算法。后续详细更新。
悲观锁/乐观锁优缺点

悲观锁：消耗更长的时间。
乐观锁：执行性能发高，容易产生ABA问题、循环时间长开销大、只能保证一个共享变量的原子操作

  • ABA问题：如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那我们就能说明它的值没有被其他线程修改过了吗？很明显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回A，那CAS操作就会误认为它从来没有被修改过。
  • 循环时间长开销大：自旋CAS（也就是不成功就一直循环执行直到成功）如果长时间不成功，会给CPU带来非常大的执行开销。
  • 只能保证一个共享变量的原子操作：CAS 只对单个共享变量有效，当操作涉及跨多个共享变量时 CAS 无效。可以把多个共享变量合并成一个共享变量来操作。

什么是共享锁？

共享锁又称读锁(read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事物都不能对数据进行修改(获取数据上的排它锁)，直到以释放所有共享锁。当如果事物对读锁进行修改操作，很可能会造成死锁。
使用：

  • LOCK TABLE table_name [AS alias_name] READ
  • SELECT ... LOCK IN SHARE MODE

什么是排它锁？

排它锁exclusive lock又称写锁(writer lock)。若某个事物对某一行加上了排它锁，只能这个事物对其进行读写，在此事物结束之前，其他事物不能对其进行任何加锁。其他进程可以读取，不能进行写操作，需等待其释放。
使用：

  • LOCK TABLE table_name [AS alias_name][LOW_PRIORITY] WRITE
  • SELECT ... FOR UPDATE

优化锁

  • 尽量使用较低的隔离级别
  • 精心设计索引，并尽量使用索引访问数据，使加锁更加精确，从而减少锁冲突的机会。
  • 选择合理的事物大小，小事物发生锁冲突的几率也更小
  • 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据时，做好直接使用排它锁。
  • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。
  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
  • 不要申请超过实际需要的锁级别。
  • 除非必要，查询时不要显示加锁。
  • 对于一些的特定的事物，可以使用表锁来提高处理速度或减少死锁的可能。

相关推荐