sqlserver 事务和锁详解

淩亂°似流年 2023-07-10 14:19 15阅读 0赞

### *事务的概念* ###

事务( transacton)是构成单一逻辑工作单元的操作集合，它是一个SQL语句序列，通常，每个 INSERT

UPDATE、 DELETE命令被作为一个事务处理，事务开始之后，所有的操作都陆续写到事务日志中，因此事务处理确保了数据的一致性和可恢复性

当进行事务操作时，系统自动生成一个检查点机制，检査点周期地楹査事务日志，如果在事务日志中事务全部完成，那么检査点将事务日志中的该事务提交到数据库中，并且在事务日志中做一个检查点提交标记。如果没有完成，那么检査点不将事务日志中的该事务提交到数据库中，并且在事务日志中做一个检查点未提交标记。

### *事务的基本属性* ###

事务具有下列基本属性(ACID):

原子性( Atomicity):事务是一个不可分割的工作单元，要么都执行，要麽都不执行

**   1.案例**

A给B转帐100元钱

![Center][]

>        
> 
> `begin transaction`
> 
> `update account set money= money - 100where name='A';`
> 
> `update account set money= money +100where name='B';`
> 
> `if Error then`
> 
> `rollback`
> 
> `else`
> 
> `commit`

**  2.分析 **

在事务中的扣款和加款两条语句，要么都执行，要么就都不执行。否则如果只执行了扣款语句，就提交了，此时如果突然断电，A账号已经发生了扣款，B账号却没收到加款，在生活中就会引起纠纷。

** 3.解决方法**

在数据库管理系统（DBMS）中，默认情况下一条SQL就是一个单独事务，事务是自动提交的。只有显式的使用start transaction开启一个事务，才能将一个代码块放在事务中执行。保障事务的原子性是数据库管理系统的责任，为此许多数据源采用日志机制。例如，SQL Server使用一个预写事务日志，在将数据提交到实际数据页面前，先写在事务日志上。

一致性( Consistency):即数据不会因事务的执行而遭受破环,j就是说不能破坏关系数据的完整性以及业务逻辑上的一致性。

**1.案例**

对银行转帐事务，不管事务成功还是失败，应该保证事务结束后ACCOUNT表中aaa和bbb的存款总额为2000元。

隔离性( Isolation):在并发处理过程中，事务的先后单独执行结果不随多个事务并发执行的情况而改变

持久性( Durability):意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中。

### *事务的分类* ###

根据系统的设置，可以把事务分成两种类型。一种是系统提供的事务，另一种是用户定义的事务。系统提供的事务是指在执行某些语句时，一条语句就是一个事务。

### *事物的并发控制产生的问题* ###

1. **脏读 **：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。  
 e.g.  
        1.Mary的原工资为1000, 财务人员将Mary的工资改为了8000(但未提交事务)  
        2.Mary读取自己的工资 ,发现自己的工资变为了8000，欢天喜地！  
        3.而财务发现操作有误，回滚了事务,Mary的工资又变为了1000  
          像这样,Mary记取的工资数8000是一个脏数据。

2. **不可重复读 **：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。  
    e.g.  
    1.在事务1中，Mary 读取了自己的工资为1000,操作并没有完成  
    2.在事务2中，这时财务人员修改了Mary的工资为2000,并提交了事务.  
    3.在事务1中，Mary 再次读取自己的工资时，工资变为了2000

解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。

3. **幻读** : 是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。  
   e.g.   
   目前工资为1000的员工有10人。  
   1.事务1,读取所有工资为1000的员工。  
   2.这时事务2向employee表插入了一条员工记录，工资也为1000  
   3.事务1再次读取所有工资为1000的员工 共读取到了11条记录，

三者的场景介绍完,但是一定仍然有很多同学搞不清楚,它们到底有什么区别，我总结一下。

**脏读**：指读到了其他事务未提交的数据。

**不可重复读**：读到了其他事务已提交的数据(update)。

不可重复读与幻读都是读到其他事务已提交的数据,但是它们针对点不同。

不可重复读：update。

幻读：delete，insert。

### *事务的四种隔离级别* ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMzQ5NTQ_size_16_color_FFFFFF_t_70][]

### *SQLSERVER和ORACLE 事务比较 * ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMzQ5NTQ_size_16_color_FFFFFF_t_70 1][]

### *锁的分类* ###

1.按锁定的对象来分

有表锁、行锁、数据库锁等，表锁定是对整个表进行锁定，行锁定是对表中特定行进行锁定，数据库锁是锁定整个数据库。

2.从数据库系统角度分为三种：

排他锁、共享锁、更新锁。

3.从程序员角度分为两种：

一种是悲观锁，一种乐观锁

### 悲观锁（Pessimistic Lock） ###

顾名思义，很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人拿这个数据就会block（阻塞），直到它解锁。

> 悲观锁（Pessimistic Lock）：正如其名，具有强烈的独占和排他特性。它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

传统的关系数据库里用到了很多这种锁机制，比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁。

悲观锁按使用性质划分

共享锁（Share Lock）

S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。  
**性质**  
1. 多个事务可封锁同一个共享页；  
2. 任何事务都不能修改该页；  
3. 通常是该页被读取完毕，S锁立即被释放。

> 在SQL Server中，默认情况下，数据被读取后，立即释放共享锁。  
> 例如，执行查询语句“SELECT \* FROM my\_table”时，首先锁定第一页，读取之后，释放对第一页的锁定，然后锁定第二页。这样，就允许在读操作过程中，修改未被锁定的第一页。  
> 例如，语句“SELECT \* FROM my\_table HOLDLOCK”就要求在整个查询过程中，保持对表的锁定，直到查询完成才释放锁定。

排他锁（Exclusive Lock）

X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。（某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面打开了。）  
**性质**  
1. 仅允许一个事务封锁此页；  
2. 其他任何事务必须等到X锁被释放才能对该页进行访问；  
3. X锁一直到事务结束才能被释放。

> 产生排他锁的SQL语句如下：select \* from ad\_plan for update;

更新锁

U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。

> 因为当使用共享锁时，修改数据的操作分为两步：  
> 1. 首先获得一个共享锁，读取数据，  
> 2. 然后将共享锁升级为排他锁，再执行修改操作。  
> 这样如果有两个或多个事务同时对一个事务申请了共享锁，在修改数据时，这些事务都要将共享锁升级为排他锁。这时，这些事务都不会释放共享锁，而是一直等待对方释放，这样就造成了死锁。  
> 如果一个数据在修改前直接申请更新锁，在数据修改时再升级为排他锁，就可以避免死锁。

**性质**  
1. 用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；  
2. 当被读取的页要被更新时，则升级为X锁；  
3. U锁判断不符合条件就释放，符合就转为X锁。

悲观锁按作用范围划分为：行锁、表锁。（这部分摘自[https://www.jianshu.com/p/eb41df600775][https_www.jianshu.com_p_eb41df600775] ）

行锁

锁的作用范围是行级别。

表锁

锁的作用范围是整张表。

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

> 举个例子，一个用户表user，有主键id和用户生日birthday。  
> 当你使用update … where id=?这样的语句时，数据库明确知道会影响哪一行，它就会使用行锁；  
> 当你使用update … where birthday=?这样的的语句时，因为事先不知道会影响哪些行就可能会使用表锁。

### 乐观锁（Optimistic Lock） ###

顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以，不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号等机制。

> 乐观锁（ Optimistic Locking ）： 相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。  
> **悲观锁**大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。  
> **乐观锁**，大多是基于数据版本（ Version ）记录机制实现。  
> **数据版本**：为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write\_condition机制的其实都是提供的乐观锁。

乐观锁的实现方式（这部分摘自[https://www.jianshu.com/p/eb41df600775][https_www.jianshu.com_p_eb41df600775] ）

*  版本号（version）

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

*  时间戳（使用数据库服务器的时间戳）
    
    > **时间戳（timestamp）**：和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。
 *  待更新字段
    
    > **待更新字段**：和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），如果是就把我修改的count的值更新到该字段，否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。
 *  所有字段
    
    > **所有字段**：和待更新字段类似，只是使用所有字段做版本控制信息，只有所有字段都没变化才会执行更新。

[Center]: https://img-blog.csdn.net/20131105115813406?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvc2h1YWloag==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMzQ5NTQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200303151738134.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMzQ5NTQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMzQ5NTQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200303151928477.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMzQ5NTQ=,size_16,color_FFFFFF,t_70
[https_www.jianshu.com_p_eb41df600775]: https://www.jianshu.com/p/eb41df600775