SQL Server 中的 ACID 属性

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 属性。