sql 锁

锁

 

数据库锁

因为数据库要解决并发控制问题。在同一时刻，可能会有多个客户端对同一张表进行操作，比如有的在读取该行数据，其他的尝试去删除它。为了保证数据的一致性，数据库就要对这种并发操作进行控制，因此就有了锁的概念。

简述乐观锁和悲观锁

 

乐观锁：对于数据冲突保持一种乐观态度，操作数据时不会对操作的数据进行加锁，只有到数据提交的时候才通过一种机制来验证数据是否存在冲突。

悲观锁：对于数据冲突保持一种悲观态度，在修改数据之前把数据锁住，然后再对数据进行读写，在它释放锁之前任何人都不能对其数据进行操作，直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁，然后才可以对数据进行操作，

一般数据库本身锁的机制都是基于悲观锁的机制实现的。

 

简述MySQL中的按粒度的锁分类

 行级锁是 MySQL 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MyISAM 与 InnoDB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。

因此，采取了折衷的页级锁，一次锁定相邻的一组记录（行）。BDB 支持页级锁。

共享锁，又称之为读锁，简称S锁，当事务对数据加上读锁后，其他事务只能对该数据加读锁，不能做任何修改操作，也就是不能添加写锁。只有当数据上的读锁被释放后，其他事务才能对其添加写锁。

共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免”不可重读”的问题的出现。

排它锁，又称之为写锁，简称X锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读写，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。

写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。

MySQL 常用存储引擎的锁机制

• MyISAM 和 Memory 采用表级锁（table-level locking）
• BDB 采用页级锁（page-level locking）或表级锁，默认为页级锁；
• InnoDB 支持行级锁（row-level locking）和表级锁，默认为行级锁。

InnoDB 中的行锁与表锁

         前面提到过，在 InnoDB 引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表？什么时候只锁住一行呢？

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。

• InnoDB 这种行锁实现的特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

 

InnoDB 存储引擎的锁的算法有三种：

• Record lock：记录锁，单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身

 

死锁

官方定义如下：两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

这个就好比你有一个人质，对方有一个人质，你们俩去谈判说换人。你让对面放人，对面让你放人。

原因

• 两个以上的并发事务
• 每个事务当前持有了锁，且未释放
• 每个事务都在申请新的锁
• 事务之间产生了锁资源的循环等待

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

解决

MySQL有两种死锁处理方式：

• 等待，直到超时（innodb_lock_wait_timeout=50s）。
• 发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）。

由于性能原因，一般都是使用死锁检测来进行处理死锁。

死锁检测

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

如何避免发生死锁

• 创建索引，可以使创建的锁更少。
• 操作完之后立即提交事务，特别是在交互式命令行中
• 以固定的顺序访问表和行。即按顺序申请锁，这样就不会造成互相等待的场面
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

 

避免死锁的方法

有多种方法可以避免死锁，这里只介绍常见的三种：

1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低发生死锁的可能性；
2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

 

事务和锁机制是什么关系

https://www.zhihu.com/question/23242151

https://www.cnblogs.com/liang24/p/14145267.html

数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别

开启事务就自动加锁。

 

ANSI SQL标准定义了4种事务隔离级别来避免3种数据不一致的问题。事务等级从高到低，分别为：

1.Serializable（序列化）

最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。innoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

可能导致大量的超时现象和锁竞争。

2.Repeatable read（可重复读）

这是MySQL的默认事务隔离级别

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改---幻读 ---一个事务内两次读取的结果集数目不同，重点是新增或删除

行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB引入了间隙锁（Gap Lock）。

间隙锁:锁住某个范围，但不包括记录本身.确保了无法再插入新的记录。所以用行锁+间隙锁解决幻读问题。行锁保证更新行，间隙锁保证插入行

3.Read Committed（已提交读）

允许读取并发事务已经提交的数据。大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) -----不可重复读  --在一个事务中两次读取同一条数据的结果不同，重点是修改

所以读锁在事务中持有可以保证不出现不可重复读

4.Read Uncommitted（未提交读）

最低的隔离级别，允许读取尚未提交的数据变更---导致脏读、（一个事务读取了另一个事务未提交的数据）--加写锁就可以保证不出现脏读