关系数据库中的事务——SqlServer为例说明

一、关系数据库的事务简介

 1.1、事务简介

        简单的说事务是一个或多个SQL语句被看做是一个整体单元，要么都执行成功，要么只要有一个失败就全部失败【即事务确保一个事务内的所有语句都成功或都失败，不允许部分完成】。

 1.2、事务的ACID特征

事务一般应用于联机事务处理 (OLTP)场景中：且事务具备ACID特征

 1.3、关系数据库的OLTP与OLAP两种使用场景       

        关系数据库使用场景一般分为【联机事务处理（OLTP）】【联机分析处理（OLAP）】：

事务使用示例

二、事务的使用示例 

 2.1、事务的创建和异常回滚处理

1、事务的创建

--1、显式启动事务可使用全称【BEGIN TRANSACTION】或简称【BEGIN TRAN】
BEGIN TRANSACTION;--2、需要执行的多条sql语句--3、事务启动后，必须以提交【COMMIT TRANSACTION】或回滚结【ROLLBACK TRANSACTION】结束
COMMIT TRANSACTION;
ROLLBACK TRANSACTION;

2、事务的异常捕获回滚处理 

BEGIN TRYBEGIN TRANSACTION;--需执行的多条sql语句COMMIT TRANSACTION;
END TRY
BEGIN CATCH
--捕获异常并回滚事务SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;ROLLBACK TRANSACTION;
END CATCH;

可以在 ROLLBACK 命令之前使用XACT_STATE【用来检查事务是否处于活动状态】

--实现事务只有在存在活动用户事务时才会被回滚
BEGIN TRYBEGIN TRANSACTION;--需要执行的多条sql语句COMMIT TRANSACTION;
END TRY
BEGIN CATCHSELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;IF (XACT_STATE()) <> 0BEGINROLLBACK TRANSACTION;END;END CATCH;

注意：

1、事务启动后必须进行在最后进行提交，如果连接在事务中途断开，整个事务将回滚 ；

2、事务是可以嵌套的，在这种情况下，如果外部事务回滚，内部事务将回滚；

3、如果事务中发生错误，请使用回滚命令（回滚命令则撤消在事务期间对数据所做的任何修改，使其处于事务启动前的状态）；

4、存储过程中当 SET XACT_ABORT 为 ON 时，如果 SQL Server 引发错误，则会回滚整个事务。 当 SET XACT_ABORT 为 OFF 时，如果错误的严重性较低，则只回滚引发错误的语句；

5、由于并不总是清楚事务是提交还是回滚，因此向事务添加错误处理至关重要。

 2.2、执行sql是否事务示例

--创建订单表
CREATE TABLE Test.dbo.Orders (custid int NOT NULL,empid int NOT NULL,orderdate datetime NOT NULL
);--创建订单详情表
CREATE TABLE Test.dbo.OrderDetails (orderid int NOT NULL,productid int NOT NULL,unitprice money NOT NULL,qty int NOT NULL
);

--模拟程序使用sql语句批量插入数据（故意给第二条数据的值设置错误）
BEGIN TRYINSERT INTO dbo.Orders(custid, empid, orderdate) VALUES (66, 6,  GETDATE());INSERT INTO dbo.Orders(custid, empid, orderdate) VALUES (77, 'test',  GETDATE());INSERT INTO dbo.OrderDetails(orderid,productid,unitprice,qty) VALUES (6, 666, 16.66, 66);INSERT INTO dbo.OrderDetails(orderid,productid,unitprice,qty) VALUES (7, 777, 27.77, 77);
END TRY
BEGIN CATCHSELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;
END CATCH;

   如上《模拟程序使用sql语句批量插入数据（故意给第二条数据的值设置错误）》我们在批量执行多条sql语句插入数据的时候，只要不是第一条sql报错，那么第一条数据的内容就会被插入到表中，但是当后面的第二条sql报错后，第二条的数据是没有插入到表中，但是第一条插入的数据也并没有被回滚，导致数据库处于不一致的状态（即：该结果我们期望的要么数据都同时插入到表中，要么都不插入到表中的期望不符)； 

--模拟程序使用事务执行sql插入语句（这里也故意给第二天数据的值设置错误[执行后数据都没有插入到表中符合预期]）
BEGIN TRYBEGIN TRANSACTION;INSERT INTO dbo.Orders(custid, empid, orderdate) VALUES (66, 6,  GETDATE());INSERT INTO dbo.Orders(custid, empid, orderdate) VALUES (77, 'test',  GETDATE());INSERT INTO dbo.OrderDetails(orderid,productid,unitprice,qty) VALUES (6, 666, 16.66, 66);INSERT INTO dbo.OrderDetails(orderid,productid,unitprice,qty) VALUES (7, 777, 27.77, 77);COMMIT TRANSACTION;
END TRY
BEGIN CATCHSELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;ROLLBACK TRANSACTION;
END CATCH;--模拟程序使用事务执行sql插入语句【修改为正常的内容后就可以全部插入表中也是符合预期】
BEGIN TRYBEGIN TRANSACTION;INSERT INTO dbo.Orders(custid, empid, orderdate) VALUES (66, 6,  GETDATE());INSERT INTO dbo.Orders(custid, empid, orderdate) VALUES (77, 7,  GETDATE());INSERT INTO dbo.OrderDetails(orderid,productid,unitprice,qty) VALUES (6, 666, 16.66, 66);INSERT INTO dbo.OrderDetails(orderid,productid,unitprice,qty) VALUES (7, 777, 27.77, 77);COMMIT TRANSACTION;
END TRY
BEGIN CATCHSELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;ROLLBACK TRANSACTION;
END CATCH;

        如上《模拟程序使用事务执行sql插入语句》我们故意给第二条数据的值设置错误时则没有一条数据插入到表中符合预期；接下来我们使用都正确的事务sql语句时可以正常将数据插入表中也也符合预期；这样数据库就能永远处于一致的状态了。

 2.3、并发

        多用户数据库的核心功能是并发。 并发通过使用锁定和阻塞来使数据在许多用户同时更新和读取时保持一致。

        注意：并发是在事务级别上发生的。 写入事务可能会阻止其他事务更新，甚至读取相同的数据。 同样，读取事务可以阻止其他读者，甚至是某些写入程序。 因此，必须避免不必要冗长的事务或涉及过多数据的事务。

关于锁的详细内容可以查看：事务锁定和行版本控制指南 - SQL Server | Microsoft Learn

关于阻塞的详细内容可查看：了解并解决阻止问题 - SQL Server | Microsoft Learn

数据库引擎中的隔离级别 | Microsoft Learnhttps://learn.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms189122(v=sql.105)

Snapshot Isolation in SQL Server（SQL Server 中的快照隔离） - ADO.NET | Microsoft Learnhttps://learn.microsoft.com/zh-cn/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server SQL Server 中有五个不同的隔离级别，如上图所示：

--启用读取已提交快照【则使用默认的读取已提交隔离级别的任何事务都将使用乐观锁定】
ALTER DATABASE *db_name* SET READ_COMMITTED_SNAPSHOT ON;--关闭读取已提交快照【则使用的读取已提交隔离级别的任何事务都将使用悲观锁定】
ALTER DATABASE *db_name* SET READ_COMMITTED_SNAPSHOT OFF;

三、其他资料

使用 Transact-SQL 实现事务 - Training | Microsoft Learnhttps://learn.microsoft.com/zh-cn/training/modules/implement-transactions-transact-sql/?source=recommendations事务处理 - .NET Framework | Microsoft Learnhttps://learn.microsoft.com/zh-cn/dotnet/framework/data/transactions/CLR 集成和事务 - SQL Server | Microsoft Learnhttps://learn.microsoft.com/zh-cn/sql/relational-databases/clr-integration-data-access-transactions/clr-integration-and-transactions?view=sql-server-ver17使用 Transact SQL 实现事务 | dp-080-transact-SQLhttps://microsoftlearning.github.io/dp-080-Transact-SQL/Instructions/Labs/13-implement-transitions-in-tsql.html浏览所有课程、学习路径和模块 - Training | Microsoft Learnhttps://learn.microsoft.com/zh-cn/training/browse/?products=azure-sql-database%2Cazure-sql-managed-instance%2Cazure-sql-virtual-machines%2Cazure-sqlserver-vm%2Csql-server&levels=advanced&roles=data-analyst%2Cdata-engineer%2Cdata-scientist%2Cdatabase-administrator%2Cdeveloper%2Csolution-architect%2CstudentSQL - 事务：初学者指南 - 高级SQL - W3schoolshttps://w3schools.tech/zh-cn/tutorial/sql/sql-transactions

事务、事务的四大特性(ACID)、三大并发问题、四种锁、事务的隔离级别https://www.cnblogs.com/jimoyu/p/12251282.html深入学习MySQL事务：ACID特性的实现原理 - 编程迷思 - 博客园https://www.cnblogs.com/kismetv/p/10331633.htmlsql server中的事务模式（隐式事务，显式事务，自动提交事务）https://www.cnblogs.com/gered/p/10437395.html

数据库历史数据如何处理 https://docs.pingcode.com/baike/2077206