平平淡淡从从容容

0%

MySQL各种锁

发表于 分类于 4.5k 4 分钟

在数据库的锁机制中介绍过，数据库管理系统(DBMS)中并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的原有特性，乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。无论是悲观锁还是乐观锁，都是人们定义出来的概念，是宏观的概念，可以认为是一种思想。

一、悲观锁(Pessimistic Lock)

  1. 先获取锁，再进行业务操作，即“悲观”的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。
    • 常所说的“一锁二查三更新”即指的是使用悲观锁。
    • 数据库上的悲观锁需要数据库本身提供支持，即通过常用的select ... for update操作来实现悲观锁。
    • 当数据库执行select ... for update时会获取被select中的数据行的行锁，其他并发执行的select ... for update如果试图选中同一行则会发生排斥以此达到锁的效果。
    • select ... for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
  2. 传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁
  3. 悲观锁的实现
    • 在对任意记录进行修改前，先尝试为该记录加上排他锁(exclusive locking)
    • 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常(具体响应方式由开发者根据实际需要决定)
    • 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了
    • 其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常

二、乐观锁(Optimistic Lock)

  1. 先进行业务操作而不主动去拿锁，即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好
  2. 乐观锁的实现完全是逻辑的，不需要数据库提供特殊的支持，一般的实现乐观锁的方式就是记录数据版本(实现数据版本有两种方式:1.使用版本号 2.使用时间戳)
    • 当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。
    • 当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

总的来说，MySQL各存储引擎使用了三种类型(级别)的锁定机制：行级锁定，页级锁定和表级锁定。

三、表级锁 table-level locking

  1. 表锁，给整个表加锁，又分为表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)
  2. MySIAM和Memory默认采用表级锁
  3. 阻塞装状态
    • 对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
    • 对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作
    • MyISAM表的读操作与写操作之间，以及写操作之间是串行的
    • 当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作，其他线程的读、写操作都会等待，直到锁被释放为止
  4. 加锁
    • MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁
    • 在执行更新操作(UPDATE、DELETE、INSERT等)前会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

四、行级锁 row-level locking

  1. 行锁,就是给某一条记录加锁，又分共享锁和排他锁。
  2. InnoDB支持行级锁和表级锁，默认行级锁
锁名称 特性
共享锁，又称读锁，S锁 1.若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。
2.这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改
3.通过使用select … lock in share mode加共享锁
排他锁，又称写锁，X锁 1.若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁(共享锁/排他锁)，直到T释放A上的锁。
2.这保证了其他事务在T释放A上的锁之前不能再读取和修改A
3.InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁
4.select语句默认不会加任何锁类型，通过select …for update加排它锁
2. 行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

五、页级锁 page-level locking

  1. 页级锁,就是给某一页加锁
  2. BDB采用页级锁或表级锁，默认页级锁

六、共享锁(Shared)

  1. 又称为S锁，读锁。
  2. 一个事务对数据对象O加了S锁，可以对O进行读取操作，但是不能进行更新操作。
  3. 加锁期间其它事务能对O加S锁，但是不能加X锁。

七、排它锁(Exclusive)

  1. 又称为X锁，写锁。
  2. 一个事务对数据对象O加了X锁，就可以对O进行读取和更新。
  3. 加锁期间其它事务不能对O加任何锁。

七、死锁

  1. 死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用,它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。
  2. 表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的InnoDB。
  3. 如何防死锁
    • 尽量避免并发的执行涉及到修改数据的语句。
    • 要求每一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行。
    • 预先规定一个加锁顺序，所有的事务都必须按照这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保证一致。
    • 每个事务的执行时间不可太长，对程序段的事务可考虑将其分割为几个事务。在事务中不要求输入，应该在事务之前得到输入，然后快速执行事务。
    • 使用尽可能低的隔离级别。
    • 数据存储空间离散法。该方法是指采用各种手段，将逻辑上在一个表中的数据分散的若干离散的空间上去，以便改善对表的访问性能。主要通过将大表按行或者列分解为若干小表，或者按照不同的用户群两种方法实现。
    • 编写应用程序，让进程持有锁的时间尽可能短，这样其它进程就不必花太长的时间等待锁被释放。

八、锁时间

  1. 查看锁时间 show status like 'innodb_row_lock%'
项 说明
Innodb_row_lock_current_waits 当前正在等待锁定的数量
Innodb_row_lock_time 从系统启动到现在锁定的总时间长度，单位ms
nnodb_row_lock_time_avg 每次等待所花平均时间
Innodb_row_lock_time_max 从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits 从系统启动到现在总共等待的次数
  1. 事务和锁相关的表
    • INNODB_LOCKS
    • INNODB_LOCK_WAITS
    • INNODB_TRX

九、间隙锁

  1. 使用范围条件而不是相等条件检索数据并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做”间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。举例来说，假如user表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：select * from user where user_id > 100 for update;是一个范围条件的检索，InnoDB不仅会对符合条件的user_id值为101的记录加锁，也会对user_id大于101（这些记录并不存在）的“间隙”加锁。

    • 间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。

  2. InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了user_id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。

  3. 间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。

    • 间隙锁是开区间

  4. 间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁，仔细想想的话，这个并不难理解，大家也可以自己测试一下。

实战

  1. 初始化表
1
2
3
4
5
6
7
CREATE TABLE `table_gap` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
);
  1. 初始化记录
1
insert into table_gap(id,name,age) values(1,1,1),(5,5,5),(10,10,10),(15,15,15),(19,19,10);

  1. session A：begin;select * from table_gap where age=10 for update;，此语句会在(1,5],(5,10]加锁

  2. session B：insert into table_gap(id,name,age) values(2,2,2);，此插入语句会被阻塞直至超时

十、Waiting for table metadata lock

在操作某个事务时，操作到一半没有提交就直接exit了，结果导致后来每次操作这张表都会卡住，通过show processlist可看到处于Waiting for table metadata lock状态的进程ID。

十一、全局锁 FTWRL

  1. 顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock(FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：
    • 数据更新语句(数据的增/删/改)
    • 数据定义语句DDL(包括建表、修改表结构等)
    • 更新类事务(增/删/改)的提交语句
  2. 全局锁的典型使用场景是，做全库逻辑备份
    • 也就是把整库每个表都select出来存成文本
    • FTWRL确保不会有其他线程对数据库做更新，然后对整个库做备份
    • 注意，在备份过程中整个库完全处于只读状态
  3. 全库进入只读状态 set global readonly=true
    • 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大。
    • 如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。