SQLserver中的事务以及数据并发的问题和事务的四种隔离级别

SQLserver中的事务

在 SQL Server 中，事务是一组原子性的 SQL 语句集合，要么全部成功执行，要么全部不执行。事务确保数据库的完整性和一致性，即使在发生错误或系统故障的情况下也是如此。SQL Server 支持本地事务和分布式事务。

事务的特性（ACID属性）

1. 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成。

2. 一致性（Consistency）：事务必须确保数据库从一个一致的状态转移到另一个一致的状态。

3. 隔离性（Isolation）：并发执行的事务之间相互隔离，每个事务都感觉不到其他事务的存在。

4. 持久性（Durability）：一旦事务提交，它对数据库的改变就是永久性的，即使系统发生故障也不会丢失。

事务的使用

在 SQL Server 中，可以使用 BEGIN TRANSACTION、COMMIT TRANSACTION 和 ROLLBACK TRANSACTION 来控制事务。

1. 显式事务

显式事务是由用户明确开始和结束的事务。

-- 开始事务
BEGIN TRANSACTION;
​
-- 执行一系列 SQL 语句
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
​
-- 提交事务
COMMIT TRANSACTION;

如果需要回滚事务，可以使用 ROLLBACK TRANSACTION：

-- 回滚事务
ROLLBACK TRANSACTION;

2. 隐式事务

隐式事务是由 SQL Server 自动管理的事务，通常在每个单独的 SQL 语句后自动提交。

-- 隐式事务示例
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;

事务隔离级别

事务隔离级别控制事务在并发环境下如何相互隔离。SQL Server 支持以下隔离级别：

1. READ UNCOMMITTED：允许读取未提交的数据。

2. READ COMMITTED：保证读取的数据是已经提交的。

3. REPEATABLE READ：保证在事务期间，已读取的数据不会被其他事务修改。

4. SERIALIZABLE：提供最严格的隔离级别，事务串行执行，不允许其他事务并发执行。

可以通过 SET TRANSACTION ISOLATION LEVEL 语句设置事务的隔离级别：

-- 设置事务隔离级别为 READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

事务日志

SQL Server 使用事务日志来记录事务对数据库所做的更改。在事务提交时，日志记录会被标记为已提交。如果事务失败或回滚，相关的日志记录会被撤销。

死锁

死锁是两个或多个事务相互等待对方释放资源的情况。SQL Server 通常能够检测到死锁并自动解决，但有时可能需要手动干预。

示例：使用事务处理错误

以下示例展示了如何在事务中处理错误：

BEGIN TRY
    BEGIN TRANSACTION;
​
    -- 执行一系列 SQL 语句
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
​
    -- 提交事务
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- 回滚事务
    ROLLBACK TRANSACTION;
    -- 处理错误
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;

注意事项

• 事务应该尽可能短，以减少锁定资源的时间。

• 合理选择事务隔离级别，以平衡性能和数据一致性。

• 监控和优化事务日志，以避免日志文件过大导致性能问题。

数据并发的问题

数据并发是指多个用户或进程同时访问和修改数据库中的数据。在多用户环境中，数据并发是常见的情况，它带来了一些挑战，主要包括数据一致性、性能和资源竞争等问题。以下是数据并发可能引起的一些主要问题：

1. 脏读（Dirty Read）：当一个事务读取到另一个未提交事务修改的数据时，如果那个事务最终回滚，那么第一个事务读取到的数据就是无效的。

2. 不可重复读（Non-Repeatable Read）：在一个事务中，多次读取同一数据集合时，由于其他事务的介入导致读取到的数据不一致。例如，一个事务读取到一条记录，然后另一个事务修改了这条记录并提交，导致第一个事务再次读取时数据发生了变化。

3. 幻读（Phantom Read）：与不可重复读类似，但涉及到插入或删除操作。一个事务在读取某个范围的记录时，另一个事务插入或删除了一些记录，导致第一个事务再次读取时，似乎看到了“幻影”记录。

4. 丢失更新（Lost Update）：当两个或多个事务同时更新同一数据项时，一个事务的更新可能会覆盖另一个事务的更新，导致数据不一致。

5. 死锁（Deadlock）：两个或多个事务相互等待对方持有的资源，导致所有事务都无法继续执行。

6. 更新丢失（Update Lost）：当一个事务读取数据，然后另一个事务更新了这些数据并提交，第一个事务再次读取这些数据时，可能会丢失这些更新。

7. 资源竞争（Resource Contention）：多个事务同时请求同一资源，可能导致性能瓶颈，特别是在高并发环境下。

为了解决这些问题，数据库系统通常采用以下策略：

• 锁机制（Locking）：通过行级锁、表级锁或其他锁类型来控制对数据的访问，以防止并发事务间的冲突。

• 事务隔离级别（Transaction Isolation Levels）：数据库提供了不同的隔离级别，如 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE，以控制事务间的可见性和并发性。

• 乐观并发控制（Optimistic Concurrency Control）：在事务提交时检查是否违反了一致性约束，而不是在事务执行时锁定资源。

• 悲观并发控制（Pessimistic Concurrency Control）：在事务开始时就锁定资源，以确保事务的一致性。

• 死锁检测和解决策略：数据库系统通常内置了死锁检测机制，能够在检测到死锁时自动回滚其中一个事务。

• 索引和查询优化：通过优化索引和查询来减少锁的竞争，提高并发性能。

事务回滚

BEGIN TRY
BEGIN TRAN 
UPDATE STOCK SET STONAME='李逵' WHERE STOCKID=1
UPDATE STOCK SET STONAME='宋江' WHERE STOCKID=2
UPDATE STOCK SET STONAME='宋江' WHERE STOCKID=-1
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT '出现异常';
END CATCH

 

事务的四种隔离级别

事务的隔离级别是数据库管理系统用来控制并发事务之间相互影响的程度。SQL Server 支持四种标准的事务隔离级别，它们分别是：

1. READ UNCOMMITTED（读未提交）：

   • 在这个隔离级别下，事务可以读取到其他事务未提交的数据。这意味着脏读、不可重复读和幻读都是可能发生的。

   • 这种隔离级别提供了最低的隔离，因此它允许最大的并发性，但同时数据的一致性和准确性风险最高。

2. READ COMMITTED（读已提交）：

   • 事务只能读取到其他事务已经提交的数据。这是 SQL Server 的默认隔离级别。

   • 这种隔离级别防止了脏读，但仍然允许不可重复读和幻读。

3. REPEATABLE READ（可重复读）：

   • 在这个隔离级别下，事务在整个事务期间可以多次读取到相同的数据集合，即使其他事务修改了这些数据，也不能提交。这防止了脏读和不可重复读。

   • 但是，幻读仍然可能发生，因为其他事务可以插入新的行。

4. SERIALIZABLE（可串行化）：

   • 这是最高的隔离级别，它通过完全锁定涉及的数据来防止脏读、不可重复读和幻读。

   • 在这个级别下，事务会以一种顺序执行，就像它们是串行的一样，从而确保了最高的数据一致性，但会牺牲并发性能。

如何设置隔离级别

在 SQL Server 中，可以通过以下 SQL 语句来设置事务的隔离级别：

SET TRANSACTION ISOLATION LEVEL [隔离级别名称];

例如，要将隔离级别设置为 READ COMMITTED，可以使用：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

选择隔离级别的考虑因素

选择适当的隔离级别需要在数据一致性和并发性能之间做出权衡。较低的隔离级别（如 READ UNCOMMITTED）可能会提高并发性能，但同时增加了数据不一致的风险。较高的隔离级别（如 SERIALIZABLE）可以提供更强的数据一致性保证，但可能会降低并发性能。