详解SQL Server 中的 ACID 属性

目录
  • SQL Server 中的事务是什么?
  • 事务的 ACID 属性是什么?
  • SQL Server 中事务的原子性
  • SQL Server 中事务的一致性
  • SQL Server 中事务的隔离性
  • SQL Server 中事务的持久性

SQL Server 中的事务是什么?

SQL Server 中的事务是一组被视为一个单元的 SQL 语句,它们按照“做所有事或不做任何事”的原则执行,成功的事务必须通过 ACID 测试。

事务的 ACID 属性是什么?

首字母缩写词 ACID 是指事务的四个关键属性

  • 原子性: Atomicity
  • 一致性: Consistency
  • 隔离性: Isolation
  • 持久性: Durability

为了理解这一点,我们将使用以下两个表测试。

Product (产品表)

ProductID Name Price Quantity
101 Laptop 15000 100
102 Desktop 20000 150
104 Mobile 3000 200
105 Tablet 4000 250

ProductSales (产品销售表)

ProductSalesID ProductID QuantitySold
1 101 10
2 102 15
3 104 30
4 105 35

请使用以下 SQL 脚本创建并使用示例数据填充 Product 和 ProductSales 表。

IF OBJECT_ID('dbo.Product','U') IS NOT NULL
    DROP TABLE dbo.Product
IF OBJECT_ID('dbo.ProductSales','U') IS NOT NULL
    DROP TABLE dbo.ProductSales
GO
CREATE TABLE Product
(
  ProductID INT PRIMARY KEY,
  Name VARCHAR(40),
  Price INT,
  Quantity INT
 )
GO
INSERT INTO Product VALUES(101, 'Laptop', 15000, 100)
INSERT INTO Product VALUES(102, 'Desktop', 20000, 150)
INSERT INTO Product VALUES(103, 'Mobile', 3000, 200)
INSERT INTO Product VALUES(104, 'Tablet', 4000, 250)
GO
CREATE TABLE ProductSales
(
  ProductSalesId INT PRIMARY KEY,
  ProductId INT,
  QuantitySold INT
)
GO
INSERT INTO ProductSales VALUES(1, 101, 10)
INSERT INTO ProductSales VALUES(2, 102, 15)
INSERT INTO ProductSales VALUES(3, 103, 30)
INSERT INTO ProductSales VALUES(4, 104, 35)
GO

SQL Server 中事务的原子性

SQL Server 中事务的原子性确保事务中的所有 DML 语句(即插入、更新、删除)成功完成或全部回滚。例如,在以下 spSellProduct 存储过程中,UPDATE 和 INSERT 语句都应该成功。如果 UPDATE 语句成功而 INSERT 语句失败,数据库应该通过回滚来撤消 UPDATE 语句所做的更改。

IF OBJECT_ID('spSellProduct','P') IS NOT NULL
    DROP PROCEDURE spSellProduct
GO
CREATE PROCEDURE spSellProduct
@ProductID INT,
@QuantityToSell INT
AS
BEGIN

  -- 首先我们需要检查待销售产品的可用库存
  DECLARE @StockAvailable INT
  SELECT @StockAvailable = Quantity FROM Product WHERE ProductId = @ProductId

  --如果可用库存小于要销售的数量,抛出错误
  IF(@StockAvailable < @QuantityToSell)
  BEGIN
    Raiserror('可用库存不足',16,1)
  END

  -- 如果可用库存充足
  ELSE
  BEGIN
    BEGIN TRY
      -- 我们需要开启一个事务
      BEGIN TRANSACTION

      -- 首先做减库存操作
      UPDATE Product SET Quantity = (Quantity - @QuantityToSell) WHERE ProductID = @ProductID

      -- 计算当前最大的产品销售ID,即 MaxProductSalesId
      DECLARE @MaxProductSalesId INT
      SELECT @MaxProductSalesId = CASE
          WHEN MAX(ProductSalesId) IS NULL THEN 0
          ELSE MAX(ProductSalesId)
          END
      FROM ProductSales

      -- 把 @MaxProductSalesId 加一, 所以我们会避免主键冲突
      --(解释下,建表的时候,没有设置主键自增,所以需要人工处理自增)
      Set @MaxProductSalesId = @MaxProductSalesId + 1

      -- 把销售的产品数量记录到ProductSales表中
      INSERT INTO ProductSales VALUES (@MaxProductSalesId, @ProductId, @QuantityToSell)

      -- 最后,提交事务
      COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
      -- 如果发生了异常,回滚事务
      ROLLBACK TRANSACTION
    END CATCH

  End
END

SQL Server 中事务的一致性

SQL Server 中事务的一致性确保数据库数据在事务开始之前处于一致状态,并且在事务完成后也使数据保持一致状态。如果事务违反规则,则应回滚。例如,如果可用库存从 Product 表中减少,那么 ProductSales 表中必须有一个相关条目。

在我们的示例中,假设事务更新了 product 表中的可用数量,突然出现系统故障(就在插入 ProductSales 表之前或中间)。在这种情况下系统会回滚更新,否则我们无法追踪库存信息。

SQL Server 中事务的隔离性

SQL Server 中事务的隔离性确保事务的中间状态对其他事务不可见。一个事务所做的数据修改必须与所有其他事务所做的数据修改隔离。大多数数据库使用锁定来维护事务隔离。

为了理解事务的隔离性,我们将使用两个独立的 SQL Server 事务。从第一个事务开始,我们启动了事务并更新了 Product 表中的记录,但我们还没有提交或回滚事务。在第二个事务中,我们使用 select 语句来选择 Product 表中存在的记录,如下所示。

在sqlserver management studio 或 Navicat 中新建两个独立的查询窗口

首先在第1个窗口运行以下事务,更新库存(注意事务没有提交或回滚,回滚语句被注释了)

begin tran
update dbo.Product set Quantity = 150 where ProductID = 101
--rollback tran

然后在第2个窗口运行以下语句,查询被更新的产品

select * from dbo.Product where ProductID = 101

你会发现,第2个窗口中的查询语句被阻塞了(一直处于运行状态,没有返回数据)

解决阻塞: 切换到第1个窗口, (按下鼠标左键拖动选择 rollback tran ,注意不包含注释 -- ),
然后单独执行这个语句, 在 sqlserver management studio 直接点击执行就行, 在 Navicat 中,点击运行按钮右边的下拉箭头,点击运行已选择的,好了,再切换到第2个窗口,你会发现结果出来了

阻塞的原因: SqlServer默认的事务隔离级别是 Read Committed,
在上述的Update语句执行时会在对应的数据行上加一个 排它锁(X), 直到事务提交或者回滚才会释放,这保证了在此期间,其他任何事务都不能操作此行数据(查询也不行),因为排它锁(也叫独占锁),和其他类型的锁都是不兼容的,这保证了其他事务看不到另一个事务的中间状态,即避免了脏读

SQL Server 中事务的持久性

SQL Server 中事务的持久性确保一旦事务成功完成,它对数据库所做的更改将是永久性的。即使出现系统故障或电源故障或任何异常变化,它也应该保护已提交的数据。

注意:首字母缩写词 ACID 由 Andreas Reuter 和 Theo Härder 在 1983 年创建,然而,Jim Gray 在 1970 年代后期已经定义了这些属性。大多数流行的数据库,如 SQL Server、Oracle、MySQL、Postgre SQL 默认都遵循 ACID 属性。

到此这篇关于SQL Server 中的 ACID 属性的文章就介绍到这了,更多相关SQL Server ACID内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • SQL Server 存储过程遇到“表 ''#TT'' 没有标识属性无法执行 SET 操作”错误

    创建临时表,往临时表插入数据的时候报的错误. 一开始提示没有打开主键,后来打开主键就提示上述错误异常. 从网上查找资料没有找到,然后又到群里问各位大牛,一位大牛告诉我是没有设置主键. 我又仔细看看提示,恍然大悟,我就给临时设置了主键. 没有主键之前的代码: CREATE TABLE #TT( CourseId INT , UserId INT, ClassId INT ) INSERT INTO #TT ( CourseId, UserId, ClassId ) SELECT CourseId,

  • SQL Server重置IDENTITY属性种子值操作

    SQL Server重置IDENTITY属性种子值 -- IDENTITY重置种子 DBCC CHECKIDENT(表名, RESEED, 0) 补充:sql server 修改IDENTITY 字段 的统计当前值 一个表数据是从另一服务器同步过来的.现在数据已超过了 3514万条,但插入值时,提示不能插入重复键值 320. 查看表的统计时间,看到是N年前统计的. 手工更新表统计信息: UPDATE STATISTICS TABLE_NAME, 再插入,还是无效. 后使用代码: DBCC CHE

  • SqlServer 扩展属性的介绍

    SqlServer帮助中对扩展属性的描述是: The Extended Properties property sets or retrieves provider-specific connection information that cannot be explicitly described through the property mechanism. 对于扩展属性有如下操作: 复制代码 代码如下: exec sp_addextendedproperty N'MS_Descriptio

  • 获取SQL Server表字段的各种属性实例代码

    复制代码 代码如下: -- SQL Server 2000 SELECT a.name AS 字段名, CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN (SELECT name FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys WHERE id = a.id AND colid

  • 详解SQL Server 中的 ACID 属性

    目录 SQL Server 中的事务是什么? 事务的 ACID 属性是什么? SQL Server 中事务的原子性 SQL Server 中事务的一致性 SQL Server 中事务的隔离性 SQL Server 中事务的持久性 SQL Server 中的事务是什么? SQL Server 中的事务是一组被视为一个单元的 SQL 语句,它们按照“做所有事或不做任何事”的原则执行,成功的事务必须通过 ACID 测试. 事务的 ACID 属性是什么? 首字母缩写词 ACID 是指事务的四个关键属性

  • 详解SQL Server中的事务与锁问题

    一  概述 在数据库方面,对于非DBA的程序员来说,事务与锁是一大难点,针对该难点,本篇文章试图采用图文的方式来与大家一起探讨. "浅谈SQL Server 事务与锁"这个专题共分两篇,上篇主讲事务及事务一致性问题,并简略的提及一下锁的种类和锁的控制级别. 下篇主讲SQL Server中的锁机制,锁控制级别和死锁的若干问题. 二   事务 1   何为事务 预览众多书籍,对于事务的定义,不同文献不同作者对其虽有细微差别却大致统一,我们将其抽象概括为: 事务:指封装且执行单个或多个操作的

  • 详解SQL Server 中 JSON_MODIFY 的使用

    SQL Server 中 JSON_MODIFY 的使用 Intro SQL Server 从 2016 开始支持了一些 JSON操作,最近的项目里也是好多地方字段直接存成了 JSON,需要了解一下怎么在SQL Server 中操作 JSON. JSON支持适用于 SQL Server 2016 及以上版本 和 Azure SQL Database. 最近在项目中很多地方字段都是存一个 JSON 字符串,尤其是针对一些有很多不确定的属性的数据来说,这样的信息我们一般都放在一个 Extra 之类的

  • 详解SQL Server中的数据类型

    前言 前面几篇文章我们讲解了索引有关知识,这一节我们再继续我们下面内容讲解,简短的内容,深入的理解. 数据类型 SQL Server支持两种字符数据类型,一种是常规,另外一种则是Unicode.常规数据类型包括CHAR和VARCHAR,Unicode数据类型包括NCAHR和NVARCHAR.常规字符的每个字符使用1个字节存储,而Unicode数据的每个字符要求2个字节.常规字符列限制为仅仅只针对于英语,而Unicode则是针对于多种语言.两种字符数据类型的文本表示方式也不相同,在表示常规字符文本

  • 详解SQL Server数据库状态和文件状态

    数据库状态 (database states) 查询数据库的当前状态 : 1.查询所有数据库的状态 ,通过sys.databases目录视图的state_desc列 user master go select state_desc ,[name] from sys.databases go 2.查询指定数据库的状态,通过DATABASEPROPERTYEX函数的Status属性 select DATABASEPROPERTYEX('demoData','status') go 状态: ONLIN

  • 详解SQL Server 2008工具SQL Server Profiler

    一.SQL Profiler工具简介 SQL Profiler是一个图形界面和一组系统存储过程,其作用如下: 1.图形化监视SQL Server查询: 2.在后台收集查询信息: 3.分析性能: 4.诊断像死锁之类的问题: 5.调试T-SQL语句: 6.模拟重放SQL Server活动: 也可以使用SQL Profiler捕捉在SQL Server实例上执行的活动.这样的活动被称为Profiler跟踪. 1.Profiler跟踪 从开始=>所有程序=>Microsoft SQL Server 2

  • 详解SQL Server数据库架构和对象、定义数据完整性

    前言 本节我们继续SQL之旅,本节我们如题来讲讲一些基本知识以及需要注意的地方,若有不妥之处,还望指出,简短的内容,深入的理解. 数据库架构和对象 数据库包含架构,而架构又包含对象,架构可以看做是表.视图.存储过程等对象的容器.架构是一个命名空间,它被用做对象名称的前缀,比如在Cnblogs的架构中有一个名称为Blogs的表,此时我们用架构式限定式名称(即两部分式对象名称)所以Blogs表示为Cnblogs.Blogs.如果我们引用对象时省略了架构名称,SQL Server将会检查对象是否存在用

  • 图文详解SQL Server 2008R2使用教程

    本文为大家分享了SQL Server 2008R2简单使用教程,供大家参考,具体内容如下 1 首先找到开始菜单中相关内容:如下图:安装的组件不同可能有所不同:我的电脑中包括如下项: 商业智能:管理控制台:导入和导出数据:分析服务:集成服务:配置工具:文档和教程:性能工具: 因为偶装的组件多: 2 进入管理控制台 首先是登录:服务器类型选择 数据库引擎:此处先用Windows身份验证登录: 3 进入管理控制台 界面如下:看到 数据库 下有四个子项,系统数据库.数据库快照.ReportServer.

  • 图文详解SQL Server 2008 R2安装配置方法

    安装sql server2008 R2的方法,轻松简单易操作,按照操作一步一步来. 工具: sql server 2008 R2安装包一个(从网上下载) 方法/步骤: 安装包解压 解压后,打开setup.exe文件,选择安装,显示如图: 选择全新安装或向现有安装添加功能 点确定 输入 企业版序列号:R88PF-GMCFT-KM2KR-4R7GB-43K4B 点我接受许可条款,点下一步 点安装,如果操作系统没有安装.net framet3.5 sp1 ,将会自动安装. 必备环境全部通过后,点下一步

  • 详解SQL Server如何修改数据库物理文件的存在位置

    前言 大家应该都知道SQL Server创建新库时,默认会把数据存放在C盘中,一旦数据库中的存储数据多了以后,C盘的空间就会所剩无几.解决方案是将存放数据的物理文件迁移到其他盘. 具体流程为: 1.将现有的数据库脱机 ALTER DATABASE DB1 SET OFFLINE WITH ROLLBACK IMMEDIATE; 2.将数据库文件移到新的位置 文件复制完成以后需要:右键-属性-安全-在组或用户名处添加Authenticated Users-更改该组权限为完全权限,否则接下来的操作会

随机推荐