读书笔记 SQL 事务理解

2024-07-21 02:47:36

字体：大中小

来源：转载

供稿：网友

读书笔记 SQL 事务理解事务的ACID属性

Atomicity 原子性

每个事务作为原子单元工作（即不可以再拆分），也就是说所有数据库变动事务，要么成功要么不成功。

SQL Server把每个DML或者 DDL命令都当做一个事务。不允许任何命令只是部分成功。比如一个UPDATE语句更新500行，除非500行全部更新，否则有任何情况阻止该命令更新。SQL Server会终止该命令更新，并且回滚事务。

Consistency 一致性

每个事务，不论成功或失败，数据库中定义的约束状态必须一致，否则会回滚。

比方说一个事务企图插入一个无效的外键，这会被SQL Server检测到违反约束，并且生成一个错误提示。

Isolation 隔离性

事务的执行看上去是互不干扰的，隔离的程度基于隔离级别设置。比方说，两个事务要更改同一个数据，其中之一必须等另外一个完成以后才能去修改。

SQL Server用‘锁’来达到事务的隔离的目的。通常有两种锁 Shared locks 共享锁 用作读取数据 Exclusive locks 排它锁 用作变更数据

Durability 持久性

事务操作结果都会被保存下来（事务日志 database transaction log）。每个数据库变动（数据修改语句或者DDL语句）首先会把原始版本的数据（updates和deletes）写到事务日志，当事务提交，并且所有一致性检查都通过以后，事件成功提交的事实就会写入事务日志。如果数据库此之前意外当机，那么再次启动后，数据会回滚。

阻塞 Blocking

如果两个seesion在同样的资源上申请排它锁，当其中一个生成排它锁以后，另外一个必须等第一个释放后（commmit 或者 roll back）才能申请。也就是说同一时间内，两个会话无法写入同一个资源，这样，一个写入阻塞了另外一个写入。这就叫阻塞

除了同一资源申请排它锁会造成堵塞之外，一个排他锁也会阻止其他事务读取同样的资源。因为排它锁和共享锁是不相容的。

死锁 Deadlocking

如果有两个或多个session互相阻塞，这就会造成死锁。当SQL Server 检测到以后，会中断其中一个，然后返回错误信息 1205.

Session 1	Session 2
USE TSQL2012; BEGIN TRAN;	USE TSQL2012; BEGIN TRAN;
UPDATE HR.Employees SET Region = N'10004' WHERE empid = 1
	UPDATE PRoduction.Suppliers SET Fax = N'555-1212' WHERE supplierid = 1
UPDATE Production.Suppliers SET Fax = N'555-1212' WHERE supplierid = 1
<blocked>	UPDATE HR.Employees SET phone = N'555-9999' WHERE empid = 1
	<blocked>

发生死锁以后其中一个事务会完成，而另外一个会被中断，并且显示1205错误信息

Msg 1205, Level 13, State 51, Line 1 Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

最后可以把成功执行事务回滚

IF @@TRANCOUNT > 0 ROLLBACK

@@TRANCOUNT

用来返回在当前连接上执行的 BEGIN TRANSACTION 语句的数目。

如果返回0则表示当前不在一个事务里面， 1表示在一个事务里面，大于1则表示在一个嵌套的事务里面。

注意一个事务只能包含一个ROLLBACK 命令，他会回滚整个事务，然后重置 @@TRANCOUNT 为0

实际例子如下

1. COMMIT TRAN

USE TSQL2012;SELECT @@TRANCOUNT; -- = 0BEGIN TRAN;    SELECT @@TRANCOUNT; -- = 1    BEGIN TRAN;        SELECT @@TRANCOUNT; -- = 2        -- Issue data modification or DDL commands here    COMMIT    SELECT @@TRANCOUNT; -- = 1COMMIT TRAN;SELECT @@TRANCOUNT; -- = 0

2. ROLLBACK TRAN

USE TSQL2012;SELECT @@TRANCOUNT; -- = 0BEGIN TRAN;    SELECT @@TRANCOUNT; -- = 1    BEGIN TRAN;        SELECT @@TRANCOUNT; -- = 2        -- Issue data modification or DDL command here    ROLLBACK; -- rolls back the entire transaction at this pointSELECT @@TRANCOUNT; -- = 0

事务隔离级别

READ COMMITTED

这个是默认隔离级别，只有数据更改被提交以后才能被读取们所有SELECT语句会企图取得一个共享锁，修改数据的另外一个事务会话的排他锁会阻塞READ COMMITTED 会话。

在查询语句加入 WITH (NOLOCK) 或 WITH (READUNCOMMITTED)可以直接读取

注意现在 WITH (NOLOCK) 不推荐使用，在新的SQL 版本中 Update和Delete 语句里面不允许用这个选项了。去而代之的是 WITH (READUNCOMMITTED)

SELECT lastname, firstnameFROM HR.Employees WITH (READUNCOMMITTED);

例子：写入阻塞写入

Session 1	Session 2
USE TSQL2012; BEGIN TRAN;	USE TSQL2012;
UPDATE HR.Employees SET postalcode = N'10004' WHERE empid = 1;	UPDATE HR.Employees SET phone = N'555-9999' WHERE empid = 1;
<more work>	<blocked>
COMMIT TRAN;
	<results returned>

写入阻塞读取

Session 1	Session 2
USE TSQL2012; BEGIN TRAN;	USE TSQL2012;
UPDATE HR.Employees SET postalcode = N'10005' WHERE empid = 1	SELECT lastname, firstname FROM HR.Employees
	<blocked>
COMMIT TRAN;
	<results returned>

READ UNCOMMMITED

这个隔离级别允许reader读取未提交的数据，这个设定使得SELECT 语句不用申请共享锁，不会被writer阻塞。然而被读取的数据在随后可能会回滚到原来的状态，这回导致脏读（reading dirty data)

例子

Session 1	Session 2
USE TSQL2012; BEGIN TRAN;	USE TSQL2012; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE HR.Employees SET region = N'1004' WHERE empid = 1;
	SELECT lastname, firstname, region FROM HR.Employees
	<results returned: region = 1004 for empid = 1>
ROLLBACK TRAN;
<region for empid = 1 rolled back to original value>	SELECT lastname, firstname, region FROM HR.Employees;
	<results returned: region = original value for empid = 1>