马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
x
SQL Server 中的事务是什么？
SQL Server 中的事务是一组被视为一个单元的 SQL 语句，它们按照“做所有事或不做任何事”的原则执行，成功的事务必须通过 ACID 测试。
事务的 ACID 属性是什么？
首字母缩写词 ACID 是指事务的四个关键属性
- 原子性: Atomicity
- 一致性: Consistency
- 隔离性: Isolation
- 持久性: Durability
为了理解这一点，我们将使用以下两个表测试。
Product (产品表)
ProductIDNamePriceQuantity101Laptop15000100102Desktop20000150104Mobile3000200105Tablet4000250ProductSales (产品销售表)
ProductSalesIDProductIDQuantitySold110110210215310430410535请使用以下 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 中事务的原子性确保事务中的所有 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 中事务的一致性确保数据库数据在事务开始之前处于一致状态，并且在事务完成后也使数据保持一致状态。如果事务违反规则，则应回滚。例如，如果可用库存从 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
- select * from dbo.Product where ProductID = 101
解决阻塞: 切换到第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 属性。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
