mysql 第15章 表锁 mysql 第15章 表锁

2022-06-18

一、什么是锁

  • 锁是计算机协调多个线程并发访问某一资源的机制。

  • 在数据库中,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。

  • MySQL的锁机制比较简单,表锁由MySQL Server实现,行锁则是存储引擎各自实现,不同的引擎实现的方式不同。在MySQL的常用引擎中InnoDB支持行锁,而MyISAM则只能使用MySQL Server提供的表锁。

  • 几种常见锁的比较:

  • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

  • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

二、MyISAM 的表锁

MySQL的表级锁有两种模式:

  • 表共享读锁(Table Read Lock)

  • 表独占写锁(Table Write Lock)

  • 给表加共享读锁语法:lock table 表名 read

  • 给表加独占写锁语法:lock table 表名 write

演示表结果:

mysql> show create table testmyisam \G;
*************************** 1. row ***************************
       Table: testmyisam
Create Table: CREATE TABLE `testmyisam` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

例1:演示表共享读锁

https://file.lulublog.cn/images/3/2022/08/Z2XRZ3A29WA1E29aeveRresDT23R12.jpg

例2:演示表独占写锁

https://file.lulublog.cn/images/3/2022/08/VZBw3p3QMfB44TZBTFtFd3wMZ33CZ0.jpg

总结

  • 对MyISAM表加了共享读锁,对于其他session,不会阻塞对同一个表的读请求,阻塞对同一个表的写请求;对于当前session,对同一个表的写请求会直接报错。

  • 一个session中只要使用了lock table加了表锁,不管是共享读锁还是独占写锁,在当前session中,对其他表或用表别名访问同一个表会直接报错。

  • 对MyISAM表加了独占写锁,对于其他session,对同一个表的所有请求都会阻塞;对于当前session,可以对同一个表进行CRU

因为使用MyISAM、MEMORY、MERGE这些存储引擎的表在同一时刻只允许一个会话对表进行写操作,所以这些存储引擎实际上最好用在只读,或者大部分都是读操作,或者单用户的情景下。另外,在MyISAM存储引擎中有一个称之为Concurrent Inserts的特性,支持在对MyISAM表读取时同时插入记录,这样可以提升一些插入速度。

三、InnoDB 的表锁

  • 表级别的S锁、X锁

    • 和MyISAM的表锁差别不大。注意开启一个新事务的时候会释放表锁。


  • 元数据锁

    • 在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时,InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。另外,在对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时,其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞,同理,某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时,在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server 层使用一种称之为元数据锁(英文名:Metadata Locks,简称MDL)来实现的。

  • 表级别的IS锁、IX锁

    • 当我们在对使用InnoDB存储引擎的表的某些记录加S锁之前,那就需要先在表级别加一个IS锁,当我们在对使用InnoDB 存储引擎的表的某些记录加X锁之前,那就需要先在表级别加一个IX锁。

    • IS锁和IX锁的使命只是为了后续在加表级别的S锁和X锁时判断表中是否有已经被加锁的记录,以避免用遍历的方式来查看表中有没有上锁的记录。我们并不能手动添加意向锁,只能由InnoDB存储引擎自行添加。

  • 表级别的AUTO-INC锁

    • 在使用MySQL 过程中,我们可以为表的某个列添加AUTO_INCREMENT属性,之后在插入记录时,可以不指定该列的值,系统会自动为它赋上递增的值,系统实现这种自动给AUTO_INCREMENT修饰的列递增赋值的原理主要是两个:

    • 采用AUTO-INC锁,也就是在执行插入语句时就在表级别加一个AUTO-INC锁,然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值,在该语句执行结束后,再把AUTO-INC锁释放掉。这样一个事务在持有AUTO-INC锁的过程中,其他事务的插入语句都要被阻塞,可以保证一个语句中分配的递增值是连续的。如果我们的插入语句在执行前不可以确定具体要插入多少条记录(无法预计即将插入记录的数量),比方说使用INSERT…SELECT、REPLACE…SELECT或者LOAD DATA这种插入语句,一般是使用AUTO-INC锁为AUTO_INCREMENT修饰的列生成对应的值。

    • 采用一个轻量级的锁,在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级锁,然后生成本次插入语句需要用到的AUTO_INCREMENT列的值之后,就把该轻量级锁释放掉,并不需要等到整个插入语句执行完才释放锁。如果我们的插入语句在执行前就可以确定具体要插入多少条记录,那么一般采用轻量级锁的方式对AUTO_INCREMENT修饰的列进行赋值。这种方式可以避免锁定表,可以提升插入性能。

  • InnoDB提供了一个称之为innodb_autoinc_lock_mode的系统变量来控制到底使用上述两种方式中的哪种来为AUTO_INCREMENT修饰的列进行赋值:

    • https://file.lulublog.cn/images/3/2022/08/jA57rebzgN6p5p65ZaJTbRXWXxaA9n.jpg

    • MySQL5.7.X中缺省为1。innodb_autoinc_lock_mode的取值说明:

    • 值为0时:一律采用AUTO-INC锁

    • 值为2时:一律采用轻量级锁

    • 值为1时:两种方式混着来(也就是在插入记录数量确定时采用轻量级锁,不确定时使用AUTO-INC锁)。

    • 不过当innodb_autoinc_lock_mode值为2时,可能会造成不同事务中的插入语句为AUTO_INCREMENT修饰的列生成的值是交叉的,在有主从复制的场景中是不安全的。

阅读 715