简单了解数据库锁机制与分类

一、概述

数据库是一个多用户使用的共享资源，当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能读取或存取不正确的数据，破坏数据的不正确性（脏读，不可重复读，幻读等），可能产生死锁。锁主要用于多用户环境下保证数据库完整性和一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁，加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。简单来说，当执行SQL语句事务前，应向数据库发出请求对你访问的记录加锁，在这个事务释放锁之前，其他事物不能对这个数据进行修改更新操作。

数据库锁出现的目的：处理并发问题。

二、发生锁的必要条件

数据库锁表的四个必要条件：
（1）互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用完释放后其他进程才能使用。
（2）请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不释放的状态。
（3）不剥夺条件：指进程已获得的资源，在未使用完之前不能被剥夺，只能在使用完时由自己释放。
（4）环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

三、锁的分类

从数据库系统角度分为三种：排他锁、共享锁、更新锁。从程序员角度分为两种：一种是悲观锁，一种是乐观锁。
乐观并发控制和悲观并发控制是并发控制采用的主要方法。乐观锁和悲观锁不仅在关系数据库里应用，在Hibernate、Memcache等也有相关概念。
如下图所示分类：

悲观锁：即悲观并发控制(Pessimistic Concurrency Controller，缩写PCC)。悲观锁是指在数据处理过程中，使数据处于锁定状态，一般使用数据库的锁机制实现。即每次去获取数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人拿这个数据就会block（阻塞），直到它拿到锁。传统的关系数据库里用到了很多这种锁机制，比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁。

注意：在MySQL中使用悲观锁，必须关闭MySQL的自动提交即set autocommit=0。MySQL默认使用自动提交autocommit模式，即执行一个更新操作，MySQL会自动将结果提交。

悲观锁按使用性质划分为共享锁、排他锁、更新锁三种。
共享锁（Share Lock）
S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。X锁和S锁都是加在某一个数据对象上的。
共享锁有如下性质：

  • 多个事务可封锁同一个共享页；
  • 任何事务都不能修改该页；
  • 通常是该页被读取完毕，S锁立即被释放。
-- 例如在执行sql显示加上共享锁
select * from t_user lock in share mode;

排他锁（Exclusive Lock）
X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。原理类似试衣间（某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面打开）。

排他锁有如下性质：

  • 仅允许一个事务封锁此页；
  • 其他任何事务必须等到X锁被释放才能对该页进行访问；
  • X锁一直到事务结束才能被释放。

SELECT … FOR UPDATE会锁住行及任何关联的索引条目，和你对那些行执行update语句相同。其他的事务会被阻塞在对这些行执行update操作。所有被共享锁和排他锁查询所设置的锁都会在事务提交或者回滚之后被释放。（使用SELECT FOR UPDATE为update操作锁定行，只适用于autocommit被禁用的情况下）。

-- 产生排他锁的SQL语句如下：
select * from ad_plan for update;

更新锁（Update Lock）也叫U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。
更新锁有如下性质：

  • 用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；
  • 当被读取的页要被更新时，则升级为X锁；
  • U锁一直到事务结束时才能被释放。

悲观锁按作用范围划分为：行级锁、表级锁。
行级锁：锁的作用范围是行级别。行锁表示对一条记录加锁，只影响一条记录。通常用在DML语句中，如INSERT, UPDATE, DELETE等。InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。

行级锁是一种排他锁，防止其他事务修改此行。在使用以下语句时，Oracle会自动应用行级锁：INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新。使用COMMIT或ROLLBACK语句释放锁。
行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力从而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

表级锁：锁的作用范围是整张表。表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

锁表发生在insert、update 、delete中。 锁表的原理是数据库使用独占式封锁机制，当执行上面的语句时对表进行锁住，直到发生commite或者回滚或者退出数据库用户。
锁表的原因：
第一、 A程序执行了对tableA的insert但还未commite时，B程序也对tableA进行insert则此时会发生资源正忙的异常就是锁表。
第二、锁表常发生于并发而不是并行（并行时个线程操作数据库时，另一个线程是不能操作数据库的）。

如何减少锁表的概率：减少insert 、update 、delete语句执行到commite之间的时间。也就是说将批量执行改为单个执行、优化sql自身的非执行速度。如果异常则对事务进行回滚。

mysql各存储引擎支持的锁：
BDB：支持页级锁和表级锁，默认是页级锁。
InnoDB：支持行级锁和表级锁，默认是行级锁。
MyISAM &Memory：这两个存储引擎都是采用表级锁。

数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。

乐观锁（Optimistic Lock）：
每次去拿数据的时候都认为别人不会修改，所以不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号、时间戳等机制去判断是否被更新过。

(1)版本号（记为version）
就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段，每次更新把这个字段加1，读取数据的时候把version读出来，更新的时候比较version，如果还是开始读取的version就可以更新了，如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。这里的关键是判断version和更新两个动作需要作为一个原子单元执行，否则在你判断可以更新以后正式更新之前有别的事务修改了version，这个时候你再去更新就可能会覆盖前一个事务做的更新，造成第二类丢失更新，所以你可以使用update … where … and version=”old version”这样的语句，根据返回结果是0还是非0来得到通知，如果是0说明更新没有成功，因为version被改了，如果返回非0说明更新成功。

(2)时间戳（timestamp，使用数据库服务器的时间戳）
和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。

(3)待更新字段
和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），如果是就把我修改的count的值更新到该字段，否则更新失败。

(4)所有字段
和待更新字段类似，只是使用所有字段做版本控制信息，只有所有字段都没变化才会执行更新。

四、发生锁的原因

发生锁表可能的原因有：
(1)字段不加索引:在执行事务的时候，如果表中没有索引，会执行全表扫描，如果这时候有其他的事务过来，就会发生锁表。
(2)事务处理时间长:事务处理时间较长，当越来越多事务堆积的时候，会发生锁表。
(3)关联操作太多:涉及到很多张表的修改等，在并发量大的时候，会造成大量表数据被锁。

五、解决锁出现的方法

出现锁表时解决方法有如下几种：
1、通过相关的sql语句可以查出是否被锁定，和被锁定的数据。
2、为加锁进行时间限定，防止无限死锁。
3、加索引，避免全表扫描。
4、尽量顺序操作数据。
5、根据引擎选择合理的锁粒度。
6、事务中的处理时间尽量短。

生产中出现死锁等问题是比较严重的问题，因为通常死锁没有明显的错误日志，只有在发现错误的时候才能后知后觉的处理，所以，一定要尽力避免。

六、锁定时间的长短

锁保持的时间长度为保护所请求级别上的资源所需的时间长度。
用于保护读取操作的共享锁的保持时间取决于事务隔离级别。采用READ COMMITTED的默认事务隔离级别时，只在读取页的期间内控制共享锁。在扫描中，直到在扫描内的下一页上获取锁时才释放锁。如果指定HOLDLOCK提示或者将事务隔离级别设置为REPEATABLE READ或SERIALIZABLE，则直到事务结束才释放锁。

根据为游标设置的并发选项，游标可以获取共享模式的滚动锁以保护提取。当需要滚动锁时，直到下一次提取或关闭游标（以先发生者为准）时才释放滚动锁。但是，如果指定HOLDLOCK，则直到事务结束才释放滚动锁。

用于保护更新的排它锁将直到事务结束才释放。
如果一个连接试图获取一个锁，而该锁与另一个连接所控制的锁冲突，则试图获取锁的连接将一直阻塞到：将冲突锁释放而且连接获取了所请求的锁。 连接的超时间隔已到期。默认情况下没有超时间隔，但是一些应用程序设置超时间隔以防止无限期等待。

七、如何锁表或锁表的某一行

（1）锁一个表的某一行
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED //设置事务隔离级别为读未提交
SELECT * FROM table ROWLOCK WHERE id = 1

（2）锁定数据库的一个表
SELECT * FROM table WITH (HOLDLOCK)

HOLDLOCK持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放等于SERIALIZABLE事务隔离级别。
NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于READ UNCOMMITTED事务隔离级别。
PAGLOCK 使用一个表锁的地方用多个页锁。
ROWLOCK 强制使用行锁。
TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表。
UPLOCK 强制在读表时使用更新而不用共享锁。

八、数据库隔离级别

每一种隔离级别满足不同的数据要求，使用不同程度的锁。
Read Uncommitted，读写均不使用锁，数据的一致性最差，也会出现许多逻辑错误。
Read Committed，使用写锁，但是读会出现不一致，不可重复读。
Repeatable Read, 使用读锁和写锁，解决不可重复读的问题，但会有幻读。
Serializable, 使用事务串形化调度，避免出现因为插入数据没法加锁导致的不一致的情况。

（1）丢失修改/丢失更新(lose update)
两个事务T1和T2读入同一数据并修改，T2的提交结果破坏了T1的提交结果，导致T1的修改被丢失。

（2）脏读
脏读是指事务T1读取了事务T2更新后的数据(已写入磁盘)，然后T2回滚操作，那么T1读取到的数据是脏数据。
例如：A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致。

（3）不可重复读(non-repeatable read)
A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致。
不可重复读是指事务T1读取数据后，事务T2执行更新操作，使事务T1无法再现前一次读取结果。主要分三种情况：

  • 事务T1读取某一数据后，事务T2对其进行了修改，当事务T1再次读取该数据时，得到的值与前一次不同；
  • 事务T1按照某一条件读取某些数据后，事务T2删除了其中部分记录，当事务T1再次以相同条件读取数据时，发现某些记录神秘消失了；
  • 事务T1按照某一条件读取某些数据后，事务T2在符合该条件下插入了部分记录，当事务T1再次以相同条件读取数据时，发现多了一些记录。

第二条和第三条好像发生幻觉一样，数据莫名其妙多了或者少了，有时也被称为幻影(phantom row) 或 幻读，其注重插入和删除操作，而第一种注重修改操作。

针对不可重复读的解决方案如下：

  • 对于第一种，对象是一条记录或者多条记录，解决此问题需要锁行(row locks)，其他的记录不用关心；
  • 对于幻读，操作对象是整个表，所以需要锁表(table locks)。

产生上面三种数据不一致的主要原因是破坏了事务的隔离性。

（1）读不提交，造成脏读(Read Uncommitted)
一个事务中的读操作可能读到另一个事务中未提交修改的数据，如果事务发生回滚就可能造成错误。

例子：A打100块给B，B看账户，这是两个操作，针对同一个数据库，两个事物，如果B读到了A事务中的100块，认为钱打过来了，但是A的事务最后回滚了，造成损失。避免这些事情的发生就需要我们在写操作的时候加锁，使读写分离，保证读数据的时候，数据不被修改，写数据的时候，数据不被读取。从而保证写的同时不能被另个事务写和读。
没有解决脏读问题，不建议使用。

（2）读提交(Read Committed)
我们加了写锁，就可以保证不出现脏读，也就是保证读的都是提交之后的数据，但是会造成不可重复读，即读的时候不加锁，一个读的事务过程中，如果读取数据两次，在两次之间有写事务修改了数据，将会导致两次读取的结果不一致，从而导致逻辑错误。
在READ COMMITTED级别下，除了唯一性的约束检查(duplicate-key checking)和外键约束检查(foreign-key constraint checking)需要使用gap lock外，其他情况InnoDB不会使用此算法。所以由于gap lock算法禁用，该级别下是会出现幻读的。
只读提交隔离级别仅支持基于行的二进制日志记录。如果使用read committed with binlog_format=mixed，服务器将自动使用基于行的日志记录。

  • 对于update或delete语句，innodb只对其更新或删除的行持有锁。在mysql评估where条件之后，将释放非匹配行的记录锁。这大大降低了死锁的可能性，但它们仍然可能发生。
  • 对于update语句，如果一行已经被锁定，innodb执行“semi-consistent”读取，将最新提交的版本返回给mysql，这样mysql就可以确定该行是否与更新的where条件匹配。如果行匹配（必须更新），mysql会再次读取行，这次innodb要么锁定它，要么等待对它的锁定。

（3）可重读(Repeatable Read）
解决不可重复读问题，一个事务中如果有多次读取操作，读取结果需要一致（指的是固定一条数据的一致，幻读指的是查询出的数量不一致）。 这就牵涉到事务中是否加读锁，并且读操作加锁后是否在事务commit之前持有锁的问题，如果不加读锁，必然出现不可重复读，如果加锁读完立即释放，不持有，那么就可能在其他事务中被修改，若其他事务已经执行完成，此时该事务中再次读取就会出现不可重复读，所以读锁在事务中持有可以保证不出现不可重复读，写的时候必须加锁且持有，这是必须的了，不然就会出现脏读。Repeatable Read（可重复读）也是MySql的默认事务隔离级别，上面的意思是读的时候需要加锁并且保持。
在数据库系统对REPEATABLE READ的说明中，禁止脏读和不可重复读，但可能会出现幻读。InnoDB存储引擎与标准SQL有所不同，使用Next-Key 和 Gap-Key 锁的算法，避免幻读的产生。简单来说，可以分为以下几方面：

  • 对于普通的(非锁定的)SELECT语句，不会加锁（S锁也不加），而是读取数据的快照（read the snapshot）
  • 对于锁定读取(选择with For UPDATE或For SHARE)、UPDATE和DELETE语句，锁定取决于该语句是使用具有唯一搜索条件的惟一索引，还是使用范围类型搜索条件。
    • 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不锁定它之前的空白。
    • 对于其他搜索条件，InnoDB锁定扫描的索引范围，使用gap-lock锁或next-key锁来阻止其他会话插入到范围覆盖的间隙中。

（4）可串行化（Serializable）
解决幻读问题，在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读是由于并发事务增加记录导致的，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
解决上面说的所有事务并发问题，但事务是串行执行的，即一个一个的按照顺序执行，不能并发执行。

九、MySQL 事务特性

事务是由一组SQL语句组成的逻辑处理单元，事务具有ACID属性。
（1）原子性（Atomicity）：事务是一个原子操作单元。在当时原子是不可分割的最小元素，其对数据的修改，要么全部成功，要么全部都不成功。
（2）一致性（Consistent）：事务开始到结束的时间段内，数据都必须保持一致状态。
（3）隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的”独立”环境执行。
（4）持久性（Durable）：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

十、参考博文

（1） https://blog.csdn.net/AntdonYu/java/article/details/82496349
（2） https://zhidao.baidu.com/question/427336027.html （较为清楚）
（3） https://www.cnblogs.com/djs19/p/11507024.html （基础概念，有图比较直观）
（4） https://www.cnblogs.com/wzxblog/p/5731799.html
（5） https://blog.csdn.net/user2041/article/details/80766135
（6） https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%AD%BB%E9%94%81/10015665?fr=aladdin
（7）https://zhuanlan.zhihu.com/p/63912473 （锁，备份等）
（8） https://blog.csdn.net/xxy55895/java/article/details/105715458
（9） https://blog.csdn.net/a1920135141/article/details/100915289/
（10） https://blog.csdn.net/C_J33/java/article/details/79487941
（11） https://blog.csdn.net/u014427391/java/article/details/78951730
（12） https://www.cnblogs.com/panxuejun/p/5924252.html （案例解释很仔细）
（13）https://mingshan.fun/2019/09/01/transaction-running/ （事务锁等待解决方法）

posted @ 2020-05-07 19:28  jason小蜗牛  阅读(1060)  评论(0)  编辑  收藏  举报